(Solved) – VBA Using FileDialog as path in a loop


I am trying to make a copy of a workbook that I have, based on list of IDs. I have got this to work OK if I hard code the path, however I can’t figure out how to do this where specifying the path using msoFileDialogFolderPicker.

I have tried a number of variations depending on what I have found online and have got as far as below but stuck. Help appreciated.

Dim xFilepath As Variant
Dim xFilename As String
xFilepath = Application.FileDialog(msoFileDialogFolderPicker)
xFilename = Range("Table9[ProgramID]") & " Product Financial Allocation" & ".xlsb"

With xFilepath
    .Title = "Choose Destination"
    mypath = .SelectedItems(1) & ""
End With

mypath = mypath


  For i = 1 To 3

Sheets("FILES").Range("A" & i).copy Sheets("TEMPLATE").Range("Table9[ProgramID]")

        ActiveWorkbook.SaveCopyAs Filename:=mypath & Filename
        FileFormat = 50

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Workbooks.Open Filename:=mypath & Filename

    Call DeleteQueries


  Next i

        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Application.EnableEvents = True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *