Code to create graph gives a runtime error

[Dharmesh]

Hi, i have some coding which exports a query into Excel and then a graph is manipulated from this. the coding works, but the second time you run it I get a runtime 1004 'global error'

Below is my code, can anyone tell me what the problem is:

Dim myExcel As New Excel.Application
Dim myBook As Excel.Workbook
Dim mySheet As Excel.Worksheet
Dim rs As Recordset
Dim db As Database
Dim sSQL As String
Dim i As Integer

myExcel.Visible = True

Set myBook = myExcel.Workbooks.Add(xlWBATWorksheet)

'SA total
Set mySheet = myBook.ActiveSheet
'MsgBox mySheet.Name
mySheet.Name = "Austria"

Set db = CurrentDb
sSQL = db.QueryDefs("Booking_Curve_Austria").SQL
Set rs = db.OpenRecordset(sSQL)

For i = 1 To rs.Fields.Count
mySheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i

mySheet.Cells(2, 1).CopyFromRecordset rs

'create graph
Range("A1:A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Austria").Range("A1:AB8"), PlotBy _
:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="BookingCurve"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With


'Save sheet
mySheet.SaveAs "P:\Documents\Austria_Booking_curve.xls"

'Close excel file
myBook.Close False
myExcel.Quit


Thanks

Dharmesh

 

[ByteMyzer]

Try the following updated code (added/updated lines are highlighted):

[b]Dim myExcel As Excel.Application[/b]
Dim myBook As Excel.Workbook
Dim mySheet As Excel.Worksheet
Dim rs As Recordset
Dim db As Database
Dim sSQL As String
Dim i As Integer

[b]Set myExcel = New Excel.Application[/b]
myExcel.Visible = True

Set myBook = myExcel.Workbooks.Add(xlWBATWorksheet)

'SA total
Set mySheet = myBook.ActiveSheet
'MsgBox mySheet.Name
mySheet.Name = "Austria"

Set db = CurrentDb
sSQL = db.QueryDefs("Booking_Curve_Austria").SQL
Set rs = db.OpenRecordset(sSQL)

For i = 1 To rs.Fields.Count
mySheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i

mySheet.Cells(2, 1).CopyFromRecordset rs

'create graph
Range("A1:A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Austria").Range("A1:AB8"), PlotBy _
:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="BookingCurve"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With


'Save sheet
mySheet.SaveAs "P:\Documents\Austria_Booking_curve.xls"

'Close excel file
myBook.Close False
[b]Set mySheet = Nothing[/b]
[b]Set myBook = Nothing[/b]
myExcel.Quit
[b]Set myExcel = Nothing[/b]

 

[Dharmesh]

Hi ive put the code in, but im still getting a runtime error 1004.

I get the error on line:

Range("A1:A8").Select


Do you have any ideas.

Thanks

Dharmesh

 

[ByteMyzer]

Forgot to account for the fact that this was not in Excel's immediate VBA environment. Try this for the graph code:

'create graph
With mySheet
    .Range("A1:A8").Select
    .Range(myExcel.Selection, myExcel.Selection.End(xlToRight)).Select
End With
With myBook
    .Charts.Add
    .ActiveChart.ChartType = xlLineMarkers
    .ActiveChart.SetSourceData Source:=.Sheets("Austria").Range("A1:AB8"), _
    PlotBy:=xlRows
    .ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="BookingCurve"
    With .ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End With

 

[Dharmesh]

Hi

Thanks for this, this is working brilliantly. Can you tell me what the problem was with the code.

Thanks

Dharmesh

 

[ByteMyzer]

Your code is in the Access VBA environment, yet your code was written for the Excel environment. Even with Excel references in place, when working with Excel objects such as Range, ActiveChart, etc., you must include the reference to the parent object.

Example:
Range("A1:A8").Select

...becomes:
mySheet.Range("A1:A8").Select