Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed

[josiah.mcdonald]

I've read many similar posts on this subject but it seems this error
can have many different causes. I've created an Access DB that
generates Excel reports (Requirement). It happens on every other
report (Same rpt) beginning with the second one, it only corrects
itself though if press 'END' when the error message comes up. I've
noticed a symptom that tells me this error is going to occur and I
close the Excel file before it finishs and gets to the code that causes
the error. Below is the indication I'm going to get the error:


objRptWS.Activate
objRptWS.Range(objRptWS.Cells(5, 20), objRptWS.Cells(5,
21)).Select
With .Shapes(strBox1).TextFrame
.Characters.Text = " "
objRptWS.Shapes(strBox1).Select
ExecuteExcel4Macro "FORMULA(" & strJudge1 & ")"
.Characters.Font.Size = 12
.Characters.Font.NAME = "ToyotaKV"
.Characters.Font.Bold = True
.HorizontalAlignment = xlHAlignCenter
End With

Every other time is when it kind of skips the ExecuteExcel4Macro line
and does put the formula into the box. This is the indication however
the error happens later on in the code (The code itself is pretty long
so just including snippets if anyone needs more of the code to help me
please let me know). That code is:

For Each objChart In objRptWS.ChartObjects
If StrComp(objChart.NAME, "Chart 1") <> 0 Then
objRptWS.ChartObjects(objChart.NAME).Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.Orientation = 90
Set objChart = Nothing
End If
Next objChart

The ActiveChart statement is the one the debugger highlights as the
culprit. Any help would be greatly appreciated

 

[Peter T]

Without recreating your code I don't think it's necessary to select anything
other than to do the ExecuteExcel4Macro, which can normally be done a
different way -

(untested)

With objRptWS.Shapes(strBox1).
.Drawingobject.formula = "=$A$1"
With .Textframe
'etc

or (subject to no puctuation type characters in the name)
With objRptWS.Texboxes(strBox1).
.formula = "=$A$1"
With .Shaperange.Textframe


For Each objChart In objRptWS.ChartObjects
If StrComp(objChart.NAME, "Chart 1") <> 0 Then
objChart.Axes(xlCategory).TickLabels.Orientation = 90
End If
Next objChart

re "xlCategory", you'd need a reference to Excel to access the named
constants

Regards,
Peter T

 

[josiah.mcdonald]

Thanks Peter,

I tried what you suggested but Access didn't
recognize .drawingObject.Formula, I know you wrote untested so I'm
guessing thats why. Your second solution however did work. So I no
longer get that issue with inputting the formula however when doing
that you showed I actually had two problems instead of 1 because I
still get that error message every other time but the formula fills in
everytime.

I already had the excel reference to xlCategory I use it many times in
the code before that happens, so I don't think thats it. The code
thats giving me the issue doesn't execute 'til right. If you have any
more suggestions they will be greatly appreciated.

However if not thanks a lot for the formula suggestion I'd rather that
work everytime anyway even if the user still has to close the excel
sheet in the event of an error.

 

[Peter T]

I tried what you suggested but Access didn't

recognize .drawingObject.Formula,

Not sure why that doesn't work but I don't think related to Access

myShape.DrawingObject.Formula =
is effectively the same as
myTexbox.Formula =
albeit getting to it from a different approach

The code
thats giving me the issue doesn't execute 'til right. If you have any
more suggestions they will be greatly appreciated.

Difficult to suggest without some idea of what in particular "doesn't
execute 'til right". Try and get it all working in an Excel project first,
and without selecting anything (selecting / de-selecting chartobject charts
is not always straightforward).

Regards,
Peter T

 

[josiah.mcdonald]

Well actually Shapes(shapename).formula doesn't work
either .TextBoxes(BoxName).Formula works don't know why, it doesn't
really matter so much I know there is subtle annoying differences
between VB in excel and using excel VB from access. For example a
really annoying one is how excel uses printer names and how access
does it. In access it just needs the server path but in excel it also
adds where in your printers it is (assuming) :
\\ServerPath on Ne0x (Where x is some number that is unique to
each computer)

I meant to write that, the code doesn't happen 'til right 'at the end'
after the entire report has pretty much been generated its just an
axes formatting script. I agree with you that its because I'm
selecting it its giving me an issue. I'm not really sure how to do it
without selecting it however :

objRptWS.ChartObjects(objChart.NAME).Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.Orientation = 90
Set objChart = Nothing


After using your first suggestion I took all the other sheet and cell
selections out, per your suggestion. It worked great so all thats left
is the above. In general though do you know of a good resource I
could use, besides the reference library that is packaged with access.
Its not very in depth and most books usually focus on one program or
the other not their interaction.

 

[Peter T]

Well actually Shapes(shapename).formula doesn't work

mySheet.Shapes(string-shapename).DrawingObject.formula =

Did you try that as I suggested before

I agree with you that its because I'm
selecting it its giving me an issue. I'm not really sure how to do it
without selecting it however :

objRptWS.ChartObjects(objChart.NAME).Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.Orientation = 90
Set objChart = Nothing

Looks like you already have a reference to the chartobject or chart with
'objChart'. Without selecting, replace all your quoted code above with
simply -

if 'objChart' is a chartobject -
objChart.Chart.Axes(xlCategory).TickLabels.Orientation = 90

if 'objChart' is a chart-
objChart.Axes(xlCategory).TickLabels.Orientation = 90

Oops, reviewing the thread I see 'objChart' is a chartobject, and I had
omitted the .Chart in my original suggestion to avoid selecting.

Regards,
Peter T

 

[josiah.mcdonald]

Peter T,

Thanks a lot it works perfectly now.

Josiah