[AccessD] Excel Automation Problem - spreadsheet is hidden

Thu Apr 15 08:13:50 CDT 2010

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

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

I use Dim xlBook As Excel.Workbook (it's an existing workbook)

And set it thusly: Set xlBook = GetObject(Me.txtSpreadsheet) where Me.txtSpreadsheet has the path and name of the workbook.

R

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

Have you instatiated it ?

 Dim xlBook As NEW Excel.Workbook

Or

Dim xlBook As Excel.Workbook

Set xlbook as new excel.workbook

Just guessing?

Max

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

Lambert:

Tried that first but it barfs on  xlBook.Visible = True where xlBook is
defined: Dim xlBook As Excel.Workbook

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 12:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel Automation Problem - spreadsheet is hidden

Hmm. I took a closer look at that site and noticed that the coder has not even declared the variable wkbNewBook, and the code is evidently written to run in Excel VBA. 

However, in your code, assuming that xlBook is an Excel.WorkBook object, you should be able to write this...

' close spreadsheet
    Set xlSheetProductionPlan = Nothing
    Set xlSheetBOMs = Nothing

    xlBook.Visible = True
    xlBook.Save
    xlBook.Close

    Set xlApp = Nothing
    Set xlBook = Nothing

MsgBox "Export Complete.", vbExclamation

End Sub 

Lambert

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

Lambert:

I get Object does not support this property or method on the .Visible = True

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 11:44 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel Automation Problem - spreadsheet is hidden

This looks like a possible solution...

http://www.vbforums.com/archive/index.php/t-411430.html

Lambert 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com