Consulting

Results 1 to 7 of 7

Thread: Solved: VBA Printer.Print

  1. #1

    Solved: VBA Printer.Print

    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?

  2. #2
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    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.

    [vba]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
    [/vba]
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  3. #3
    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

  4. #4
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    Edit your two constants to:
    [VBA] Const strHeader_c As String = "<html><body><font size=""3"" color=""#FF0000""><pre>" & vbNewLine
    Const strFooter_c As String = vbNewLine & "</pre></font></html></body>"
    [/VBA]
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  5. #5
    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

  6. #6
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    Change
    [vba]Sub SimpleExample()[/vba]To[vba]Sub SimpleExample(filePath As String)[/vba] And change
    [vba]Set tsFile = fso.OpenTextFile("C:\Test.txt", ForReading, False, TristateUseDefault)[/vba] To [vba]Set tsFile = fso.OpenTextFile(filePath, ForReading, False, TristateUseDefault)[/vba]
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  7. #7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •