Closing Excel from Access VBA

[sparkyrose]

Hi all,

My db exports a query to an excel file (specifically a .csv file) with address data. I need to format the zip code column to the Excel Zip Code format before saving (otherwise leading zeros are omitted).

After searching this forum I found an old discussion (from 2005) that seemed like it would work but I'm having a problem closing the instance of Excel that is created in the code. The application hangs until I manually close it in Task Mgr.

Stepping through the code line by line it is fine until the Close / Quit part (see below).

If anyone can help I'd be very grateful.

This is my code:

Public Sub ModifyExportedExcelFileFormats(sFile As String)
'On Error GoTo Err_ModifyExportedExcelFileFormats

    Application.SetOption "Show Status Bar", True

    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")

    Dim xlApp As Object
    Dim xlSheet As Object
 
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
    
    With xlApp
            .Application.Sheets("BatchProcess").Select
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Columns("I:I").Select
            .Application.Selection.EntireColumn.NumberFormat = "00000"
            .Application.Activeworkbook.Save
            .Application.Activeworkbook.Close
            .Application.Quit
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing
    
    
    vStatusBar = SysCmd(acSysCmdClearStatus)

Exit_ModifyExportedExcelFileFormats:
    Exit Sub

'Err_ModifyExportedExcelFileFormats:
 '   vStatusBar = SysCmd(acSysCmdClearStatus)
  '  MsgBox Err.Number & " - " & Err.Description
   ' Resume Exit_ModifyExportedExcelFileFormats

End Sub

It gets called from a separate Sub as follows:

DoCmd.TransferText acExportDelim, , "qryUPSExportFirst", "S:\Programming\MFNDb\BatchProcess.csv", False

Call ModifyExportedExcelFileFormats("S:\Programming\MFNDb\BatchProcess.csv")

 

[Rx_]

Apply this concept and let us know.

With xlApp
.Application.Sheets("BatchProcess").Select
.Application.Cells.Select

Should be:
With xlApp
.Sheets("BatchProcess").Select
.Cells.Select

The xlApp is already the Application - calling it a second time might affect it adversly.
dim strSaveAsFileName
strSaveAsFileName = "C:\*** file location and name ***** "
xlApp.ActiveWorkbook.SaveAs FileName:=strSaveAsFileName
xlApp.Visible = False
xlApp.Workbooks.Close
xlApp.Quit

 

[sparkyrose]

Been a while before I could get back to this.

Thanks, this worked fine and my code now runs smoothly. Oddly, when I put a breakpoint in and step through I get the same issue as described above. When I just call it from the form, however, it runs perfectly.

Any clue why that would be? Not a big deal as I have it working; just curious.

Thanks again!