How to Print a File or Control

[Chaplain Doug]

Excel 2003. FIRST, I want to just be able to print (to my default printer)
the contents of a text control on a user form. I was given the following
code, but the printout is funky (margins are super narrow).

Dim PrinterName As String, I As Integer
I = InStr(Application.ActivePrinter, " on ")
PrinterName = Mid(Application.ActivePrinter, 1, I - 1)
Open PrinterName For Output As #1
Print #1, ProcessStatus.ProcStat.Text
Close #1

IS THERE A BETTER WAY TO ACCOMPLISH THIS?

SECOND, How does one print out an external file from Excel VBA code.
Suppose for instance that I want to print out file "Test.txt." How would I
program that? Thanks for the help and God bless.

 

[Tom Ogilvy]

Test this chaplain doug

Sub abc()
Dim PrinterName As String, I As Integer
s = "0123456789012345678901234567890123456789"
I = InStr(Application.ActivePrinter, " on ")
PrinterName = Mid(Application.ActivePrinter, 1, I - 1)
Open PrinterName For Output As #1
Print #1, s & s
Close #1

End Sub

this went all the way across the paper - if it does it for you, you may have
to take out the CHR(10)'s in your text box and place them in more appropriate
places in your text string.

 

[Chaplain Doug]

Dear Tom:

First, I thank you for taking the time to try to help me. The code you
suggest here is the same as I already have. It does not give reliable
results. For instance when I set my default to one network printer it prints
with super small top, left, right, and bottom margins. When I set my default
to another network printer, the printer says I need to load COM10 envelopes.
When I set my default to my desktop printer, it does not print at all.

What I would really like to do is save the text from the control into a
".txt" file (this I know how to do) and then print this file. How from Excel
VBA can I print an existing file? Thanks for the help and God bless.

 

[Tom Ogilvy]

If I wanted to do it from DOS/Command window I would do

copy "C:\MyFile\Test.txt" Lpt1

perhaps you can get the shell command to support something like that. I
haven't tried it.

 

[Chaplain Doug]

Dear Tom:

The lpt1 is a problem, since I am using a network printer.

I got some code a while back that allowed me to open and view a text file.
I was hoping for a similar tip that would allow me to print the text file.

I realize that I may have tapped you out on this. Should I repost and see
if someone else can provide code to do what I want? I get the impression
that once a post has a reply, then other MVPs assume it has been handled.
Thus, do I need to repost to get others to look at this as well? Thanks.

 

[Chaplain Doug]

Dear Tom:

I put this together from another post in this forum. it works great. Just
thought you might want to add it to your basket:

Dim WordObj As Object
On Error GoTo Err_PS
Set WordObj = CreateObject("Word.Application")
Open CurDir + "\temp.doc" For Output As #1
Print #1, ProcessStatus.ProcStat.Text
Close #1
WordObj.Documents.Open CurDir + "\temp.doc"
WordObj.PrintOut Background:=False, Copies:=1
WordObj.Quit
Set WordObj = Nothing
Kill (CurDir + "\temp.doc")
Exit_PS:
Close #1
Exit Sub
Err_PS:
Call MsgBox("Error printing status window. Please contact system
administrator.", vbOKOnly, "Print Error")
ProcessStatus.PrintStatus.Enabled = False
GoTo Exit_PS

 

[Tom Ogilvy]

Thanks but no thanks.

If I was going to open word to print the file, why wouldn't I just add a
sheet in Excel, write the text directly and print it, delete the sheet or
just write it to a section of an existing sheet, print that section, then
clear it.
Yours seems like the long way around the block. I would have suggested this
simple solution, but when you asked for a specific solution, I assumed you
had ruled out the obvious simple solution.