code gives runtime error

[Dharmesh]

Hi

I have some coding which runs the first time, but on the second go, it gives a runtime error 1004. Does anyone know the problem.

Thanks

Dharmesh

Private Sub Command0_Click()
Dim myExcel As 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

Set myExcel = New Excel.Application
myExcel.Visible = True

Set myBook = myExcel.Workbooks.Add(xlWBATWorksheet)

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

Set db = CurrentDb
sSQL = db.QueryDefs("All_Standards_sales_agreements_Austria_Total").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

'Delete column g
Range("A1").Select
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

'Add subtotals and totals
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

'SA Weekly
Set mySheet = myBook.Worksheets.Add
mySheet.Name = "SA Weekly"

Set db = CurrentDb
sSQL = db.QueryDefs("All_Standards_sales_agreements_Austria_Weekly").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

'Delete column g
Range("A1").Select
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

'Add subtotals and totals
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

'SO Total
Set mySheet = myBook.Worksheets.Add
mySheet.Name = "S0 Total"

Set db = CurrentDb
sSQL = db.QueryDefs("All_Standards_sales_orders_Austria_Total").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

'Delete column g
Range("A1").Select
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

'Add subtotals and totals
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

'SO Weekly
Set mySheet = myBook.Worksheets.Add
mySheet.Name = "SO Weekly"

Set db = CurrentDb
sSQL = db.QueryDefs("All_Standards_sales_orders_Austria_Weekly").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

'Delete column g
Range("A1").Select
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

'Add subtotals and totals
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

'Booking curve Austria
Set mySheet = myBook.Worksheets.Add
'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
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

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

'Close excel file
myBook.Close False
Set mySheet = Nothing
Set myBook = Nothing
myExcel.Quit
Set myExcel = Nothing
End Sub

 

[aziz rasul]

You probably have an instance of Excel running the second time around. If u look into the task manager (under the Processes tab), you will see it.

You have quit Excel and set it to nothing which is what I do and it works. Hence can't see anything obviously wrong.

 

[Dharmesh]

Hi

I have set excel to nothing, but a problem still occurs.

Dharmesh

 

[aziz rasul]

Did u check the Processes tab to c if there was an instance of Excel.exe. If so and u select and press 'End Process', you will see that the code will work but stop working the next time.

 

[RoyVidar]

You are using unqualified references to Excel objects, properties or methods. Every reference to excel objects, properties or methods needs needs to be "anchored" to an object.

Here the challenge is within the following code, I think

Range("A1").Select
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Try (untested)

With mySheet
    .Columns("G:G").Delete Shift:=xlToLeft
    .Range("A1").Select
End With
myExcel.Selection.Subtotal GroupBy:=2, Function:=xlSum, _
    TotalList:=Array(4, 5, 6), Replace:=True, PageBreaks:=False, _
    SummaryBelowData:=True

Here is a bit more info http://support.microsoft.com/default.aspx?kbid=178510

Hint, hint, to make the code more readable/display indentation, check out the "vB code is On" link at the bottom of the pages, or use the octothorpe (#) button in the reply pane.