Excel Invisible after Automation

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
 
G

Guest

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 '<-
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top