VBA: Open an xls file and add it to the current workbook

2
votes

I am having trouble with this one. I have an xls file which contains a single sheet of data. In my main application, a different workbook, I want to open this single sheet xls file and copy the sheet into the current workbook.

I can do it if I select the range on the source file to copy but this single file may change so I'd rather come up with a solution that just copies the whole file. This is some of the code i've been working with:

Set src = Workbooks.Open(Filename:="thefile.xlsx")

Range("F4:F67").Copy
ThisWorkbook.Activate
Sheets("Result").Activate
Range("A1").Select
ActiveSheet.Paste

I appreciate any help with this.

Thanks

2
excelfilevbacopy

2 Answers

4
votes

Here is a simple solution that doesn't require .Select or .Activate.

Sub getSheetFromA()
    getSheet "a.xls", 1
End Sub

Sub getSheet(filename As String, sheetNr As Integer)
    Dim srcWorkbook As Workbook

    Set srcWorkbook = Application.Workbooks.Open(filename)
    srcWorkbook.Worksheets(sheetNr).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    srcWorkbook.Close
    Set srcWorkbook = Nothing
End Sub
2
votes

I found a solution:

Set src = Workbooks.Open(Filename:="file.xlsx")
Cells.Copy

ThisWorkbook.Activate
Sheets("Result").Activate
Range("A1").Select
ActiveSheet.Paste