Passing Excel Objects as parameters within Access VBA

[vic]

Hi,

I'm creating Excel spreadsheets within an Access application. When the
following code was all in the same Sub, it worked.
Set xlApp = CreateObject("Excel.Application")
Set xlWrkbk = xlApp.Workbooks.Open(FileLocation)
Set xlChartObj = xlApp.Charts.Add
xlWrkbk.ActiveChart.ChartType = xlPie
xlWrkbk.ActiveChart.SetSourceData Source:=xlSourceRange,
PlotBy:=xlColumns
xlWrkbk.ActiveChart.Location Where:=xlLocationAsObject,
name:=ChartSheetName
xlWrkbk.ActiveChart.HasTitle = True
xlWrkbk.ActiveChart.PlotArea.Height = PlotAreaSize
xlWrkbk.ActiveChart.PlotArea.Width = PlotAreaSize
But, when I moved the top two lines (Set xlApp and Set xlWrkbk) to a
different procedure, and passed the xlApp and xlWrkbk as parameters to
the original procedure, I start getting errors. Error is: "1004,
Method 'HasTitle' of object '_Chart' failed". The next error is:
"1004, Unable to get the Height property of the PlotArea class."
When passing the Excel objects as variables, I'm using ByRef xlApp as
Excel.Application.

Any ideas what I'm doing wrong here.

Thanks,
Vic

 

[Guest]

Assuming your xl objects are all in the same form or module, dim them at the
module level. That way they will have visibility to any procedure in the
module.

 

[vic]

The dim does not seem to be the problem. I have then at the module
level now, and the errors are still the same. Only some of the lines
of code are getting the errors. Some lines seem to continue to work as
before.

Any other ideas?

thanks

 

[Guest]

Because proper referencing of excel objects is weird, I can only hazzard a
guess.

First, if your xl objects are dimmed at the module level, it would not be
nessary to pass the reference as a parameter. It is possible it is getting
confused; however, since the errors were already there, I doubt it.

One other thing I see, is that there is not association between the chart
and the workbook:
Set xlApp = CreateObject("Excel.Application")
Set xlWrkbk = xlApp.Workbooks.Open(FileLocation)
Set xlChartObj = xlApp.Charts.Add
xlWrkbk.ActiveChart.ChartType = xlPie
xlWrkbk.ActiveChart.SetSourceData Source:=xlSourceRange,
PlotBy:=xlColumns
xlWrkbk.ActiveChart.Location Where:=xlLocationAsObject,
name:=ChartSheetName

Instead of:
Set xlChartObj = xlApp.Charts.Add
how about;
Set xlChartObj = xlWkrbk.Charts.Add

 

[vic]

I changed the xlApp to xlWrkbk on the Charts.Add line, but still get
the same error messages. Your quote "proper referencing of
excelobjects is weird" is sooooo true! Glad to know that I'm not
crazy.

Any more ideas would be appreciated. I really need to get this thing
working and really don't want the same code is several different
procedures.

Thanks

 

[Larry Daugherty]

When things get complex and confusing, I try to simplify what I'm
doing.

Since you are wrestling with an apparent Excel problem, I'd copy the
code over to Excel as a macro and get it to work over there. Once it
works when entirely within Excel I'd move it back to Access, make the
adjustments for the Access environment and try it again.

HTH

 

[RoyVidar]

(e-mail address removed) wrote in message

I changed the xlApp to xlWrkbk on the Charts.Add line, but still get
the same error messages. Your quote "proper referencing of
excelobjects is weird" is sooooo true! Glad to know that I'm not
crazy.

Any more ideas would be appreciated. I really need to get this thing
working and really don't want the same code is several different
procedures.

Thanks

Back in the original code, you declare and instantiate a chart object
variable, but you don't use it, in stead you use .ActiveChart.

Set xlChartObj = xlApp.Charts.Add
xlWrkbk.ActiveChart.ChartType = xlPie
xlWrkbk.ActiveChart.SetSourceData Source:=xlSourceRange,
PlotBy:=xlColumns

Have you tried working with the chart object

Set xlChartObj = xlWrkbk.Charts.Add
xlChartObj.ChartType = xlPie
xlChartObj.SetSourceData Source:=xlSourceRange,
PlotBy:=xlColumns

I don't know exactly what happens, so the below is just a thought, but
I think that sometimes there's a mixup somewhere when you create and
instantiate an object, as you do with the chart object, but then
reference the object through other means. Again, I think that might
sometimes, under some conditions, be seen as an implicit reference by
Excel, and give the 1004, and other automation errors. But again, I
don't know for sure.