Printing Excel file from Access Switchboard

[Bill]

I am using an Access module to print a series of Word
files and Access reports from an icon on an Access
switchboard. I would like to add an Excel file to the
print list. Below is the statement I am using to print
the Word files. Can anyone assist me in creating a
statement to add to my module that will allow me to print
an Excel file called "C:\test\printfile.xls" from the same
icon on the Acess switchboard?

Dim WordObj As Object
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open "C:\test\test.doc"
WordObj.PrintOut Background:=False
WordObj.Quit -1

 

[Guest]

Just a suggestion, and an MVP may come along with one much
more elegant that (for example) avoids opening the file.

NB This may currently fail if xl or the file are already
open. If this is an issue, to ask for help on how to trap
for whether the application/file is open post another
question here.

So, for what it is worth:

1. Add a reference to the MS excel version you are trying
to connect to using menu command Tools, References...

2. Add this line to your declarations

Dim xlApp As Object

3. Add this to your code, where myfile.xls is the file to
be printed.


Set xlApp = CreateObject("excel.application")
xlApp.Workbooks.Open Filename:="D:\myfile.xls"
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True ' this should be on end of line above
xlApp.Quit ' Use the Quit method to close xl
Set xlApp = Nothing ' Release the reference.


4. Good Luck

Jean

 

[Jean]

Sorry, didn't mean to post as entirely anonymous!

Jean