-
December 7th, 2011, 10:31 AM
#1
[RESOLVED] Using a VBscript to open up files in a folder
Hi,
I am trying to build a VB script which will loop through a folder and open up all the files within it (all .xlsm). The code will then run a macro within each of the files.
It was working when the script was built as a list, but this was for a quick test, in real life it will deal with hundreds so a loop is needed.
the code i currently have is:
Option Explicit
Dim FSO, FLD, FIL, objExcel
Dim strFolder
Set objExcel = CreateObject("Excel.application")
strFolder = "H:\My Documents"
Set FSO = CreateObject ("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder (strFolder)
For Each FIL in FLD.Files
objExcel.workbooks.open FIL.Name
objExcel.Run "Macro"
Next
objExcel.quit
The script is running and then failing saying Error: 'Filename.xlsm' Could not be found. Which is strange, as it is there, and it has obviously found it as it is the first file in the folder.
I am unsure of where to go and what to do next.
Any help would be greatly appreciated.
Thanks
Sam
-
December 7th, 2011, 10:41 AM
#2
Re: Using a VBscript to open up files in a folder
I don't use FSO but sounds like it is giving you the filename but not the path, you may want to try something like I have added in red to get the proper path for your file and see if that solves the issue.
Code:
Option Explicit
Dim FSO, FLD, FIL, objExcel
Dim strFolder
Set objExcel = CreateObject("Excel.application")
strFolder = "H:\My Documents"
Set FSO = CreateObject ("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder (strFolder)
For Each FIL in FLD.Files
objExcel.workbooks.open strFolder & "\" & FIL.Name
objExcel.Run "Macro"
Next
objExcel.quit
On another note your Dim statements should be changed. As is you are defining all your vars as Variants which is not a good idea. You need to specify what type you want each variable to be.
Last edited by DataMiser; December 7th, 2011 at 10:45 AM.
Always use [code][/code] tags when posting code.
-
December 7th, 2011, 11:07 AM
#3
Re: Using a VBscript to open up files in a folder
Excellent that has worked ... I also sorted out the Dim statements. Thanks a lot DataMiser
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|