Run the same Macro on multiple workbooks

[tzallas]

With some great help from the forum community here, I have managed to use a macro that runs through a dropdown list and exports a sheet to a pdf in a user determined location
Huge thanks to @Yongle

See this thrread for details: https://www.mrexcel.com/forum/excel...ropdown-list-export-into-pdf.html#post5279704

I have about 25 separate workbooks that would need to have the same maro in them, so it would be beneficial to be able to run the same macro on multiple workbooks at a click of a button

I have been able to smash together the below, based on information from here: https://stackoverflow.com/questions/44282502/run-excel-macro-on-multiple-workbooks-files

My VBA skills are below novice

questions:

1. I assume the workbooks that would be affected by the code would also have to be macro enabled (.xlsm)?
2. Where would tha "master code" need to be stored? do I create a blank workbook with the just the module in there and link it to a button, open that and start macro? or does it have to reside in one of the affected workbooks? or in all? could we trigger it with some sort of desktop icon?
3. as I understand it if I run the code as is, it will prompt for a destionation folder for export for each workbook. Which is good. Would there be a way to have it prompt once for an export location for all affected workbooks in the current macro loop? or even give the option for one universal destination or destination per workbook? just to avoid having the user to standby and interact if they don't need to.
4. Also would it be possible to make the macro close affected workbooks after completion (without affecting/saving)? it seems to leave them open currently

I don't know if I am making my life easier or more complicated with this one, any feedback is greatly appreciated nonetheless.

Thank you

Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
Const NamedRangeName = "Tool_Named_Range"
Const SheetName = "Uni-MAG"
Const CellWithDropdown = "B5"
Const PrintRange = "A4:L41"
Const DefaultFolder = "C:\Users\Public\Desktop\"
Dim fPath As String, choice As Range, fName As String
'Prompt user to select folder
        MsgBox "Select folder for PDF Export", vbOKOnly + vbInformation, "PDF Export"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
        .InitialFileName = DefaultFolder
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With


'rest of code
    If fPath <> "" Then
        With Sheets(SheetName)
            For Each choice In Range(NamedRangeName)
                .Range(CellWithDropdown) = choice
                'choice is the dropdown list contents
                fName = choice & ".pdf"
                .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
            Next
        End With
    Else
        'Prompt user that no file was selected
        MsgBox "No folder was selected", vbOKOnly + vbInformation, "No Folder Selection"
    End If
 End With
            xFileName = Dir
        Loop
    End If
End Sub

 

[Rosen]

Answers (to the best of my knowledge):
1. Only the book which contains the Macro itself would have to be macro enabled. The other books can by of any type (.xls*).
2. The "master code" can be stored in any location as long as that book is a macro enabled book (.xlsm).
3. See code below, it will only prompt once for a destination folder.
4. See code below, it will close without saving all workbooks it opens.

Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Const NamedRangeName = "Tool_Named_Range"
    Const SheetName = "Uni-MAG"
    Const CellWithDropdown = "B5"
    Const PrintRange = "A4:L41"
    Const DefaultFolder = "C:\Users\Public\Desktop\"
    Dim fPath As String, choice As Range, fName As String
    Dim Book As Workbook
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
        .InitialFileName = DefaultFolder
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With
    
    If fPath = VBA.Constants.vbNullString Then
        MsgBox "No folder was selected", vbOKOnly + vbInformation, "No Folder Selection"
        Exit Sub
    End If
    
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            Set Book = Workbooks.Open(xFdItem & xFileName)
            With Book.Sheets(SheetName)
                For Each choice In Range(NamedRangeName)
                    .Range(CellWithDropdown) = choice
                    'choice is the dropdown list contents
                    fName = choice & ".pdf"
                    .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
                Next
            End With
            xFileName = Dir
            Book.Close SaveChanges:=False
        Loop
    End If
End Sub

This code is untested, please remember to backup your work before running new code.

I hope this helps!

 

[tzallas]

Thank You @Rosen for the feedback, apologies for taking so long to reply, was travelling...little hectic.

So, I tried your code and it does seem to be wroking great!, we are nearly there.

Some clarifications from my side:

There seem to be two prompts (folderpicker) they both have .Title = "Select destination folder for PDf export", however the first one is indeed the export destination but, the second one is actually the folder containing all workbooks this macro will be applied to.
Also the order of picking should be reversed. First pick folder with all workbooks affected, then pick where to export the pdf's

This is confusing and I am having trouble re-ordering the folder choice and also getting theMsgbox to appear before each pick to guide the user
The below examples do not seem to work when inserted before issuing the .Show command

