Shutting Excel Server down after exporting spreadsheet

  • Thread starter Thread starter Ian Millward
  • Start date Start date
I

Ian Millward

Dim oXl As Object, oWb As Object
Dim fName As String
After creating a new Excel file with the following code, if I try to
re-export the data and overwrite the previous version, an error is reported
executing the column format routine and I cannot use or delete the Excel
file. Also, I have to shut the server down using Windows Task Manager to use
the spreadsheet.
The routine does not make Excel visible so I am closing the file and
destroying the object variable by code but it leaves the Server active. Any
hints what I am doing wrong?

I am using W2k and Office 2000

//
fName = "C:\Fred.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryMain",
fName, True

Set oXl = CreateObject("Excel.Application")
Set oWb = oXl.Workbooks.Open(fName)

'Adjust column widths and make headers bold
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
Selection.Columns.AutoFit
Range("A1").Select


oWb.Save
oWb.Close False
Set oWb = Nothing
oXl.Quit
Set oXl = Nothing

End Sub
//
 
One area that might be worth checking is your
declarations. I update and run Excel out of Access, but
I use different declarations, e.g.

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range

By properly shutting the apps after update, e.g.

xlApp.Quit
Set xlRng = Nothing
Set xlSht = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing

I have not encountered any errors you have.

Lucky

P.S. If you going to use the above mentioned
declarations, do not forget to check Microsoft Excel 9.0
Object Library in your references.
-----Original Message-----
Dim oXl As Object, oWb As Object
Dim fName As String
After creating a new Excel file with the following code, if I try to
re-export the data and overwrite the previous version, an error is reported
executing the column format routine and I cannot use or delete the Excel
file. Also, I have to shut the server down using Windows Task Manager to use
the spreadsheet.
The routine does not make Excel visible so I am closing the file and
destroying the object variable by code but it leaves the Server active. Any
hints what I am doing wrong?

I am using W2k and Office 2000

//
fName = "C:\Fred.xls"

DoCmd.TransferSpreadsheet acExport,
Click to expand...
acSpreadsheetTypeExcel9, "QryMain",
 
I am not sure how TransferSpreadsheet behaves if the destination file
already exists. I would recommend deleting the existing file (if any) at
the beginning of your code.
 
Back
Top