Strange thing about Excel automation from access

[robojeff]

I have noticed some strange occurances after using automation from access to load excel spreadsheets and am wondering is I am not closing something correctly...

After I am finished doing my automation and saving my file, the next time that I open any excel file, another file seems to open which has the file name of one of my older automated excel files in a different window.

I usually don't notice this but if I click on the window tab on the excel taskbar the name of the past excel file is displayed... sometimes it is from a day or two earlier even though I shut my system down every night...

Any tips of what I can check for to see if I am closing it correctly or not?

Thanks

 

[JoeAtWork]

You are probably not shutting down the instance of Excel you opened in automation. If memory serves correctly, there is a Quit method that should be called.

Post your code where you close down Excel.

 

[robojeff]

Thanks Joe-
I have cut out a portion of the code and added it below. This will create the spread sheed but then a Bookx.xls will also be created and I get a message asking if I want to save that file when I open and close the file (strFile)

Dim strQuery As String, strTemp As String, strFileName As String, strTemp2 As String
'Dim rs As ADODB.Recordset
Dim onoff As Integer, total As Integer
Dim xl As Object ' application
Dim wr As Object ' workbook
Dim sh As Object ' worksheet    Set xl = CreateObject("Excel.Application")
Dim objXL As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim objXLChart As ChartObject
Dim db As Database
Dim rs As Recordset
Dim strFile As String, sheet As Integer
Dim intLoop As Integer
Set objXLBook = objXL.Workbooks.Add

Set db = DBEngine(0)(0)
strFile = "C:\test.xls"

 intLoop = 3

 Set objXLSheet = objXLBook.Worksheets("Sheet" & sheet)

   With objXLSheet         ' create the FPY table header on sheet1
        .Columns("A:E").ColumnWidth = 14
        .Columns("A:E").HorizontalAlignment = xlCenter
        .Columns("A:E").WrapText = True
        .Cells(2, 1).Font.Bold = True
        .Cells(2, 2).Font.Bold = True
        .Cells(2, 3).Font.Bold = True
        .Cells(2, 4).Font.Bold = True
        .Cells(2, 5).Font.Bold = True
        .Cells(1, 1) =  " Rework Data "
        .Cells(2, 1) = "Month/Year"
        .Cells(2, 2) = "Pass Qty"
        .Cells(2, 3) = "Fail Qty"
        .Cells(2, 4) = "FPY Avg."
        .Cells(2, 5) = "Rework Hours"
        
End With

    objXLBook.SaveAs strFile

    On Error Resume Next
    Set objXLChart = Nothing
    Set objXLSheet = Nothing
    Set objXLBook = Nothing
    objXL.Quit
    Set objXL = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
  
End Sub

 

[RoyVidar]

Here are two things to try;

remove

Dim objXLChart As ChartObject

cause it isn't in use, and might cause problems due to it's declaration (should you need charts, check out for instance

http://support.microsoft.com/kb/202169

or

http://support.microsoft.com/kb/142387)

and, before releasing your workbook variable, close the workbook

objXLBook.Close True
Set objXLBook = Nothing

I would also recommend explicit declarations;

Dim db As DAO.Database
Dim rs As DAO.Recordset

Roy-Vidar

 

[PHV]

Sorry, but your code makes no sense for me:
You close a never opened recordset
You use a sheet named Sheet0
...

 

[robojeff]

Thanks Roy & PHV-

My aplogoies if the code doesn't make a lot of sense as I omitted a lot of the code as I felt that most of it was not pertinent to the excel file closure issue... (otherwise, there would have been several pages of code there with graph creation code...)

I hope that I left enough in there to illustrate what I was doing to open & close the spreadsheet which is what Joeatwork requested...

I will check out Roy's links

thanks