MsgBox
.Title
.ButtonName
.InitialFileName

I would like:

1. User presses macro button on sheet.
2. Msgbox "Please select folder containing all affected Workbooks"
3. Folder picker opens with .Title = "Please select folder containing all affected Workbooks" .InitialFileName = "DefaultFolder" .Buttoname = "Select"
4. User selects folder with workbooks, presses select
5. MsgBox "Select Folder for PDF Export" (this seems to not be happening anymore after modifying the code with your additions, no clue why
6. Folder picker opens with .Title = "Select destination folder for PDF export" .InitialFileName = DefaultFolder .ButtonName = "Select"
7. Macro runs through, completes and closes as per your previous code.
8. Steps 3 & 6 would only happen once for the entire gamut of workbooks affected by this
9. All workbooks closed without savig, as succesfully done per your previous code above
10. I would like the user to open anyone of the 25 workbooks and still be able to run this "multi" Macro, they will see 2 buttons, one for exporting only the current sheet and one for exporting all workbooks as selected. So I will be applying your the succesful code to all workbooks along with a second module for the single workbook. I am just mentioning in this in case having the same macro in all affected workbooks might cause a problem, or if it is even possible at all. Trying to make this fool proof to an extent

Code for running macro on multiple worksbooks
(this is where I need to apply the above fixes/features, code will be replicated on all workbooks, to allow user to open any one at random and to this)

Sub MassExport()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Const NamedRangeName = "Tool_Named_Range"
    Const SheetName = "Uni-MAG"
    Const CellWithDropdown = "B5"
    Const PrintRange = "A4:L41"
    Const DefaultFolder = "C:\Users\Public\Desktop\"
    Dim fPath As String, choice As Range, fName As String
    Dim Book As Workbook
    'Folder picker order needs to be reversed, first pick folder conataining workbooks, then export destination folder
    'Customisation of Folderpicker with .Title etc would be great
    'would also need to add MsgBox prmpt before folder picker pops up to guide user
    With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select destination folder for PDF export"
        .InitialFileName = DefaultFolder
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With
     'This part of the code with the Msgbox does not seem to come into play anymore, not sure why
    If fPath = VBA.Constants.vbNullString Then
        MsgBox "No folder was selected", vbOKOnly + vbInformation, "No Folder Selection"
        Exit Sub
    End If
    
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
       'msgbox to tell user wht they will need to be picking needed here, was in previous code sample
       'Customisation of Folderpicker with .Title etc would be great
       If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        'Changed to "*.xlsm*" since all affected workbooks will be macro-enabled anyway
        xFileName = Dir(xFdItem & "*.xlsm*")
        Do While xFileName <> ""
            Set Book = Workbooks.Open(xFdItem & xFileName)
            With Book.Sheets(SheetName)
                For Each choice In Range(NamedRangeName)
                    .Range(CellWithDropdown) = choice
                    'choice is the dropdown list contents
                    fName = choice & ".pdf"
                    .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
                Next
            End With
            xFileName = Dir
            Book.Close SaveChanges:=False
        Loop
    End If
End Sub

Code for running only on the current Workbook
This works great and as desired, it will co-exist in a second module in all workbooks so user can choose wither to run the below code for one workbook, or apply macro to multiple workbooks as per above code

Sub PDF_Export_current_only()
Const NamedRangeName = "Tool_Named_Range"
Const SheetName = "Uni-MAG"
Const CellWithDropdown = "B5"
Const PrintRange = "A4:L41"
Const DefaultFolder = "C:\Users\Public\Desktop\"
Dim fPath As String, choice As Range, fName As String
'Prompt user to select folder
        MsgBox "Select folder for PDF Export", vbOKOnly + vbInformation, "PDF Export"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
        .InitialFileName = DefaultFolder
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With


'rest of code
    If fPath <> "" Then
        With Sheets(SheetName)
            For Each choice In Range(NamedRangeName)
                .Range(CellWithDropdown) = choice
                'choice is the dropdown list contents
                fName = choice & ".pdf"
                .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
            Next
        End With
    Else
        'Prompt user that no file was selected
        MsgBox "No folder was selected", vbOKOnly + vbInformation, "No Folder Selection"
    End If


End Sub

 

[Rosen]

Here are some modifications that I believe meet your requirements:

Sub MassExport()
    ' =========================================================================
    ' STEP 1: It is assumed at this point that the user has clicked the button,
    ' which has caused this code to fire.
    ' =========================================================================
    
    Dim xFdItem As Variant
    Dim xFileName As String
    Const NamedRangeName = "Tool_Named_Range"
    Const SheetName = "Uni-MAG"
    Const CellWithDropdown = "B5"
    Const PrintRange = "A4:L41"
    Const DefaultFolder = "C:\Users\Public\Desktop\"
    Dim fPath As String, choice As Range, fName As String
    Dim Book As Workbook
    
    ' =========================================================================
    ' STEP 2: Prompt User to select folder
    ' =========================================================================
    MsgBox "Please select folder containing all affected Workbooks", vbOKOnly + vbInformation
    
    ' =========================================================================
    ' STEP 3: Prompt user with Folder Picker
    ' =========================================================================
    With Application.FileDialog(msoFileDialogFolderPicker)
        'Customisation of Folderpicker
        .Title = "Please select folder containing all affected Workbooks"
        .InitialFileName = DefaultFolder
        .ButtonName = "Select"
        If .Show = -1 Then xFdItem = .SelectedItems(1) & Application.PathSeparator
    End With
    
    ' =========================================================================
    ' STEP 4: Ensure the user clicked 'Select' and not 'Cancel' or 'X'
    ' =========================================================================
    If xFdItem = VBA.Constants.vbNullString Then Exit Sub
    
    ' =========================================================================
    ' STEP 5: Prompt user to select destination folder
    ' =========================================================================
    MsgBox "Select Folder for PDF Export", vbOKOnly + vbInformation
    
    ' =========================================================================
    ' STEP 6: Prompt user to select PDF export folder
    ' =========================================================================
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
        .InitialFileName = DefaultFolder
        .ButtonName = "Select"
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With
    
    ' =========================================================================
    ' NOT A STEP OUTLINED, BUT: Ensure the user clicked 'Select'
    ' =========================================================================
    If fPath = VBA.Constants.vbNullString Then Exit Sub
    
    ' =========================================================================
    ' STEP 7 - 9 are completed below
    ' =========================================================================
    'Changed to "*.xlsm*" since all affected workbooks will be macro-enabled anyway
    'No need to have the wildcard (*) at the end, the wildcard says we are looking for
    'any value to come after the .xls (be it nothing, or m or x, etc.), but since we
    'aren't, it isn't applicable.
    xFileName = Dir(xFdItem & "*.xlsm")
    Do While xFileName <> ""
        ' Setting ReadOnly parameter to true, so as to allow the workbooks to be opened
        ' and updated by other users while this run is occuring. NOTE: the state of the
        ' workbook will not dynamically update, whatever state it was in once the below
        ' code executes on that workbook will determine the data that is exported to
        ' the PDF
        Set Book = Workbooks.Open(xFdItem & xFileName, , True)
        With Book.Sheets(SheetName)
            For Each choice In Range(NamedRangeName)
                .Range(CellWithDropdown) = choice
                'choice is the dropdown list contents
                fName = choice & ".pdf"
                .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
            Next
        End With
        xFileName = Dir
        Book.Close SaveChanges:=False
    Loop
End Sub

This code is untested, please remember to backup your work before running new code.​

Hope this helps!

 

[tzallas]

ray:[MENTION]Rosen[/MENTION]
There is nothing quite like the high from observing successful automation

Thank you very much, that worked exactly as intended!
And I very much appreciate the help notes in ethe code too!

 

[tzallas]

I just noticed one issue. (Though now that I think of it, should have been obvious to me)

The Workbooks all contain the same macros
They all reside in the same folder
if the open workbook that runs the massExport macro is part of the folder containing all affected Workbooks then the following error pops up. (I assume because it is already open and inculded in the selection of files that the macro will be applied to, so the code sort of "self-implodes")

"xyz.xlsm is already open. Re-opening will cause any changes you made to be discarded. Do you want to re-open xyz.xlsm?"

Clicking "yes" re-opens it and stops the procession of the massexport macro

Clicking "no" returns the follwoing error

"Run time error 1004
Method 'Open' of object workbooks failed"

Clicking "debug" takes you to the below line of the code:

Set Book = Workbooks.Open(xFdItem & xFileName, , True)

I have a hunch you will tell me that the open workbook running the macro, cannot be part of the sample workbooks.
All workbooks to be affected by the macro have to be closed when the macro comes to them

Is there a way to make it so it would include the open workbook too, for a neatly wrapped user experience?
I was thnking of maybe trying to assign the macro to a desktop shortcut icon and have it run without needing to open excel at all, but from what I understand this is not supported (?)

Worst comes to worst I will create a dummy Workbook in a different location to run it without clashing, but I thought I would ask here first in case I can get a neat solution

Thank you