Excel Invisible after Automation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new to using Automation and am missing something in my code. My code
exports a report to Excel, formats the spreadsheet, saves and closes. After
I run the code, opening any Excel file from Explore or desktop shortcuts
stays invisible. I have to open a new instance of Exel from Start and load
from within Excel. Attached below is my code. Thanks in advance for the
great help!

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim bXL As Boolean

'***export report to spreadsheet
DoCmd.OutputTo acOutputReport, "FakeReportName", "Microsoft Excel",
strFileName

'***Open an instance of Excel, open the workbook
If fIsAppRunning("Excel") Then
Set Xl = GetObject(, "Excel.Application")
Set XlBook = GetObject(strFileName)
bXL = False
Else
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(strFileName)
bXL = True
End If

'***Must be visible in order to process
Xl.Visible = True
XlBook.Windows(1).Visible = True

'***Define the topmost sheet in the Workbook as XLSheet
Set XlSheet = XlBook.ActiveSheet

'***format spreadsheet
XlSheet.Range("A:A").ColumnWidth = 6.71
XlSheet.Range("B:B").EntireColumn.AutoFit
XlSheet.Range("B:B").NumberFormat = "m/d/yy"
XlSheet.Range("A3").Select
ActiveWindow.FreezePanes = True
XlSheet.Range("A1").Select

'***Save updated file & close active workbook
'***If Excel previously opened, minimize, else close
Xl.ActiveWorkbook.Save
If bXL = False Then
Xl.ActiveWorkbook.Close
Xl.Application.windowstate = xlMinimized
Else
Xl.Quit
End If
 
Found the answer. Thanks to the book "VBA and Macros for Microsoft Excel" by
Bill Jelen and Tracy Syrstad. I needed to add this line after I saved the
excel file -
Xl.ActiveWorkbook.Save
Xl.ScreenUpdating = True '<-
 
Back
Top