Getting File Location and Name for Excel VBA

Question

I am creating a VBA program that will copy one column from one file to another.

The current code works, but I wish to change it to where a prompt will come up and ask the user for the file location and name / extension. That input will be imported as the file location for the Workbooks.Open function and go from there.

How do I create a prompt to ask for the user to input the file location and name for the desired excel file, and have it input in the Workbooks.Open function?

Code:

Sub Macro1()

Dim wb1 As Workbook
Dim wb2 As Workbook

MsgBox "Now converting data from Incident Data to Specific Data "

'Set it to be the file location, name, and extension of the Call Data CSV
Set wb1 = Workbooks.Open("Z:\xxxx\Call Data - Copy.csv")

'Set it to be the file location of the Working File
Set wb2 = Workbooks.Open("Z:\xxxx\Working File.xlsx")

wb1.Worksheets(1).Columns("E").Copy wb2.Worksheets(1).Columns("A")
wb1.Worksheets(1).Columns("I").Copy wb2.Worksheets(1).Columns("Q")
wb1.Worksheets(1).Columns("AE").Copy wb2.Worksheets(1).Columns("R")
wb1.Worksheets(1).Columns("BD").Copy wb2.Worksheets(1).Columns("F")

wb2.Close SaveCahnges:=True
wb1.Close SaveChanges:=True

End Sub

Answer 1

I would go with FileDialog to select an input file:

Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

fDialog.AllowMultiSelect = False
fDialog.title = "Select a file"
fDialog.InitialFileName = "C:\"
fDialog.Filters.Clear
fDialog.Filters.Add "Excel files", "*.xlsx"

'Show the dialog. -1 means a file has been successfully selected
If fDialog.Show = -1 Then
   Debug.Print fDialog.SelectedItems(1)
End If

For saving you can refer to this post

EDIT:

To use it in Workbooks.Open you just do something like the following:

Dim fname As String
If fDialog.Show = -1 Then
   fname=fDialog.SelectedItems(1)
Else 
   MsgBox("Filename selection error")
   Exit Sub
End If

Set wb1 = Workbooks.Open(fname)