[AccessD] Excel Automation Problem - spreadsheet is hidden

Lambert,

    That is a nice way to do it. I like your approach of using the function Excel_OpenWorkBookHidden() in conjunction with its helper function IsExcelRunning(). 

    Interestingly, I have never encountered a behavior of the type reported by Rocky. Thanks to the outstanding suggestion posted by Mark (xlApp.Windows(1).Visible = True), Rocky's problem stands resolved as confirmed by him.

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Heenan, Lambert 
  To: Access Developers discussion and problem solving 
  Cc: A.D. Tejpal 
  Sent: Thursday, April 15, 2010 23:15
  Subject: RE: [AccessD] Excel Automation Problem - spreadsheet is hidden


  A.J.

  For me that happens in a related function...

  Function Excel_OpenWorkBook(Path As String, Optional UpdateLinks As Boolean = False, Optional Password As String = "") As Excel.Application
  Dim xlObj As Excel.Application
      On Error GoTo Excel_OpenWorkBook_err

      Set xlObj = Excel_OpenWorkBookHidden(Path, UpdateLinks, Password)

      If xlObj.Name > "" Then xlObj.Visible = True

      Set Excel_OpenWorkBook = xlObj
  Excel_OpenWorkBook_exit:
      Exit Function
  Excel_OpenWorkBook_err:
      ReportError Err.Number, Err.Description, "Excel_OpenWorkBook", "Excel_mod", "File Name=" & Path
      Set Excel_OpenWorkBook = Nothing
      Resume Excel_OpenWorkBook_exit
  End Function

  Often I want to open an Excel file and pull some data from it and then close the file. There's no need to the user to see the file on screen, hence the function Excel_OpenWorkBookHidden.

  Lambert

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
  Sent: Thursday, April 15, 2010 1:28 PM
  To: Access Developers discussion and problem solving
  Cc: A.D. Tejpal
  Subject: Re: [AccessD] Excel Automation Problem - spreadsheet is hidden

  Rocky,

      It is presumed that after setting the object variable xlApp to excel application, you have the following statement:

      xlApp.Visible = True

  Best wishes,
  A.D. Tejpal
  ------------

    ----- Original Message -----
    From: Heenan, Lambert
    To: Access Developers discussion and problem solving
    Sent: Thursday, April 15, 2010 18:43
    Subject: Re: [AccessD] Excel Automation Problem - spreadsheet is hidden


    I honestly don't know but it might be worth a try. 

    Lambert 

    -----Original Message-----
    From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
    Sent: Wednesday, April 14, 2010 1:26 PM
    To: 'Access Developers discussion and problem solving'
    Subject: Re: [AccessD] Excel Automation Problem - spreadsheet is hidden

    Do you think that will make a difference in the hidden problem?

    Rocky
     

    -----Original Message-----
    From: accessd-bounces at databaseadvisors.com
    [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
    Sent: Tuesday, April 13, 2010 5:14 PM
    To: Access Developers discussion and problem solving
    Subject: Re: [AccessD] Excel Automation Problem - spreadsheet is hidden

    I open workbooks a quite different way. The essence of it is...

    Function Excel_OpenWorkBookHidden(Path As String, _
    Optional UpdateLinks As Boolean = False, _
    Optional Password As String = "") As Excel.Application

    Dim xlApp As Excel.Application

    If IsExcelRunning() Then
    Set xlApp = GetObject(, "Excel.Application")
    Else
    Do
    Set xlApp = CreateObject("Excel.Application")
    Loop Until IsExcelRunning()
    End If

    xlApp.Workbooks.Open Path, UpdateLinks, , , Password
    Set Excel_OpenWorkBookHidden = xlApp
    End Function


    And the helper function is 

    Function IsExcelRunning() As Boolean
        Dim xlApp As Excel.Application
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        IsExcelRunning = (Err.Number = 0)
        Set xlApp = Nothing
        Err.Clear
    End Function

    So in my client code I declare an Excel.Application object...

    Dim xlApp as Excel.Application

    Then I set it with

    Set xlApp = Excel_OpenWorkBookHidden(strSomePath)

    And I access the worksheets with

    Dim xlWs as Excel.Worksheet

    Set xlWs = xlApp.Worksheets(1)

    Notice that the client code does not even use a workbook object. I've not had any serious trouble with that, and no issues with hidden worksheets.

    Lambert