1

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
Dylan F
  • 81
  • 3
  • 14

1 Answers1

2

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)
Community
  • 1
  • 1
Amorpheuses
  • 1,403
  • 1
  • 9
  • 13
  • How would I go about getting the file name from this code section and input it within the Workbooks.Open() function? I need to set the input file as a workbook in order to do the copy procedures listed below in the code. – Dylan F Apr 24 '17 at 19:54
  • 1
    See my EDIT above. – Amorpheuses Apr 24 '17 at 20:30
  • Thanks, it works! All I'm trying to do now is to get the same thing to happen for the second workbook – Dylan F Apr 24 '17 at 21:00
  • How do I get it to do the same thing but for the second workbook as well, so the user can chose the copy file and the destination file? – Dylan F Apr 24 '17 at 23:08
  • Did you try using the code in the post (ie [here](http://stackoverflow.com/questions/42865866/open-save-as-window-and-populate-file-name-and-file-path-from-cell/42879275#42879275))? – Amorpheuses Apr 25 '17 at 00:09
  • I did, it works perfectly, but I'm now trying to figure out how to use the same code in order to get the prompt to display again and as the user to select a different file for the target file. – Dylan F Apr 25 '17 at 00:29
  • 1
    Just use the .Show again on one of the FileDialogue variables that you already have. – Amorpheuses Apr 25 '17 at 02:43