How do I make a macro that enables me to import data from one workbook to another ?B

Question

Basically I would like to import data by clicking on a button assigned with the macro which would open the file browser, prompting the user to open the excel file they would like to import. I have tried to debug my codes but my For Each loop keeps getting an error, any help is appreciated!

Sub BrowseForFile()  
    Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

    fileName = Application.GetOpenFilename(, , "Browse for Workbook")
    Workbooks.Open (fileName)

    For Each sheet In Workbooks(fileName).Worksheets
        total = Workbooks("FIEP.xlsm").Worksheets.count
        Workbooks(fileName).Worksheets(sheet.Name).Copy _
          after:=Workbooks("FIEP.xlsm").Worksheets(total)
    Next sheet

    Workbooks(fileName).Close
End Sub

It states Subscript out of range and highlights "For Each sheet In Workbooks(fileName).Worksheets" — Sebastian Ong, Feb 07 '18 at 05:52
It doesn't like the path part of the now open workbook. It expects just the filename. — QHarr, Feb 07 '18 at 06:02

score 1 · Accepted Answer · answered Feb 07 '18 at 06:07

Use a variable for the workbook object:

Sub BrowseForFile()

 Dim directory As String, fileName As String, sheet As Worksheet, total As Long
 Dim wb As Workbook

 fileName = Application.GetOpenFilename(, , "Browse for Workbook")


 Set wb = Workbooks.Open(fileName)

 For Each sheet In wb.Worksheets
    total = Workbooks("FIEP.xlsm").Worksheets.count
    sheet.Copy after:=Workbooks("FIEP.xlsm").Worksheets(total)
 Next sheet

 wb.Close

End Sub

QHarr · Answer 2 · 2018-02-07T06:27:20.680

Remove the file path part. Variety of methods available. I used the one from here.

The Workbooks object is the collection of all the Workbook objects that are currently open in the Microsoft Excel application.

It does not need the file path just the name. You could also have said ActiveWorkbook, though this would have been perhaps less robust.

Edit: Or as in @TimWilliam's answer, you can store the now open workbook in a variable and use that as the reference.

Option Explicit

Sub BrowseForFile()

    Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

    fileName = Application.GetOpenFilename(, , "Browse for Workbook")

    Workbooks.Open (fileName)

    Dim fso As New FileSystemObject 'Requires references to MS Scripting Runtime

    fileName = fso.GetFileName(fileName)

    For Each sheet In Workbooks(fileName).Worksheets '
        Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("FIEP.xlsm").Worksheets(total)
    Next sheet

    Workbooks(fileName).Close

End Sub

How do I make a macro that enables me to import data from one workbook to another ?B

2 Answers2