Excel constants in access automation

[TheTrainMan]

I looked on microsoft's website to help me out, followed what I found out... and it still didn't work!!!!

I want to know how to reference excel constants from access during automation. I'm getting a "Variable Not Defined" message, when (according to what I got off the MS website) I shouldn't!!

for example, I add a new chart, but want to change the chart type

MyChart.ChartType = excel.xlColumnStacked

brings up the error message.

Using the object browser I can find out the actual constants (xlColumnStacked is 52!), but I'd prefer not to have to go through and change all the constants - I'd rather have a meaningful term there! I could go through change everything and leave a comment in my code i.e.

MyChart.ChartType = 52 'Stacked Column

but surely the constants are there so we don't have to resort to this!!!!

What am I doing wrong?

 

[chenn]

Are you automating excel? I imagine that your "Variable Not Defined" is highlighting your code line "MyChart.ChartType = excel.xlColumnStacked".

Dim objExcel as object
Set objExcel = CreateObject("Excel.Application")

... then you should be able to set a reference to your chart and change the properties of the column.