Copy in a worksheet from another workbook

[Access9001]

All I can find is code explaining how to copy active sheets to other books, but that's not what I want. I want to open an Excel file and have it extract a sheet from another workbook and add it in. Possible?

I have this code on my workbook open event


Private Sub Workbook_Open()

If WorksheetExists("Summary") = False Then
Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet

Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBook = ObjXL.Workbooks.Open(path to my workbook where the desired page is)
Set ObjXLSheet = ObjXLBook.Worksheets("Summary")
ObjXLBook.Activate

ObjXL.Sheets("Summary").Move before:= Workbooks(ThisWorkbook.FullName).Worksheets(1)

Set ObjXLSheet = Nothing
Set ObjXLBook = Nothing
ObjXL.Quit
Set ObjXL = Nothing
End If

End Sub


But it gives me a subscript out of range error

 

[Access9001]

If I try copying to activeworkbook.sheets(1) it doesn't give the subscript out of range error anymore but it now yields "copy method failed"

 

[vbaInet]

Put this in perspective. When you perform a copy operation in an Excel spreadsheet you would normally select a cell, a range of cells or the entire sheet. Without that the Copy menu is disabled.

 

[Access9001]

Well if I do the record macro I get very similar syntax but if I try to make it dynamic, it fails.

 

[SimonB1978]

Hi,

Here is how I made it work:

Private Sub Workbook_Open()

If WorksheetExists("Summary") = False Then
Dim ObjXL As Excel.Application
Dim ObjXLBookSource As Excel.Workbook
Dim ObjXLBookDest As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet

Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBookSource = ObjXL.Workbooks.Open(path to my workbook where the desired page is)
Set ObjXLBookDest = ObjXL.Workbooks.Open(path to my workbook where the desired page is going)
Set ObjXLSheet = ObjXLBookSource.Worksheets("Summary")
'ObjXLBook.Activate

ObjXLSheet.Copy before:= ObjXLBookDest.Worksheets(1)

Set ObjXLSheet = Nothing
Set ObjXLBook = Nothing
ObjXL.Quit
Set ObjXL = Nothing
End If

End Sub

Hope that helps...

Simon B.

 

[Access9001]

That just flooded my task manager with repeated openings of EXCEL.EXE -- I don't know if that was intentional out of malice or a genuine mistake, but either way, it did not work as desired.

 

[SimonB1978]

Sorry I didn't close the objects...

ObjXLBookDest.Close
ObjXLBookSource.Close
ObjXL.Quit


But I don't see how it can flood your task manager... 1 application opens 2 workbooks...

Is your sub called in a loop??

 

[Access9001]

It's part of my Open Workbook subroutine

I am afraid to reopen the file now... it's going to spam the hell out of me before I can even edit it

 

[Access9001]

Got back in and control+breaked hardcore XD Back in safely now

 

[SOS]

It's part of my Open Workbook subroutine

I am afraid to reopen the file now... it's going to spam the hell out of me before I can even edit it

All you need to do is to add

ObjXL.Visible= true

right after the instantiation so you can close it without going to the task manager. I always do that during development so that if it errors out it won't leave rogue instances of Excel running.

 

[SimonB1978]

Oh! I thought your code was inside an Access app...

IF it is in the workbook, why do you want to make it dynamic? It will always look for the same thins anyway no?

To edit it again, just rename the "Source" workbook temporarily, the on open will crash, and you'll be able to modify it...

 

[chergh]

Personally I think it's quite silly to have this in an open event and why are you using late binding to create an instance of excel when you are doing it from within excel? Anyway this works.

Private Sub Workbook_Open()

Dim book1 As Workbook
Dim book2 As Workbook

Set book1 = ThisWorkbook
Set book2 = Workbooks.Open("C:\aaa\book2.xls")

book2.Worksheets("dfs").Move before:=book1.Worksheets("Sheet1")

End Sub

But it will only work once as the sheet is being moved.

 

[Access9001]

Personally I think it's quite silly to have this in an open event and why are you using late binding to create an instance of excel when you are doing it from within excel? Anyway this works.

Is this going to infinite-loop me if it's an on-open event?

What is late-binding? I also need it to not literally move the sheet, since I need to re-use it later.

 

[Access9001]

Trying to just close book2 without saving changes to "keep" the sheet that was moved, but it's not really getting rid of the warnings because it's asking me to update cell references (which I want to make dynamic anyway).

Private Sub Workbook_Open()

If WorksheetExists("Summary") = False Then
Dim book1 As Workbook
Dim book2 As Workbook

Set book1 = ThisWorkbook
Set book2 = Workbooks.Open(path with the page to copy)

book2.Worksheets("Summary").Move before:=book1.Worksheets(1)

Set book2 = Nothing
book2.Close
End If

Application.DisplayAlerts = True
End Sub

 

[chergh]

Don't use the open event then, I don't know what you have in all your workbooks so have no idea if it will infinite loop you or not. Just put the code in a module instead of an event.

Replace the "move" in the code with "copy" if you don't want to move it, also think about how you have described your issue in the first place you should have just said you wanted to copy the worksheet rather than "have it extract a sheet from another workbook and add it in"

As for late binding you can look it up on google.

Anyway with the new requirements you have told us about you want to use something like:

Sub blah()

Dim book1 As Workbook
Dim book2 As Workbook

Application.DisplayAlerts = False
Set book1 = ThisWorkbook
Set book2 = Workbooks.Open("C:\aaa\book2.xls")

book2.Worksheets("dfs").Copy before:=book1.Worksheets("Sheet1")

book2.Close False
Application.DisplayAlerts = True

End Sub

 

[SOS]

Also a suggestion for the future. Instead of posting it in the ACCESS > MODULES & VBA category, if it is all in Excel you should put it in the EXCEL category so we aren't mislead to what is happening.

 

[ghudson]

Also a suggestion for the future. Instead of posting it in the ACCESS > MODULES & VBA category, if it is all in Excel you should put it in the EXCEL category so we aren't mislead to what is happening.

Bingo! Mods should move this post.