Import a file to current VBA workbook

Question

I'm trying to build a VBA macro to allow a user to select a file from file Dialog and import Sheet 1 from the file to the Active VBA Workbook and to rename the Sheet as "Import". Im new to this and this is as far as I got. This allows me to open file dialog and select a file. But all it does is open up the file which isnt much help.

Sub OpenAFile()

Dim fd As FileDialog
Dim FileWasChosen As Boolean

Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Add "Any Excel Files", "*.xl*"

FileWasChosen = fd.Show

If Not FileWasChosen Then
MsgBox "You didn't select a file"
Exit Sub
End If

fd.Execute

End Sub

Possible a [duplicate](http://stackoverflow.com/questions/19351832/vba-copy-from-one-workbook-and-paste-into-another).... — Peter Schneider, May 06 '15 at 15:49

score 0 · Accepted Answer · answered May 06 '15 at 16:10

You were almost there. Here is one way of doing it. It's a bit convoluted and I know there are more slick ways but it will get the job done.

Sub OpenAFile()

Dim fd As FileDialog
Dim FileWasChosen As Boolean

Dim homeWorkbook As Workbook
Set homeWorkbook = ActiveWorkbook

Dim targetBook As Workbook
Dim targetSheet As Worksheet

Application.DisplayAlerts = False

Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Add "Any Excel Files", "*.xl*"

FileWasChosen = fd.Show

If Not FileWasChosen Then
     MsgBox "You didn't select a file"
Exit Sub
End If

fd.Execute

Set targetBook = ActiveWorkbook
Set targetSheet = targetBook.Worksheets(1)

targetSheet.Copy After:=homeWorkbook.Sheets(homeWorkbook.Sheets.Count)
targetBook.Close

Application.DisplayAlerts = True

With homeWorkbook.Sheets(homeWorkbook.Sheets.Count)
    .Name = "Import"
End With


End Sub

Import a file to current VBA workbook

1 Answers1