Print Automation

[LynnaM]

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]

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]

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

 

[Topher]

does this link help?? it has a little bit about running excel hidden and running a macro from within excel? maybe then you would be able to open excel - use the macro to print the doc you want, then close excel??
http://www.mvps.org/access/modules/mdl0007.htm

 

[LynnaM]

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]

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]

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]

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]

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]

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