Questions on this code

[RonaldoOneNil]

I am writing data from my access database into an Excel spreadsheet.
In the code snippets below can anyone tell me why the following happens ?

1. When the routine has finished, why is Excel still in my process list if I
look using Ctrl-ALT-Del - Task manager ? If I exit access it has gone from
the process list.

2. Sometimes, but not always, it breaks on the sheets.Add line with either
of the following messages Error 462 remote server is not available or Error
1002 Add method of sheets _Global failed ??

Public xlExcel as Excel.Application
Public xlBook as Excel.Workbook
..
..
Dim xlServSheet as Excel.Worksheet

Set xlExcel = CreateObject("Excel.Application")
Set xlBook = xlExcel.Workbooks.Open("C:\temp\test.xls")
..
..
Set xlServSheet = xlBook.Sheets.Add(After:=Worksheets(Worksheets.Count))
..
..
xlExcel.Quit
Set xlServSheet = Nothing
Set xlBook = Nothing
Set xlExcel = Nothing

 

[Mr. B]

Try:

xlBook.close
xlExcel.Quit
Set xlServSheet = Nothing
Set xlBook = Nothing
Set xlExcel = Nothing

Also, you stated that you did not post the entire code so you may have
already covered this base, but you might want to consider using code that
will detect if Excel is already running and if so use the existing instance
of Excel. Here is link that will provide some code for this:
http://www.mvps.org/access/api/api0007.htm

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[RonaldoOneNil]

I didn't include the line of code originally but I have this line
xlBook.Close SaveChanges:=True
before xlExcel.Quit

Thanks for the other suggestion but I never have Excel running when I
execute my code. I can still get the error messages on the add worksheet part.
I have tried using Sheets.Add as well as Worksheets.Add

 

[RonaldoOneNil]

Further to my post, the error was 1004 not 1002 as I originally stated. Also
if I do not add any worksheets to my workbook then the routine works every
time and no instance of Excel is left running at the end of the process ??
What is the correct syntax for adding a worksheet at the end in your
existing workbook ?

 

[RonaldoOneNil]

I've fixed the problem. You have to explicitly reference the object on the add
This line
Set xlServSheet = xlBook.Sheets.Add(After:=Worksheets(Worksheets.Count))
needs changing to this
Set xlServSheet =
xlBook.Worksheets.Add(After:=xlBook.Worksheets(xlBook.Worksheets.Count))