Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed
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 wrote in message oups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed
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.Orienta tion = 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 wrote in message oups.com... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed
Peter T, Thanks a lot it works perfectly now. Josiah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 Method 'Range' of object '_Global' failed | Excel Programming | |||
runtime error 1004 method range of object '_global failed | Excel Discussion (Misc queries) | |||
runtime error '1004' object '_Global' failed | Excel Programming | |||
Runtime Error 1004 - Method Range of '_Global failed' | Excel Programming | |||
Error 1004: Method 'Cells' of object '_Global' failed | Excel Programming |