Open excel document

[skinny220]

Does anyone know an access VBA, I could use to open an excel file on my desktop called Show Inventory.xls?

 

[Darren Bartrup]

Re: Microsoft Excel

Seems like your second post has been spammed - shame as the thread won't appear on Zero Reply Posts anymore.

This is how I open an Excel file from Access:
You'll need to create an instance of Excel that Access can reference:

Public Function CreateXL(Optional bVisible As Boolean = True) As Object


    Dim oTmpXL As Object


    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Defer error trapping in case Excel is not running. '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Set oTmpXL = GetObject(, "Excel.Application")
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If an error occurs then create an instance of Excel. '
    'Reinstate error handling.                            '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo ERROR_HANDLER
        Set oTmpXL = CreateObject("Excel.Application")
    End If
    
    oTmpXL.Visible = bVisible
    Set CreateXL = oTmpXL


    On Error GoTo 0
    Exit Function


ERROR_HANDLER:
    Select Case Err.Number
        
        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure CreateXL."
            Err.Clear
    End Select
    
End Function

Then in another procedure you'll need a couple of variables to hold the reference to Excel and another to the workbook file.

    Dim oXLApp As Object            'Reference to Excel Application.
    Dim oXLWrkBk As Object          'Reference to workbook.

    Set oXLApp = CreateXL() 'Creates the instance of Excel.
    Set oXLWrkBk = oXLApp.WorkBooks.Open(full filename & path, False)

NB: Full filename and path will be something like "C:\Documents and Settings\ADMINNOT\Desktop\Book2.xls"

 

[skinny220]

Re: Microsoft Excel

Thanks a million.

 

[Darren Bartrup]

Re: Microsoft Excel

Glad to help.