Embedded workbook obj definition issue: CHALLENGING!

Steve-0

New Member
Joined
Jan 25, 2005
Messages
3
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
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

New Member
Joined
Jan 25, 2005
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

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