[AccessD] Excel Automation Problem - spreadsheet is hidden

Heenan, Lambert Lambert.Heenan at chartisinsurance.com
Thu Apr 15 12:45:40 CDT 2010


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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list