Problem with Excel while using Access

[FLabrecque]

Thanks in advance for any help you can provide me with. As some of you may know (I've posted about this before), I export a lot of queries/table to Excel.

I saw in the sample database section, someone posting code about using an Excel object through VBA in MS-Access. Exactly what I need to make my life simpler! I want to offer basic formating for queries I output.

My problem is this: The following code works perfectly (I excluded the formating code, not to bore you with it). After it ran, if I try to open my XLS file while Access is still opened, a blank XLS window appears (I can not see the content, althought the title bar contains the name of my file). If I close Access, close Excel, and re-open my file, no problema! Is there a line of code I forgot?

Set xlsFile = CreateObject("Excel.Application")
xlsFile.Workbooks.Open strFile
xlsFile.ActiveWorkbook.Save 'Saves the spreadsheet
xlsFile.ActiveWorkbook.Close 'Closes Excel Spreadsheet
xlsFile.Quit 'Closes Excel

Set xlsFile = Nothing

 

[FLabrecque]

After some analyzing... I believe it has something to do with the processes. It's like it open a process, that doesn't shutdown. Still has no clue how to solve this.

 

[RoyVidar]

Some of the code is implicit referencing excel objects. Try the alterations below.

dim xlsWR as object ' or excel.workbook if you're using early binding
Set xlsFile = CreateObject("Excel.Application")
set xlsWR=xlsFile.Workbooks.Open(strFile)
xlsWR.Save
xlsWR.Close
xlsFile.Quit
set xlsWR = nothing
set xlsFile=nothing

But the code you didn't want to bother us with, probably also contains unqualified referencing of Excel objects, methods and properties, and may continue to leave an Excel instance in memory. Have a look here

http://support.microsoft.com/default.aspx?kbid=178510

If you still can't get it to work, post your complete code, too.

 

[FLabrecque]

This is great advice! I'll try it and let you know.

 

[FLabrecque]

You were right, it was the lines in between that caused the problem. Thanks for the link to Microsoft's web site. It was very useful. For people out there who have the same problem, let me cut and paste the working code:

Set xlsFile = CreateObject("Excel.Application")
Set xlsWR = xlsFile.Workbooks.Open(strFile)
Set xlsSheet = xlsWR.Worksheets(strQuery)

xlsFile.Visible = False

blnData = False
i = 0
Do While blnData = False
i = i + 1
If xlsSheet.Cells(1, i).Value = vbNullString Then
iCol = i - 1
blnData = True
End If
Loop

'Orientation + Alignement
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, iCol)).Orientation = xlUpward
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, iCol)).HorizontalAlignment = xlVAlignCenter

'Mettre la bordure et la couleur de fond
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, iCol)).Borders.Value = True
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, iCol)).Interior.ColorIndex = 15

xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, iCol)).EntireColumn.AutoFit
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, 1)).EntireRow.AutoFit

xlsWR.Save
xlsWR.Close
Set xlsSheet = Nothing
Set xlsWR = Nothing
xlsFile.Quit
Set xlsFile = Nothing