Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: VBA Open File/Run Code/Close & Save/Open Next File?

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA Open File/Run Code/Close & Save/Open Next File?

    Hello,

    I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

    Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

    I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

    I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!

    Thanks,
    Jason

  2. #2
    Board Regular pboltonchina's Avatar
    Join Date
    Apr 2008
    Location
    England
    Posts
    1,093
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    This will loop through all files in a folder. Change the necessary path and macro name to suit your needs.

    Code:
    Sub AllFiles()
        Dim folderPath As String
        Dim filename As String
        Dim wb As Workbook
      
        folderPath = "C:\SAP Imports\Sales Orders\" 'change to suit
        
        If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
        
        filename = Dir(folderPath & "*.xls")
        Do While filename <> ""
          Application.ScreenUpdating = False
            Set wb = Workbooks.Open(folderPath & filename)
             
            'Call a subroutine here to operate on the just-opened workbook
            Call Mymacro
            
            
            filename = Dir
        Loop
      Application.ScreenUpdating = True
    End Sub

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    Wow! That works great. Thank you so much... I can't tell you how much time that this will save me.

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    I haven't tried this on the set of files I will be using it for yet, but wanted to make sure the "Update Links" prompt would not affect this? I'd like to update links in each file when they open... would Application.DisplayAlerts = False work for links that cannot be updated? Some of the files have links that are are no longer valid.

    In other words, can the code "Update Links" and, if required, "Continue" if links cannot be updated?

    Thanks!

  5. #5
    Board Regular pboltonchina's Avatar
    Join Date
    Apr 2008
    Location
    England
    Posts
    1,093
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    Whatever macro you are running on a single file should work exactly the same when called from this code.

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    Thanks again... one additional concern... is it possible to open the files in the alphabetical order they are in in the folder? It seems to open them at random.

    Thanks,
    Jason

  7. #7
    Board Regular pboltonchina's Avatar
    Join Date
    Apr 2008
    Location
    England
    Posts
    1,093
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    I've never bothered about the order it processes the files, It runs and when it's finished all the files are done. What difference does it make?

  8. #8
    Board Regular
    Join Date
    Feb 2010
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    Because I will be running a macro that freezes important financial information in several hundred files and I would need to quickly know how far the macro has gotten to if something were to happen. What order does it open it in? It seems to be some sort of order because it opens the same file first... I thought maybe the file size but that is not the case. I'd really like for it do open in alphabetical order. Is that possible?

    I appreciate your help.

    Jason

  9. #9
    Board Regular pboltonchina's Avatar
    Join Date
    Apr 2008
    Location
    England
    Posts
    1,093
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    I don't know what determines the order or how to pre-determine it, sorry.

  10. #10
    New Member
    Join Date
    Nov 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Open File/Run Code/Close & Save/Open Next File?

    I've adopted the code that you posed @pboltonchina, however it only loops through 1 document and then closes.

    Code:
    Sub AllFiles()    Dim folderPath As String
        Dim filename As String
        Dim wb As Workbook
      
        folderPath = "C:\Users\USER\Desktop\OCCREPORTS\Files\" 'change to suit
        
        If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
        
        filename = Dir(folderPath & "*.xls")
        Do While filename <> ""
          Application.ScreenUpdating = False
            Set wb = Workbooks.Open(folderPath & filename)
            Combine
            filename = Dir
        Loop
      Application.ScreenUpdating = True
    End Sub
    Sub Combine()
        Dim J As Integer
        Dim s As Worksheet
        Dim LastCol As Integer
        
           
        On Error Resume Next
        Sheets(1).Select
        Worksheets.Add ' add a sheet in first place
        Sheets(1).Name = "Combined"
    
    
    
    
    For Each s In ActiveWorkbook.Sheets
            If s.Name <> "Combined" Then
                Application.Goto Sheets(s.Name).[A1]
                Selection.CurrentRegion.Select
                Sheet.UsedRange.Clear
                LastCol = Sheets("Combined").Cells(1, Columns.Count).End(xlToLeft).Column
                Selection.Copy Destination:=Sheets("Combined"). _
                Cells(1, LastCol + 1)
            End If
        Next
        ActiveWorkbook.Save
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •