Opening an Excel Spreadsheet from Access Command Button

[jonnywakey]

Hi

I am trying to programme the "On Click" event on a command button to open an excel spreadsheet; unfortunately after many attempts I am still struggling to get the code to work.

The spreadsheet is located in the following location:-

W:\2007 Suggestion Scheme\2009 Suggestion Scheme

The spreadsheet itself is called "Performance Dashboard"

Could anyone point me in the right direction?

All I want the command button to do is open this spreadsheet, I have tried using Hyperlinks but the security warnings prevent the file from opening whilst Access is open.

Many thanks

jonny

 

[Mike375]

Try this, I am using Office 2003. Replace folder/file name with yours.

Dim ObjXLApp As Object
Dim ObjXLBook As Excel.Workbook
Dim OSheet As Excel.Worksheet
Set ObjXLApp = CreateObject("Excel.Application")
Set ObjXLBook = ObjXLApp.Workbooks.Open("C:\Letters\Exp.xls")
Set OSheet = ObjXLBook.Worksheets(1)
ObjXLApp.Visible = True

 

[Mike375]

I forgot about hyperlink.

Put on the OnClick event of a button

Forms!GoToExcel!Command11.HyperlinkAddress = ("C:\Letters\Exp.xls")

Change to your form name etc. That should opend without security warnings but 2007 might be different

I just ried both using Runtime 2007 and all was well, however, the db was made in A2003.

 

[bg3075]

I am trying to have Access open PDF's via hyperlinks in a query, but from a command button on a form. It actually opens the correct record in datasheet view, and can then click the hyperlink to open in Acrobat, but get an error "Unable to open Query!qryLithLogLinks![Lith_Logs]. Cannot open the specified file" I have enabled these references: Adobe Acrobat 8.0 Type Library, Acrobat Access 3.0 Type Library, Adobe Acrobat 8.0 Browser Control Type Library 1.0. What I would really prefer it do is open either a folder (from hyperlink path in query) or PDF (with default application), as some of the records may or may not not have an associated PDF.

Code:

Private Sub Command59_Click()
Dim ObjPdfApp As Object
Set ObjPdfApp = CreateObject("AcroExch.App")
DoCmd.OpenQuery ("qryLithLogLinks")
End Sub