Embedded workbook obj definition issue: CHALLENGING!

[Steve-0]

Background:
I have a word document which I have set a macro to run upon open which selects an embedded chart object and opens an embedded excel workbook consisting of a data sheet and the chart sheet. The embedded workbook then has its own macro set to run upon opening that performs a data import from another closed workbook into the data sheet. It then reselects the chart sheet and closes the workbook, which brings the user back to the word document with the chart fully updated.

Independently, each macro works fine, but when I try to cascade them, Object definition issues arise: "run-time error '1004': Method 'Range' of object '_Global' failed". After doing a little research in VB help, I found that for embedded charts, you need to insert a new class module and declare an object of type chart with events. Then you have to connect the declared object in the class module with the embedded chart.

My programming experience is minimal; however, this does sound like the solution. I have no idea how to implement it or whether or not it should be done in the word and/or the excel macro. Can someone please help.

Note: The word macro runs fine and successfully selects and opens the embedded chart... it is the excel macro in which it stops up on right away on the definition issue.

 

[scifibum]

Hmm. I think the VB help you're referring to is mostly connected to using events with charts embedded in an Excel worksheet. If your Excel macro starts to run automatically when the Word macro opens it, then you aren't having a problem with the Event not firing - it's probably another issue.

Can you post your code and specify which line of your code is generating the error?

 

[Steve-0]

Here is the code for the embedded excel chart and data sheet macro that begins upon open.

Private Sub Workbook_Open()

Sheets("Sheet1").Select 'changes active sheet from chart to data
Range("A3").Select 'selects the beginning of data range
With Selection.QueryTable.Name = "Port Count Spreadsheet Macro Test 'Port Count Rollup$'" 'source of import
.FieldNames = True 'data import parameters
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
With Selection.QueryTable 'this is the actual import request
.Connection = Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=S:\IT\2005\IT 2 Network Telecom Group\Port Count Spreadsheet\Port Cou" _
, _
"nt Spreadsheet Macro Test.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Regist" _
, _
"ry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk " _
, _
"Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OL" _
, _
"EDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
)
.CommandType = xlCmdTable
.CommandText = Array("'Port Count Rollup$'")
.Refresh BackgroundQuery:=False
End With
Sheets("Chart1").Select 'reselects the chart
ActiveWorkbook.Close 'closes embedded workbook
End Sub

I get the error on the Range("A3").select