PDA

View Full Version : Solved: VBA Printer.Print



BarkerBob
04-23-2008, 06:47 AM
Hi all

Im new here, so this may be in the worng area

I'm trying to build a routine that open, read and print a text file but I need to change the font size

I have seen examples using
Printer.Font size
Printer.Print "Some Data"

But Printer. isnt regognized in my modules, via Access and or Excel

Any tips?

Oorang
04-23-2008, 09:31 AM
To print, well anything, you either have to write your own print progam to interact with the printer (let's just call that "Plan B") or use another programs methods to print. Access/Excel do not expose methods to change the way the text prints to the printer they way you describe. So your easiest solution is to import the file into access (or excel) and make it look the way you want it to print, then print it.

However there is another way that might work if you are just printing plaintext and only want to change the font. That would be convert the file to html, and print it via Internet Explorer. This will allow you to specify your font just fine. I would observe that this approach is likely to get complicated the more you try to do with it. But it should be fine for a simple task.

Option Explicit

Sub SimpleExample()
'This will *NOT* work unless you set references to:
'-Microsoft Sciprting Runtime (scrrun.dll)
'-Microsoft Internet Controls (shdocvw.dll)
Const strHeader_c As String = "<html><body><font size=""3"" color=""#FF0000"">" & vbNewLine
Const strFooter_c As String = vbNewLine & "</font></html></body>"
Const strHTMLExt_c As String = ".html"
Const strBreak_c As String = "<BR>"
Const PRINT_WAITFORCOMPLETION = 2 'Do *Not* dim as long or type coercion will fail.
Dim fso As Scripting.FileSystemObject
Dim tsFile As Scripting.TextStream
Dim strFileText As String
Dim strTmpFilePath As String
Dim ie As SHDocVw.InternetExplorer
'Get FileSystemObject:
Set fso = New Scripting.FileSystemObject
'Open text stream to file:
Set tsFile = fso.OpenTextFile("C:\Test.txt", ForReading, False, TristateUseDefault)
'Read entire file into variable, also replace linebreaks with html linebreaks:
strFileText = Replace(tsFile.ReadAll, vbNewLine, strBreak_c)
'Close Text stream:
tsFile.Close
'Create a path for the temp file:
strTmpFilePath = fso.BuildPath(fso.GetSpecialFolder(TemporaryFolder), fso.GetTempName) & strHTMLExt_c
'Create file at path we built:
Set tsFile = fso.CreateTextFile(strTmpFilePath, True, True)
'Write html file:
tsFile.Write strHeader_c
tsFile.Write strFileText
tsFile.Write strFooter_c
'Close text stream:
tsFile.Close
'Start an invisible session of internetexplorer:
Set ie = New SHDocVw.InternetExplorer
'Open temp file in IE:
ie.navigate strTmpFilePath
'Wait for file to load:
Do Until ie.readyState = READYSTATE_COMPLETE
Loop
'Print File:
ie.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_PROMPTUSER, PRINT_WAITFORCOMPLETION
'Close IE:
ie.Quit
'Remove Temp File.
fso.DeleteFile strTmpFilePath, True
End Sub

BarkerBob
04-23-2008, 10:56 AM
Thanks for the reply, this work well except

There are areas of the text file that has blank spacing and this looses that.

So as a small example:

Report: TP006 companyname ..................................................................... APR
would be
Report: TP006 companyname APR


Haha So does this, the ................................ are blank spaces

Oorang
04-23-2008, 12:08 PM
Edit your two constants to:
Const strHeader_c As String = "<html><body><font size=""3"" color=""#FF0000""><pre>" & vbNewLine
Const strFooter_c As String = vbNewLine & "</pre></font></html></body>"

BarkerBob
04-23-2008, 12:44 PM
Totaly Cool, works perfect for a specific file, just have to find a way to pass the path and file name to it.

Thank You
Bob

Oorang
04-23-2008, 08:02 PM
Change
Sub SimpleExample()ToSub SimpleExample(filePath As String) And change
Set tsFile = fso.OpenTextFile("C:\Test.txt", ForReading, False, TristateUseDefault) To Set tsFile = fso.OpenTextFile(filePath, ForReading, False, TristateUseDefault)

BarkerBob
04-24-2008, 03:47 AM
Hi again, yes Im somewhat familar with that change and I got it to pass the file.

But, after further review, the print out isnt right, I guess its the font size and or font type.

I wrote a quick basic program back in the 90's that would do this, and it is still around and working, for what it built for, but I need to change the source code somewhat for this but the source code has dissappeared over the years.

Guess the best way to handle this is give you one or two of the files, but thats to much to ask for.

Thanks again my friend
Bob