open excel spreadsheet

[Guest]

Hi
I am trrying to open a spreadsheet in excel from form command button but
when I run I get message object variable or with block variable not set
code as follows where am I going wrong?
Thanks
Tina
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

Dim oApp As Object
Dim MYSPREADSHEET As String
Dim xlW, xlApp As Object
MYSPREADSHEET = "L:\SYMDATA\TINA\QA\CHEM.XLS"
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
Set xlW = xlApp.WORKBOOKS.Open(MYSPREADSHEET)

'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command19_Click

End Sub

 

[Ken Snell [MVP]]

You're not consistent with which object you're using to represent EXCEL.

You use oApp to set the initial reference, but then you try to use xlApp
later in the code.
Set xlW = xlApp.WORKBOOKS.Open(MYSPREADSHEET)
Change this to
Set xlW = oApp.WORKBOOKS.Open(MYSPREADSHEET)

Also, note that your Dim statement is making xlW a variant variable type,
not an object variable type.
Dim xlW, xlApp As Object
Change this to
Dim xlW As Object, xlApp As Object

 

[Richard \Manxman\ Killey]

Tina:

if you just need to open the file so the user can manipulate the Excel
workbook in some way, why not use the hyperlink capability of the
command butto? No VBA required in a simple case.

See
http://www.accessdatabasetips.com/open-word-excel-file-from-button.html
for an example.


Richard