Can DOS commands be used in a batch file to read and write data (numeric and text) in Excel spreadsheets?

The problem: I have multiple data sets created as Excel worksheets in individual Excel workbooks and I need to extract the data values from corresponding cells in each workbook/worksheet, derive summed and average values of the numeric data, concantenate some text "values," and write results to a new worksheet.

Currently, this MEANS copy/paste each worksheet into a very large "master" workbook, and use the standard Excel functions to sum across all of those worksheets, etc. This works fairly well, but it is manually intensive and prone to error.

However, I have not seen anything to suggest that DOS commands can address specific worksheets within an Excel work book, or point to specific cells within the worksheet, so I am not at all sure that it can be done.

Any thoughts on this will be much appreciated.hi,

By saving your Excell project into a CVS (COMMA Separated file) you will be able to do a DOS loop on this file by using comma as delimiter. Then you could have each cell info into separate variables (that could be summed using the SET /a command). Then you can write your file back to the same format and import it in Excell.

I used to use this file format to automatically generate graphics in Excell, but I was using a more advance programming langage (not DOS scripting which is limitating).

Hope this will help!
ypTo YP,

Many thanks.

I'll have to try some simple test cases first to see if I understand your approach.

Since I am by no means a programmer even this may be a challenge, but I can SOMETIMES muddle through with help from the forum and a couple of very basic DOS reference books.

I suspect that this may be a lot easier using some higher level language, but I am not ready to GO there just yet.

TAQuote from: Aero_Tai on November 01, 2007, 11:14:38 AM

Can DOS commands be used in a batch file to read and write data (numeric and text) in Excel spreadsheets?