Can't open Excel from Access (1 Viewer)

[racdata]

I am using Windows7 and Office 2010 but cannot find a way to open an Excel spreadsheet from a form with a button.
I have a database created in Access 2007 and I could open these spreadsheets. I have many inspection forms in Excel that I want to open with different buttons. In the button wizard there was always under application where you can open Excel and Word applications. However, in Access 2010 I cannot find them.
Although the buttons I have, to open Word documents are still working and I can open Word documents from Access. Only if they are saved as a Word 97-2003 documents. If I save it in the word 2010 format, I cannot open the document. See attachment.
Thank you for the help.

 

[Trevor G]

You can writer some VBA code to open the Excel Application and a sample is indicated below. You should set a reference in the VBA screen to do this though. Use Alt + F11 and then select Tools and Reference then go down until you see Microsoft Excel 14.object Library and click the box.


Code to Open Excel and go to a workbook.

Function OpenExcel1()
Dim xlApp as Excel.Application
Set xlApp = CreateObject("Excel.Application")
with xlApp
.Workbooks.Open "Place the Path and workbook name here.xls"
.Visible=True
end with
End Function

Create yourself a button on your form then stop the wizard and open the properties, select the On Click Event and go into the Event Procedure then you can place in the words

Call OpenExcel1

Then save and then close the VBA screen down change the view of the Form and when you click it should open the workbook

 

[boblarson]

You can writer some VBA code to open the Excel Application and a sample is indicated below. You should set a reference in the VBA screen to do this though. Use Alt + F11 and then select Tools and Reference then go down until you see Microsoft Excel 14.object Library and click the box.


Code to Open Excel and go to a workbook.


Create yourself a button on your form then stop the wizard and open the properties, select the On Click Event and go into the Event Procedure then you can place in the words



Then save and then close the VBA screen down change the view of the Form and when you click it should open the workbook

Trevor:

I am going to suggest something which you should do to separate the Access from Excel in this case. It isn't something that most people do but it can prevent some Excel code errors if you use the same code multiple times in a row. That is to release the instance of Excel like this:

xlApp.UserControl = True
Set xlApp = Nothing

Two simple lines but ones that can help avoid errors for running the code more than once without closing the Access application.

Just a suggestion, but one I've found reduces the chance of errors down the line.

 

[Trevor G]

Thanks Bob,

I like this and will use it. It does make sence when I think about it.

 

[racdata]

Thank you so much for the help, I will try this and contact you again.

I do not know much about VB, but it is a challenge.