Open and copy data from embedded workbooks

Kjuel

New Member
Joined
Sep 4, 2006
Messages
7
This is my first post on MrExcel...hope you can help me?

My task is to open several embedded workbooks from a data workbook (vTempWorkbook) and copy some information from each embedded workbook to the main workbook (vMainWorkbook).

The code below works fine if run in debug mode (F8). However Im having an error when the code selects the sheet "Test Plan" when run outside debug mode. It is as if the embedded worksheet is not really activated??? A debug just before the sheet selection returns the name of the embedded workbook when run in debug mode and the name of the data workbook when outside debug mode.

Anyone?


Code:
Sub Macro1()
    vMainWorkbook = ActiveWorkbook.Name
    vTempWorkbook = "DataWorkbook.xls"
    
    For i = 1 To ActiveSheet.OLEObjects.Count
        Windows(vTempWorkbook).Activate
        
        'Open embedded workbook and copy range
        ActiveSheet.OLEObjects(i).Verb xlOpen
        Windows("Worksheet in " & vTempWorkbook).Activate
        Debug.Print ActiveWorkbook.Name
        Sheets("Test Plan").Select
        Range("A7:F18").Copy
        
        'Paste range in main workbook
        Windows(vMainWorkbook).Activate
        Sheets("KeyControlsTest").Range("A" & ((i - 1) * 14) + 1).Select
        ActiveSheet.Paste
        
        'Close embedded workbook
        Windows("Worksheet in " & vTempWorkbook).Close
    Next i
    
    'Sort and filter on data copied (pending)
End Sub

Regards
Kasper
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Kjuel

New Member
Joined
Sep 4, 2006
Messages
7
Got it!

Replaced:

Code:
Windows("Worksheet in " & vTempWorkbook).Activate 
Debug.Print ActiveWorkbook.Name 
Sheets("Test Plan").Select 
Range("A7:F18").Copy

With:

Code:
Set wb1 = Workbooks("Worksheet in " & vTempWorkbook)
wb1.Sheets("Test Plan").Range("A7:F18").Copy

Thanks for dropping by
: )
 

Forum statistics

Threads
1,141,734
Messages
5,708,168
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top