Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 23rd, 2005, 10:07 PM
Authorized User
 
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Open an Excel file using Access, pls help!



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

 
Old June 24th, 2005, 05:44 AM
Authorized User
 
Join Date: Jul 2004
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.


 
Old June 24th, 2005, 06:22 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old June 24th, 2005, 06:24 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old June 26th, 2005, 08:16 PM
Authorized User
 
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 27th, 2005, 06:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old June 28th, 2005, 12:06 AM
Authorized User
 
Join Date: Jun 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 28th, 2005, 05:06 AM
Authorized User
 
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 28th, 2005, 07:49 PM
Authorized User
 
Join Date: Jun 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to alantodd
Default

How?
 
Old June 29th, 2005, 02:14 AM
Authorized User
 
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I used the method suggested by mmcdonal.

Regards






Similar Threads
Thread Thread Starter Forum Replies Last Post
open a excel file on local computer premnaath ASP.NET 2.0 Professional 1 December 4th, 2007 01:23 PM
Excel error cant open file Born2BFree Excel VBA 1 February 20th, 2007 01:05 PM
How to open Excel File in Access losartan Access 1 January 24th, 2005 06:08 PM
open excel file C# ? npqk VS.NET 2002/2003 2 November 26th, 2004 10:31 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.