[RESOLVED] Using a VBscript to open up files in a folder
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: [RESOLVED] Using a VBscript to open up files in a folder

Hybrid View

  1. #1
    Join Date
    Dec 2011
    Posts
    2

    [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

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,860

    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.

  3. #3
    Join Date
    Dec 2011
    Posts
    2

    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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center