Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Importing / Linking / August 2005

Tip: Looking for answers? Try searching our database.

Excel Invisible after Automation

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
vseale - 05 Aug 2005 23:34 GMT
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
vseale - 11 Aug 2005 00:50 GMT
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 '<-

> 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
[quoted text clipped - 47 lines]
>         Xl.Quit
>     End If
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.