Print Automation (1 Viewer)

LynnaM

Registered User.
Local time
Today, 11:53
Joined
May 11, 2001
Messages
19
Does anyone know how to automate printing with Excel? I have this code to print Word documents that are chosen in a listbox and I am needing to adapt this for excel also. Any help is greatly appreciated.

Dim objWord as Object
Set objWord = CreateObject("Word.App")

objWord.Documents.Open (strFile)
objWord.PrintOut Background:=False
objWord.Quit

Set objWord = nothing
 

Topher

Registered User.
Local time
Today, 11:53
Joined
Apr 18, 2000
Messages
72
in the access 97 helpfiles there is a list (below) of options for excel. perhaps changing the word.app to excel.app may work? or one of the other ones??

Set xlApp = CreateObject ("Excel.Application")
Set xlApp = CreateObject("Excel.Workbook")
Set xlApp = CreateObject ("Excel.WorkSheet")
Set xlApp = CreateObject("Excel.Application")

- T
 

LynnaM

Registered User.
Local time
Today, 11:53
Joined
May 11, 2001
Messages
19
I have tried changing it for Excel but it gives me a runtime error 438 "Does not support object". The rest of the following code works with the objExcel.PrintOut commented out. Thanks for the ideas though. Any other suggestions?


If Right(strFile, 3) = "xls" Then
Set objExcel = CreateObject("Excel.App")

objExcel.Visible = True
objExcel.Workbooks.Open (strFile)
'objExcel.PrintOut Background:=False
objExcel.Quit
 

LynnaM

Registered User.
Local time
Today, 11:53
Joined
May 11, 2001
Messages
19
I think that this article might be to run Excel macros instead of Access macros. The title says Run Excel Macros Through Automation so I am assuming that it is for Excel, but I tried it anyway and I could not get it to work. Thank you so much for your assistance, I really appreciate it.
Do you have any other ideas?
 

Topher

Registered User.
Local time
Today, 11:53
Joined
Apr 18, 2000
Messages
72
Welcome,

Sorry I dont know any other options...i think the code from the link allows you to run an excel macro from access...but im not 100% on that....

what is that your trying to print? is it not possible to print it in an access report?

- T
 

LynnaM

Registered User.
Local time
Today, 11:53
Joined
May 11, 2001
Messages
19
We have several documents (well over 100) in Word and Excel. I am in the process of making a print dialog form where user's can choose a department and it will show all the documents that apply to that department. User's will be allowed to select one, a few or all documents (word and excel) and automatically cycle through all of them (open them, print them and then close and move on to the next one). I have all of it working except the printing of the excel sheets. These documents cannot be made into Access reports due to restrictions of the company.

Thank you, I really appreciate all your help and advice.
 

Topher

Registered User.
Local time
Today, 11:53
Joined
Apr 18, 2000
Messages
72
Quite Welcome =)

only other option i can think of is in DOS you can copy a file to an lpt port - or printer port - not sure if that would work with excel files but it might.

good luck!

- T
 

LynnaM

Registered User.
Local time
Today, 11:53
Joined
May 11, 2001
Messages
19
Topher -

I finally got it figured out! I now have a completely functional listbox that cycles through and prints each Word or Excel file that is selected. Just wanted to thanks for all the help!
 

LynnaM

Registered User.
Local time
Today, 11:53
Joined
May 11, 2001
Messages
19
Ross -

I hope that this might help you out, I tried to email you but I do not know if it went. If you need further explanation just let me know.


Private Sub Print_Click()
Dim objWord As Object
Dim objExcel As Object, X
Dim strFile As String
Dim vntIndex As Variant, strValue As String

If lstPrintTitle.ListIndex < 0 Then
MsgBox "Please select 1 or more items from the list."
Exit Sub
End If

For Each vntIndex In lstPrintTitle.ItemsSelected
strFile = [lstPrintTitle].Column(2, vntIndex)
Debug.Print "file - " & strFile
Debug.Print "value - " & strValue

If Right(strFile, 3) = "xls" Then
Set objExcel = CreateObject("Excel.Application")
With objExcel.Application
.Workbooks.Open (strFile)
.Application.Visible = False
.ActiveSheet.PrintOut
.Quit
End With
Else
Set objWord = CreateObject("Word.Application")
objWord.Documents.Open (strFile)
objWord.PrintOut Background:=False
objWord.Quit
End If
Next vntIndex
Set objExcel = Nothing
Set objWord = Nothing
End Sub
 

Users who are viewing this thread

Top Bottom