Automation of Excel to perform simple tasks on many workbooks.

[admin4office]

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.
--
admin4office_perl_programmer.

[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.

--
Regards,
Tom Ogilvy


"admin4office" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> admin4office_perl_programmer.

[admin4office]

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" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "admin4office" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > --
> > 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.

--
Regards,
Tom Ogilvy

"admin4office" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "admin4office" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > > --
> > > admin4office_perl_programmer.
> >
> >
> >

[admin4office]

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

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "admin4office" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "admin4office" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > > --
> > > > admin4office_perl_programmer.
> > >
> > >
> > >
>
>
>