Printing Excel worksheet from Access

Topic

New Reply

In Access 97, want to know if it is possible to print an excel worksheet automatically. I have several reports that get printed on a regular basis. I have created an Access macro that is kicked off by a command button. Is there a procedure/process that I can incorporate into the macro to open excel, open a file, select the appropriate worksheet, print the active worksheet, close the file, close excel, and then return the focus to the command button that started the whole process?

Excel resides in the following location– “C:Program FilesMicrosoft OfficeOfficeEXCEL.EXE”

The data file resides in the following location– “D:My DocumentsPersonalExcelmovies.xls” and I want to print ‘Sheet1’.

Any help would be greatly appreciated.

Thanks steve69

Reply | Quote

Replies

Steve,
First, be sure you have the reference to Microsoft Excel 8.0 library checked under Tools|References (from within the code window of Access). Then you can use something like the following:

Sub btnExcel_Click()
Dim XLApp as New Excel.Application
XLApp.Workbooks.Open "D:My DocumentsPersonalExcelMovies.xls"
XLApp.Sheets("Sheet1").PrintOut
XLApp.Quit
Set XLApp = Nothing
End Sub

You may have to tweak it a bit, but this is the general idea.

HTH

Reply | Quote

Thank you, that worked perfectly. By the way, is there someplace to get information on the commands that are available when referencing the excel app from within Access VBA??

steve69

Reply | Quote

The VBA Developer’s Handbook (Sybex) has a lot of examples in it, but you can also get information from this link at Microsoft. It covers both Office 97 and Office 2000 automation.

Reply | Quote