CODEWITHSUNDEEP
vbaexcel
Sebastian Ong
Sebastian Ong

Reputation: 65

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

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

Upvotes: 0

Views: 48

Answers (2)

QHarr
QHarr

Reputation: 84465

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

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166755

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

Upvotes: 1

Related Questions