Add code to Loop through Array of Open Workbook Names

jfarc

Active Member
Joined
Mar 30, 2007
Messages
314
I have the below code that allows the user to 'FileDialogOpen' a varying list of Excel files from a predefined folder. This code works fine.

VBA Code:
Dim wbThis As Workbook
Dim fName As String
Dim FileToOpen As String
   
    Set wbThis = ThisWorkbook
    fldrName = wbThis.Path & "\myFolder\"
    ChDir fldrName
    With Application.FileDialog(msoFileDialogOpen)
        .Title = "Open 'ALL' Workbooks"
        .InitialFileName = fldrName & "*.xls"
        .AllowMultiSelect = True
        .Filters.Clear
        If .Show Then
            'Display selected files
            For i = 1 To .SelectedItems.Count
                Workbooks.Open Filename:=.SelectedItems(i), ReadOnly:=False, Password:=""
                ActiveWindow.WindowState = xlMaximized
            Next
        Else
            MsgBox "No Comm w/b selected, please try again."
            Application.DisplayAlerts = False
            Application.Quit
            Exit Sub
        End If
    End With

I need to add a Do/Loop or For/Next to process each file after they are all opened. I simply want to copy a Range of Cells from each of these files to the file that contains this code.

I'm pretty sure this will use an Array() but not sure how to do it. I can handle the CopyPaste code, just need the Array/Do/Loop portion.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,841
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
The normal, if that is the right word, way is to open a workbook, do what needs to be done and then close it with or without saving.
Then do the next workbook.
It looks like you are opening all workbooks in a folder.
If that is true and your "Master" is saved in that same folder, there is no problem at all to do this.
Let us know.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,780
Office Version
  1. 365
Platform
  1. Windows
I am in agreement with jolivanes. It could be something like this. I have marked inserted code sections or lines.

The actual copied range and destination will need to be tweaked to whatever you need.
I also question the two '??????? lines near the end. If the use selects no files the message box invites the user to try again but promptly closes Excel, making such a re-try that much more difficult.

VBA Code:
Sub OpenAndCopy()
  Dim wbThis As Workbook
  Dim fName As String
  Dim fldrName As String
  Dim FileToOpen As String
  Dim i As Long
  
  Set wbThis = ThisWorkbook
  fldrName = wbThis.Path & "\myFolder\"
  ChDir fldrName
  With Application.FileDialog(msoFileDialogOpen)
    .Title = "Open 'ALL' Workbooks"
    .InitialFileName = fldrName & "*.xls"
    .AllowMultiSelect = True
    .Filters.Clear
    If .Show Then
      Application.ScreenUpdating = False  '**********
      'Display selected files
      For i = 1 To .SelectedItems.Count
        Workbooks.Open Filename:=.SelectedItems(i), ReadOnly:=False, Password:=""
        ActiveWindow.WindowState = xlMaximized
        '**********
        With ActiveWorkbook
          .Sheets(1).Range("A1:C4").Copy Destination:=wbThis.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1) 'This line needs tweaking
          .Close
        End With
        '**********
      Next
      Application.ScreenUpdating = True  '**********
    Else
      MsgBox "No Comm w/b selected, please try again."
      Application.DisplayAlerts = False '?????????????
      Application.Quit  '?????????????
      Exit Sub
    End If
  End With
End Sub
 
Solution

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,841
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Or without opening all at once.
Loop through all workbooks in a folder and copy A1:D25 (Change in code as required) from Sheet1 (Change as required) into Master (This Workbook with the code)
The workbook with the macro needs to be saved in that same folder also
Code:
Sub Copy_Data_Of_First_Sheets_Only()
    Dim wb As String, a As Long
    Application.ScreenUpdating = False
    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
            With Workbooks(wb).Sheets("Sheet1")
                .Range("A1:D25").Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End With
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,780
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Or without opening all at once.
The code I suggested didn't do that either. :)

Loop through all workbooks in a folder
The OP does not want to do that. The code posted by the OP allows the user to select any number of files from the given folder.
below code that allows the user to 'FileDialogOpen' a varying list of Excel files from a predefined folder. This code works fine.
 
Last edited:

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,841
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Hi Peter.
I did not look closely at your code. One thing jumped out at me and that is the "AllowMultiSelect = True", hence your "allows the user to select any number ......" etc I assume.
Different approach I guess.
 

jfarc

Active Member
Joined
Mar 30, 2007
Messages
314

ADVERTISEMENT

I am in agreement with jolivanes. It could be something like this. I have marked inserted code sections or lines.

The actual copied range and destination will need to be tweaked to whatever you need.
I also question the two '??????? lines near the end. If the use selects no files the message box invites the user to try again but promptly closes Excel, making such a re-try that much more difficult.

Peter, this code you offered below is simple and perfect for what I needed, thank you. I thought I had to let the routine open all the workbooks first and then loop through them to do the copying. The original code already had the logic of the loop (For/Next) , I just didn't realize it.
VBA Code:
        With ActiveWorkbook
          .Sheets(1).Range("A1:C4").Copy....
          .Close
        End With


VBA Code:
            Application.DisplayAlerts = False
            Application.Quit

The above two lines are because this Excel workbook is opened and the macro is run from a different application (BlueZone Terminal Emulator). If the user wanted to re-try opening the workbooks again, they would simply click the icon in the other application. These lines are to ensure the entire instance of Excel is closed without the 'Do you want to Save?' prompts.
 

jfarc

Active Member
Joined
Mar 30, 2007
Messages
314
Hi Peter.
I did not look closely at your code. One thing jumped out at me and that is the "AllowMultiSelect = True", hence your "allows the user to select any number ......" etc I assume.
Different approach I guess.

Hi jolivanes, thanks for the input. Yes, only the current user knows what files will need opened at that point in time that will be processed. So, can't simply open all workbooks in a folder.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,780
Office Version
  1. 365
Platform
  1. Windows
Peter, this code you offered below is simple and perfect for what I needed, thank you.
You're welcome. Glad it was suitable. :)

The original code already had the logic of the loop (For/Next) , I just didn't realize it.
:biggrin:

The above two lines are because this Excel workbook is opened and the macro is run from a different application (BlueZone Terminal Emulator). If the user wanted to re-try opening the workbooks again, they would simply click the icon in the other application. These lines are to ensure the entire instance of Excel is closed without the 'Do you want to Save?' prompts.
OK, thanks for the additional information. (y)
 

Forum statistics

Threads
1,148,020
Messages
5,744,361
Members
423,864
Latest member
GolfingTitan116

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top