Clever Geek Handbook

MS EXCEL VBA - I need to import a worksheet from one excel file to another

I need to import a worksheet from one excel workbook (worksheet name is not always the same) and import it into the currently active workbook.

Here's what I have so far:

Sub openFile_Click()
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse", _
    FileFilter:="Report Files *.rpt (*.rpt),")

    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    Else
        Workbooks.Open Filename:=FileToOpen

        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Set wb1 = ActiveWorkbook
        wb2 = Workbooks(FileToOpen)     'This is where I am stuck..I can't give it a static name

        For Each Sheet In wb1.Sheets
            If Sheets.Visible = True Then
                Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
            End If

        Next Sheet

    End If
+3


source to share


2 answers


This code will work the way you want. I made the following fixes.



  • Move all variable declarations to the beginning of the procedure so that they are declared before they are used. It's just good practice.

  • Assign your active workbook to a variable before opening the second workbook so that only one workbook will open.

  • Your application also had some corrections.

    Sub openFile_Click()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.rpt (*.rpt),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)

    For Each Sheet In wb2.Sheets
        If Sheet.Visible = True Then
            Sheet.Copy After:=wb1.Sheets(wb1.Sheets.Count)
        End If
    Next Sheet

End If

End Sub
+2


source


Install book in open (or install book later without file path)

Here you go:



Sub openFile_Click()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.rpt (*.rpt),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbooks.Open(FileToOpen)




    For Each Sheet In wb1.Sheets
        If Sheet.Visible = True Then
            Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
        End If

    Next Sheet

End If
End Sub
0


source






More articles:


All Articles