1004 the specified dimension is not valid for the current chart type

[sebmorris]

Hi guys

I've been stuck with this problem for a while now and was hoping one of you could help out .

The problem code is shown below and is being run from a word document which reads an excel file (myWB - opened with a GetObject call).

With myWB.Sheets("Graphs").ChartObjects(chartreq%).Chart
        'Name of the chart is correct
        temp$ = .Name
        .ChartType = Excel.XlChartType.xlColumnClustered

        'error occurs on the line below (1004 the specified dimension is not valid for the current chart type)
        .CopyPicture Appearance:=xlScreen, Size:=xlPrinter, Format:=xlPicture
        
        'pasting chart etc...
    End With

The graph is correct in the excel document and the type is the same as the .ChartType i set.

Any help would be greatly appreciated, no idea where to go from here and ive tried many different approaches.

 

[sebmorris]

Hi

I am able to replicate the error with this code:

Sub showgraph()

Dim myWB As Excel.Workbook
Dim mychart As ChartObject
Dim spsheetfilename$

On Error GoTo Handler

spsheetfilename$ = "C:\Work\360s\sample-files\new\1 graph.xlsx"
Set myWB = GetObject(spsheetfilename$)

'Correct number of charts below
numberofcharts% = myWB.Sheets("Graphs").ChartObjects.Count
Set mychart = myWB.Sheets("Graphs").ChartObjects(1)
With mychart.Chart
    .CopyPicture Appearance:=xlScreen, Format:=xlPicture
End With

Handler:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical

End Sub

I am running the above code via a .docm word file (references: http://i.imgur.com/jDsKPor.png) and it is opening '1 graph.xlsx' (http://i.imgur.com/nLxRJdR.png).

Macro file on dropbox: https://www.dropbox.com/s/flzfgev2o9v5c5b/1 graph macro.docm?dl=0
Excel file on dropbox: https://www.dropbox.com/s/yer18dfl7anm8d5/1 graph.xlsx?dl=0

Thanks again
Seb

 

[Caleeco]

Hi Seb,

Thanks for uploading some sample files, it will make it easier to troubleshoot! I'm on my lunch break at the moment, but will take a look at it this evening once I'm back from work.

Thanks
Caleeco

 

[sebmorris]

Thanks. Really appreciate the help

 

[sebmorris]

Did you have a chance to look over the files ?

 

[Caleeco]

Hi Seb,

Sorry I didn't get a change to look at this yesterday. Currently making some updates to my website, which is a black-hole for time! I will endeavour to take a look this evening.

Cheers
Caleeco

 

[sebmorris]

I believe I solved the problem (related to how I was opening the workbook, which is strange considering text was correctly extracted from the wb, but graphs were not). I'll post a snippet later for anyone browsing the topic.

Thanks again for all your help

 

[Caleeco]

I believe I solved the problem (related to how I was opening the workbook, which is strange considering text was correctly extracted from the wb, but graphs were not). I'll post a snippet later for anyone browsing the topic.

Thanks again for all your help

Ah nice! Glad you got it sorted, thanks for letting me know

Yeah will be worthwhile posting working code for anyone facing a similar problem in the future

Caleeco