Hi,

I need help to write a code to open an Excel file from my D:\ drive from Access. It prompts me with this error message "Compile error: User-defined type not defined" at the first line of my code. Can someone advise me what is wrong??

Private Sub OpenExcel_Click()
On Error GoTo Err_OpenExcel_Click

Dim ExcelObj As Excel.Application

' Start Microsoft Excel and open the workbook.
Set ExcelObj = CreateObject("Excel.Application")


FileName = "D:\My Documents\Volume Stats" & "Quest Trade Participants" & ".xls"

Exit_Err_Export_Click:
Exit Sub

Err_Export_Click:
MsgBox Err.Description
Resume Exit_Export_Click
End Sub

It would appear that you have not got the necessary correct Reference Libraries selected from the Tools: References menu options. Choose the latest Microsoft Office (probably Microsoft Office 11.0 Object Library) option from the (extremely) long list and you should be fine. If you already have it selected, try unselecting it and then select it again. It is surprising how often this fixes things.

Hi,

   If you just want to open an Excel spreadsheet, try this as the On Click Event of a button (Called Command0 in this case):

'----------
Dim stLink As String
Dim ctl As CommandButton

stLink = "C:\Book1.xls"

    Set ctl = Me!Command0
    With ctl
        .Visible = True
        .HyperlinkAddress = stLink
        .Hyperlink.Follow
    End With
'--------------

I use this code to open pdf files generally (the path is part of the record) so the stLink variable is from the form on which the button resides. You can hard code the link, or pass the name of the spreadsheet from the database if it is stored there (with the path).

This method does not require additional references, but may not allow the greater control that the Office reference might.

HTH


mmcdonal

Also, in your link, you have this line:

'----------

FileName = "D:\My Documents\Volume Stats" & "Quest Trade Participants" & ".xls"

'----------

Should it be:

'----------

FileName = "D:\My Documents\Volume Stats\" & "Quest Trade Participants" & ".xls"

'----------

Note the extra backslash.

HTH



mmcdonal

I'm using Access 2002 so the version of the Access Object library is 10.0. Nevertheless, I tried to unselect it but I'm unable to. I was prompted "Can't remove control or reference; in use".

As suggested, I also added the backslash to my command but my program is still not working. Any help available?

Thanks

Another place to check is the other code associated with this form (I am assuming it's a form) since you could get this error when other code is wrong, but it shows up when this button is pushed.

Whenever you do anything on a form, all the code for the form is parsed before execution of your little bit. So it could be something else.

The code with the link I suggested does NOT work?



mmcdonal

u specify:

On Error GoTo **Err_OpenExcel_Click**


when the actual label of your error code is **Err_Export_Click:**

this would lead to a compile time error

Thanks for all the help. Managed to solve the problem.

How?

Hi,

I used the method suggested by mmcdonal.

Regards