[AccessD] Open Excel workbook from Access 2000

Read-Only: you need to change your method of opening the file. 

In fact the method you are using right now does not open the file at all, it opens a copy of the file using it as a template to create a NEW excel file. You can tell that this is so by looking at the tile bar in Excel: it will show the name of the original Excel file, but with a digit "1" appended.

This modification of your code opens the ORIGINAL file and also optionally does so ReadOnly

Public Sub OpenSheet(ByVal strWB As String, ByVal strSheet As String, boolReadOnly As Boolean)

    Dim excelApp As Excel.Application
    Dim excelSheet As Worksheet
    
    Set excelApp = New Excel.Application
    excelApp.Workbooks.Open strWB, , boolReadOnly
    Set excelSheet = excelApp.Worksheets(strSheet)
    excelSheet.Activate
    excelApp.Visible = True
End Sub


HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe O'Connell
Sent: Monday, December 21, 2009 2:17 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Open Excel workbook from Access 2000

Thanks to all who responded.  This is what works:

Public Sub OpenSheet(ByVal strWB As String, ByVal strSheet As String)

    Dim excelApp As Excel.Application
    Dim excelWb As Workbook
    Dim excelSheet As Worksheet
    
    Set excelApp = New Excel.Application
    Set excelWb = excelApp.Workbooks.Add(strWB)
    Set excelSheet = excelWb.Worksheets(strSheet)
    excelSheet.Visible = True
    excelSheet.Activate
    excelApp.Visible = True
End Sub

Ideally the workbook should be opened as read only. Is this possible?

Joe O'Connell


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
Sent: Monday, December 21, 2009 1:35 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Open Excel workbook from Access 2000

This is what works for me.
 Dim oXL As Excel.Application
 oXLBook As Workbook
 oxlsheet As Worksheet

 'open the spreadsheet
    Set oXL = New Excel.Application
    Set oXLBook = oXL.Workbooks.ADD
    oXL.Visible = True
    oXLBook.Activate

>>> On 12/21/2009 at 1:21 PM, in message
<1CF20DB644BE124083B31638E5D5C0234A2087 at exch2.Onappsad.net>, "Joe O'Connell" <joeo at appoli.com> wrote:
I need to open a specific sheet in an Excel workbook from Access 2000.
There is no problem opening the workbook, the list of processes shows that Excel is running.  The problem is that the workbook is not visible.
The statement to display the workbook ("excelWb.Visible = True") fails with an error "Object does not support this object or method".

Does anyone know the correct syntax to display the workbook?

The code is:

Public Sub OpenSheet( strWB, strSheet)

    Dim excelapp As Excel.Application
    Dim excelWb As Excel.Workbook
    Dim excelSheet As Excel.Worksheet
    
    Set excelapp = CreateObject("Excel.Application")
    Set excelWb = excelapp.Workbooks.Open(strWB, , True)
    excelWb.Worksheets(strSheet).Activate

    excelWb.Visible = True

End Sub


Joe O'Connell

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