Access and Excel VBA issue

[thegameoflife]

In Access I have a module that exports the xls file then formats the file. After the code below formats the xls file I can't open the file.

Sub test()
Dim xlApp As Excel.Application

    
    ' Create new hidden instance of Excel.
    Set xlApp = Excel.Application
    
    ' Show this instance of Excel.
    xlApp.Visible = False
    With xlApp
        ChDir "C:\Recoveries"
    Workbooks.Open FileName:="C:\Recoveries\HRI Recoveries for Mar 02.xls"
    ActiveCell.FormulaR1C1 = "Region & Product"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Recovered"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Fee"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Net"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Columns("B:D").Select
    Selection.NumberFormat = "$#,##0.00"
    Range("A1").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""

    End With
    xlApp.Quit
    Set xlApp = Nothing
End Sub

 

[nat1967]

I tried your code and had the same issue you described. After review, I think I might have found the problem. Your Excel file needs "Save" before the App can quit.

Add this to your code.

'***************************
End With
xlApp.SaveWorkspace
xlApp.Quit
Set xlApp = Nothing
'***************************

With the xlApp.SaveWorkspace added, I was able to run the code and open the save file.

HTH

Nathan