Cannot create Excel charts from Access

[msucur]

I am trying to create a chart in en Excel file from Access VBA and am having great troubles with it. I am using Access 2003, SP3 and have Microsoft Excel 12.0 Object Library reference in VBA that is being used in it. My MS Office version is 2007 however. I am able to create an Excel file and put data into it without a problem, but once i start with chart creation the problems ensue. I have an older version of code that I am simply reusing that is quite complex and creates a chart out of a pivot table data that itself uses an external source pointing to Access tables through ODBC. That code works but on a different machine that uses MS Access 2003 with MS Excel 11.0 Object Library and Excel 2003, not 2007. Here is the code that does not work in a shape of two procedures one of which is just a main program that calls the procedure that does the work. The commented out code are all different approaches that I also tried and that did not work. If anybody can tell me why this is happening I would be quite grateful.

Sub TestUpdateCreateOutputSpreadsheet()

' First argument is path so put the directory path of the Excel file there
' The second argument is the name of the excel file (.xls extension is assumed)
OpenDataFileAndCreateChart "C:\Documents and Settings\sg0441667\My Documents\", _
"Output spreadsheet_test"
End Sub

Sub OpenDataFileAndCreateChart(ByVal pathNm As String, ByVal wbNm As String)
'~~~ Required Excel Application object to open new Excel Window ~~~
Dim xl_Output_app As Excel.Application
'~~~ Required Excel Workbook object to open new Excel Workbook with ~~~
'~~~ in the opened Application window ~~~
Dim xl_Output_wb As Excel.Workbook
'~~~ Required Excel Worksheet object to add new Excel Worksheet in ~~~
'~~~ opened Excel Workbook ~~~
Dim xl_Output_ws As Excel.Worksheet
Dim xl_Output_chrt As Excel.Chart

'~~~ Create the application object and assign to the declared object ~~~
Set xl_Output_app = CreateObject("Excel.Application")

'~~~ Application made invisible so that user can't modify the data ~~~
' xl_Output_app.Visible = False

'~~~ Create the Workbook object and add to the declared Workbook object ~~~
Set xl_Output_wb = xl_Output_app.Workbooks.Open(pathNm & "\" & wbNm & ".xls")


'-------------------------------------------
'
' Create graph
'
'-------------------------------------------
' Dim xl_output_srs As Excel.Series

'~~~ Change the name of the second existing worksheet object and use it to create the graph ~~~
' Set xl_Output_ws = xl_Output_app.Sheets(2)
' xl_Output_app.Sheets(2).Activate

' xl_Output_ws.Name = "Graphs"
' Set xl_Output_chrt = xl_Output_ws.ChartObjects.Add
ActiveWorkbook.Charts.Add

' xl_Output_chrt.Location Where:=xlLocationAsObject, Name:=xl_Output_ws.Name
' xl_Output_chrt.SetSourceData Source:=xl_Output_wb.Sheets(1).Range("A1:B59"), PlotBy:=xlColumns
' ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets(1).Range("A1:B59")

' Set xl_output_srs = xl_Output_chrt.SeriesCollection.NewSeries
' xl_output_srs.Values = xl_Output_wb.Sheets(1).Range("B1:B59")
' xl_output_srs.XValues = xl_Output_wb.Sheets(1).Range("A1:A59")
With ActiveChart.SeriesCollection.NewSeries
.Values = xl_Output_wb.Sheets(1).Range("B1:B59")
.XValues = xl_Output_wb.Sheets(1).Range("A1:A59")
End With


xl_Output_wb.SaveAs pathNm & "\" & wbNm & "_1" & ".xls", FileFormat:=Excel.XlFileFormat.xlWorkbookNormal

' Close the Excel workbook and application
xl_Output_wb.Close
xl_Output_app.Quit
Set xl_Output_app = Nothing
End Sub

The code works up to the ActiveChart.Charts.Add statement and starts failing after that .....

Thanks in advance

Milorad

 

[Alisa]

You might get more help if you highlight the exact line of code that is failing, and post the exact error message you receive on that line of code.

 

[msucur]

You might get more help if you highlight the exact line of code that is failing, and post the exact error message you receive on that line of code.

Alisa, thanks for the suggestion. I guess I failed to communicate one of the most important parts. Well, since I posted it originally, with a help from a colleague at work, we were able to come up with a version that works. Still, many unanswered questions remain that I would love to get this forum's perspective.

Unfortunatelly, since I modified the code considerably since I am not 100% sure any more whether it was this line:

With ActiveChart.SeriesCollection.NewSeries

... or the line after that:

.Values = xl_Output_wb.Sheets(1).Range("B1:B59")

where it was failing, because when we modified the code into the following:

With ActiveChart.SeriesCollection.NewSeries
.Values = xl_Output_wb.Sheets("Data").Range("B1:B59")
.XValues = xl_Output_wb.Sheets("Data").Range("A1:A59")
End With

... it started working. So, something was amiss with sheet indexes apparently. The problem that I have with all this is that, for example, that I could not make the following code work:

[FONT=&quot] Set xl_Output_ws = xl_Output_app.Sheets(2)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] xl_Output_ws.Name = "Graphs"[/FONT]
[FONT=&quot] Set xl_Output_chrt = xl_Output_wb.Charts.Add[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] xl_Output_chrt.Location Where:=xlLocationAsObject, Name:=xl_Output_ws.Name[/FONT]
[FONT=&quot] [/FONT] [FONT=&quot] Set xl_output_srs = xl_Output_chrt.SeriesCollection.NewSeries[/FONT]
[FONT=&quot] xl_output_srs.Values = xl_Output_wb.Sheets("Data").Range("B1:B59")[/FONT]
[FONT=&quot] xl_output_srs.XValues = xl_Output_wb.Sheets("Data").Range("A1:A59")[/FONT]

It would again fail at the line that i bolded out and put into italics above and complain that NewSeries method is not available. So, I was able to make it work only using Activechart and I don't know why.

Another piculiar thing about the version that works is that, in the following sequence of commands:

[FONT=&quot] Set xl_Output_ws = xl_Output_app.Sheets(2)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] xl_Output_ws.Name = "Graphs"[/FONT]
[FONT=&quot] ActiveWorkbook.Charts.Add[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] ActiveChart.Location Where:=xlLocationAsObject, Name:=xl_Output_ws.Name[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] With ActiveChart.SeriesCollection.NewSeries[/FONT]
[FONT=&quot] .Values = xl_Output_wb.Sheets("Data").Range("B1:B59")[/FONT]
[FONT=&quot] .XValues = xl_Output_wb.Sheets("Data").Range("A1:A59")[/FONT]
[FONT=&quot] End With[/FONT]


... when the new chart is added and before adding a new series using NewSeries method, one series already exists (is created by default) in the graph and is assigned the A1:B59 range by default I guess. I don't know how that is possible ...

The second thing I noticed is that when I bring the ActiveChart variable into watch window during the debugging process, I see no SeriesColleciton property there. Is that normal? However, when I bring ActiveChart.SeriesCollection into watch, I can browse it ... Go figure.

Any comments welcome and thanks to everybody who read this for your patience with my post.

Milorad