Runtime error 462 with Excel

[TUSSFC]

I'm exporting the contents of a query to an excel file, opening the file and attempting to make some basic modifications to it. Then saving and closing the file.

I'm have INTERMITTENT issues - sometimes the export and modifications works fine without any errors. Sometimes I get runtime error 462. It's a bit hit & miss, but I think the error occurs if I run the code twice during one "session". If I close the form which calls the code and open and try the command button again - it works. If I don't close/open and just run it twice, I get the error.

My code is below.

Furthermore - and this probably qualifies as a seperate problem - the times it does work, an instance of excel.exe is left open in Task Manager. I'm sure the code is correct for killing these off at the end!

Any help?

Private Sub Command_Daily_Click()
 
Dim strOutput As String
 
strOutput = "D:\Documents and Settings\All Users\Desktop\Daily_Stats.xls"
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DailyStatsExport_Crosstab", strOutput, False
 
Dim objExcel As Excel.Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet
 
Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.DisplayAlerts = False
objExcel.EnableEvents = False
objExcel.ScreenUpdating = False
 
Set xlBook = Workbooks.Open(strOutput)
 
xlBook.Worksheets("qry_DailyStatsExport_Crosstab").Name = "Daily Stats"
 
Set xlSheet = xlBook.Worksheets("Daily Stats")
 
xlSheet.Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
 
xlBook.Save
 
objExcel.DisplayAlerts = True
objExcel.EnableEvents = True
objExcel.ScreenUpdating = True
 
xlBook.Close
Set xlBook = Nothing
 
objExcel.Application.Quit
Set objExcel = Nothing
 
MsgBox "Todays report has been saved to your desktop."
 
End Sub

 

[chergh]

Sounds like http://support.microsoft.com/kb/178510/EN-US/


I've had a quick look and made a few example changes in bold.

Private Sub Command_Daily_Click()
 
Dim strOutput As String
 
strOutput = "D:\Documents and Settings\All Users\Desktop\Daily_Stats.xls"
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DailyStatsExport_Crosstab", strOutput, False
 
Dim objExcel As Excel.Application
Dim xlBook As [B]excel.[/B]Workbook
Dim xlSheet As [B]excel.[/B]Worksheet
 
Set objExcel = [B]new excel.application[/B]

objExcel.Visible = True
objExcel.DisplayAlerts = False
objExcel.EnableEvents = False
objExcel.ScreenUpdating = False
 
Set xlBook = [B]objExcel.[/B]Workbooks.Open(strOutput)

 

[TUSSFC]

Thanks for the reply. I seem to be getting somewhere. I am still getting the same error - but further on in the code. It now highlights this row:

Selection.Insert Shift:=xlDown

I must say I was unsure about this syntax as I copied it from the Excel macro recorder. Do I need to prefix this with something?

 

[chergh]

Try changing it to:

xlSheet.Rows("1:1").Insert Shift:=xlDown
xlSheet.Rows("1:1").Insert Shift:=xlDown

 

[TUSSFC]

Hi

I got it to work another way:

objExcel.Selection.Insert Shift:=xlDown

needed the Excel object reference. Also fixed the excel.exe open process which had been hanging around which makes sense.

Thanks anyway