Open an Excel workbook from MS Access

[Will Riley]

Ok, this is nothing really new, however it does seem to be quite a common question, so for want of another better place to put it.

This code will run from a normal command button placed on an Access form

Private Sub CmdOpenExcel_Click() 
     'WillR - opens the specified Spreadsheet
    Dim xlApp As Excel.Application 
    Dim xlWB As Excel.Workbook 
    Set xlApp = New Excel.Application 
    With xlApp 
        .Visible = True 
        Set xlWB = .Workbooks.Open("R:\path\path2\path3\xlfilename.xls", , False) 
    End With 
End Sub 

Hope it helps - I have found it useful.

NB: You will need to set a reference to the Excel Object Library in the Visual Basic Editor in MS Access.

To do this,

Open the Access VBE (ALT + F11)
Go to Tools|References
Check the box next to the relevant Excel Library (for example Microsoft Excel 9.0 Object Library if using Office 2000)
Click OK