Automation of Excel to perform simple tasks on many workbooks.

[Guest]

I want to manipulate many Excel workbooks without having to open and enter
commands in each of them. If I have a folder with 1000 Excel workbooks, I
would like to run a script that could open each, tell each to save its
worksheets as plain text, and then close each. I could then use Perl to
search or index the plain text files.
The sequence of commands seems to be predictable, and therefore
scriptable. The only variables are the number of worksheets per workbook and
their names.
Is there a branch of Windows programming that supports this?
Many thanks.

 

[Tom Ogilvy]

Sub ABCD()
Dim srcPath as String
Dim destPath as STring
Dim sName as String
Dim bk as Workbook
Dim sh as Worksheet
srcPath = "C:\MyFolderxls\"
destPath = "C:\Myfoldercsv\"
sName = Dir(srcPath & "*.xls")
do
set bk = Workbooks.Open(srcPath & sName)
for each sh in bk.worksheets
sh.copy
activeWorkbook.SaveAs destPath & bk.Name & "_" & _
sh.Name & ".csv", fileformat:=xlCSV
activeWorkbook.Close Savechanges:=False
Next
bk.close SaveChanges:=False
sName = dir()
Loop while sname <> ""
End Sub

How successful this is depends on what is in your sheets.

 

[Guest]

To Tom Ogilvy,
Thanks very much! This is a great help.
Am I correct in assuming that this code is VBA? (Which I must obviously
learn.)
Would you run this program as an Excel macro, or from outside of Excel?

Thanks again,
admin4office_perl_programmer

 

[Tom Ogilvy]

It is VBA run from within Excel (as a macro). All code must be in a
workbook, so you would put this code in a workbook that is not in the folder
being processed.

 

[Guest]

Thanks once again! This may save me an enormous amount of work.
Regards,
admin4office_perl_programmer