I can create an Excel worksheet and subsequently save the worksheet.  If I 

want to use the same worksheet later, how do I open (access) it?  I can 

find no examples of this.

This is called 'automation'.  Here's an entry point to the documentation:



http://msdn.microsoft.com/library/en-us/modcore/html/deconCreatingObjectVari

ableToAutomateAnotherOfficeApplication.asp



(pls watch for line wraps).



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Terrence P. Franklin [mailto:tfrankli@k...]

Sent: Thursday, December 06, 2001 6:37 AM

To: Access

Subject: [access] What is the syntax to open an Excel worksheet in VBA?





I can create an Excel worksheet and subsequently save the worksheet.  If I 

want to use the same worksheet later, how do I open (access) it?  I can 

find no examples of this.

>Roy,

Thanks, that MSDN site led me to GetObject.  That is the magic word!



Terry--



 This is called 'automation'.  Here's an entry point to the documentation:

> 

> http://msdn.microsoft.com/library/en-

us/modcore/html/deconCreatingObjectVari

> ableToAutomateAnotherOfficeApplication.asp

> 

> (pls watch for line wraps).

> 

> HTH,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

> 

> -----Original Message-----

> From: Terrence P. Franklin [mailto:tfrankli@k...]

> Sent: Thursday, December 06, 2001 6:37 AM

> To: Access

> Subject: [access] What is the syntax to open an Excel worksheet in VBA?

> 

> 

> I can create an Excel worksheet and subsequently save the worksheet.  If 

I 

> want to use the same worksheet later, how do I open (access) it?  I can 

> find no examples of this.

Something like this ought to work for you:



   'Set up access to EXCEL for report output

   Dim XLapp As Excel.Application

   Dim XLWBook As Excel.Workbook

   Dim XLWSheet As Excel.Worksheet

   Dim XLFile As String

   Dim nRow, nCol

   Dim ThisCell

   

   Set XLapp = New Excel.Application

   Set XLWBook = XLapp.Workbooks.Add

   Set XLWBook = XLapp.Workbooks.Open(cPath & "StikFreq.XLS")

   Set XLWSheet = XLWBook.Worksheets(1)



Walt

Here's an Access 97 module I used recently to play with several Excel 97 

Workbooks ... You can pick 'n choose what you like.

... AL



=================================================================



Option Compare Database

Option Explicit

    

Dim objXL As Object

Dim objWbk As Object

Dim boolXL As Boolean



Function OpenExcel(Optional bNew = False) As Boolean



    On Error GoTo Err_OpenExcel

    

    ''' Use an existing Excel app (if available), unless specifically told 

to create new one w/ OpenExcel(True) ...

    If fIsAppRunning("Excel") And Not bNew Then

        Set objXL = GetObject(, "Excel.Application")

        boolXL = False

    Else

        Set objXL = CreateObject("Excel.Application")

        boolXL = True

    End If

    objXL.Application.Visible = True    ''''    for debugging only

    

    OpenExcel = True



Exit_OpenExcel:

    On Error Resume Next

    Exit Function

    

Err_OpenExcel:

    OpenExcel = False

    MsgBox "Error: " & Err.Number & " -- " & Err.Description, 

vbOKOnly, "OpenExcel() Error"

    Resume Exit_OpenExcel

    

End Function



Function OpenWkbk(sFileName As String) As Boolean



    On Error GoTo Err_OpenWkbk

    

    Set objWbk = objXL.Workbooks.Open(sFileName)

    OpenWkbk = True

    

Exit_OpenWkbk:

    On Error Resume Next

    Exit Function

    

Err_OpenWkbk:

    OpenWkbk = False

    MsgBox "Error: " & Err.Number & " -- " & Err.Description, 

vbOKOnly, "OpenWkbk() Error"

    Resume Exit_OpenWkbk



End Function



Function SheetFormat(Optional vSheet = 1, Optional sFileName) As Boolean

    

    ''' If Sheet Name (vSheet) is not provided, default to first sheet 

(You can specify name or number of sheet.)

    ''' If Workbook Name (sFileName) is not provided, use whatever objWbk 

was set to previously.

    

    On Error GoTo Err_SheetFormat

    

    Const xlLandscape As Integer = 2

    

    If Not IsMissing(sFileName) Then Set objWbk = objXL.Workbooks

(sFileName)

    

    With objWbk

        With .Worksheets(vSheet)

            .Range("A1:H1").Font.Bold = True

            .Range("A1:H1").Interior.ColorIndex = 15

            .Columns("A:H").AutoFit

            With .PageSetup

                .PrintTitleRows = "$1:$1"

                .PrintTitleColumns = ""

                .LeftMargin = objXL.Application.InchesToPoints(0.25)

                .RightMargin = objXL.Application.InchesToPoints(0.25)

                .CenterHorizontally = True

                .Orientation = xlLandscape

                .FitToPagesWide = 1

                .FitToPagesTall = 5

                .Zoom = False           ''' to select FitTo rather than 

Adjust (.Zoom=100)

            End With

            .Cells(1, 1).Select

        End With

        .Save

    End With

    

    SheetFormat = True

    

Exit_SheetFormat:

    On Error Resume Next

    Exit Function

    

Err_SheetFormat:

    SheetFormat = False

    MsgBox "Error: " & Err.Number & " -- " & Err.Description, 

vbOKOnly, "SheetFormat() Error"

    Resume Exit_SheetFormat



End Function



Function CloseWkbk(Optional sFileName) As Boolean



    On Error GoTo Err_CloseWkbk

    

    If IsMissing(sFileName) Then

        objWbk.Close

        Set objWbk = Nothing

    Else

        objXL.Workbooks.Close (sFileName)

    End If

    

   CloseWkbk = True

    

Exit_CloseWkbk:

    On Error Resume Next

    Exit Function

    

Err_CloseWkbk:

    CloseWkbk = False

    MsgBox "Error: " & Err.Number & " -- " & Err.Description, 

vbOKOnly, "CloseWkbk() Error"

    Resume Exit_CloseWkbk



End Function



Sub CloseExcel()

    

    On Error Resume Next

    If boolXL Then objXL.Application.Quit ''' If you started the app, then 

close it.

    Set objXL = Nothing

    Set objWbk = Nothing



End Sub