I am trying to write a VB script in Excel that will open up all files with a
..rpt extension, then format them in a particular way, then save them as .xls
files in a separate folder. I have been able to script the formatting of the
opened files, but I would like Excel to open all the files in a certain
folder that has the .rpt extension. Any suggestions on how to write this?

Sub HandleRPT()
Dim sPath as String, sName as String
Dim bk as Workbook, s as String
sPath = "C:\Myfolder\"

sName = Dir(sPath & "*.rpt")
do while sName < ""
set bk = workbooks.Open(sPath & sName)
' process bk
s = left(sName,len(sName)-4)
bk.SaveAs sPath & s & ".xls", xlWorkbookNormal
bk.close Savechanges:=False
sName = Dir
Loop
End sub

--
Regards,
Tom Ogilvy


"Jason" wrote:

I am trying to write a VB script in Excel that will open up all files with a
.rpt extension, then format them in a particular way, then save them as .xls
files in a separate folder. I have been able to script the formatting of the
opened files, but I would like Excel to open all the files in a certain
folder that has the .rpt extension. Any suggestions on how to write this?

This is poetic. It works fine now. Thanks!



"Tom Ogilvy" wrote:

Sub HandleRPT()
Dim sPath as String, sName as String
Dim bk as Workbook, s as String
sPath = "C:\Myfolder\"

sName = Dir(sPath & "*.rpt")
do while sName < ""
set bk = workbooks.Open(sPath & sName)
' process bk
s = left(sName,len(sName)-4)
bk.SaveAs sPath & s & ".xls", xlWorkbookNormal
bk.close Savechanges:=False
sName = Dir
Loop
End sub

--
Regards,
Tom Ogilvy


"Jason" wrote:

I am trying to write a VB script in Excel that will open up all files with a
.rpt extension, then format them in a particular way, then save them as .xls
files in a separate folder. I have been able to script the formatting of the
opened files, but I would like Excel to open all the files in a certain
folder that has the .rpt extension. Any suggestions on how to write this?