Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print to File (Excel 97 )

    We have a need to save our Excel spreadsheets to a Postscript file. We created a macro in the hopes of automating this process for our users. The intent of the macro is to take the existing file name, use this when saving the file in a specified folder with the same file name using a PS file extension; however, saving it as a Postscript file (using a QMS print driver). Our problem is that it only works sometimes which causes us major problems. If a spreadsheet is more than one sheet, it has problems capturing the additional sheets as one file. This is even after we have grouped them. So the people we pass the PS file to, may only get the first page.

    The code we are using is listed below. Looking for any input on the code we're using and/or suggestions on how to accomplish this. Thanks!

    ' macro created 7/24/96 to save spreadsheet out to
    ' postscript file for placement on the Internet

    Sub PrintToFile()
    Dim num As Integer
    Dim wbName As String, newName As String
    Dim destDir As String, destPrinter As String
    Dim curPrinter As String

    'set destination directory -this assumes that you always want to put the
    'print files in the same directory, in this case Q:INET
    destDir = "Q:INET"

    'set destination printer
    destPrinter = "QMS-PS 810 on FILE:"

    'store current printer
    curPrinter = Application.ActivePrinter

    'get filename and filetype
    wbName = ActiveWorkbook.Name
    'get length of filename
    num = InStr(wbName, ".") - 1

    If num <= 0 Then
    MsgBox "File has not been saved. Activity halted."
    Exit Sub
    End If

    'create new name, using existing filename and HP2 for filetype
    newName = destDir & "" & "ab" & Left(wbName, num) & ".ps"

    'delete print file if it exists:
    If Dir(newName _
    ) <> "" Then Kill newName

    'set adjust setting to 54
    With ActiveSheet.PageSetup
    .Zoom = 54
    End With


    'use sendkeys since filename can't be specified by a macro to a print file
    SendKeys newName & "{enter}"

    'print the file
    ActivePrinter = destPrinter
    ActiveWorkbook.PrintOut

    'set printer back
    Application.ActivePrinter = curPrinter


    'save file with any changes
    ActiveWorkbook.Save

    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print to File (Excel 97 )

    As you've discovered, sendkeys can be a disaster <g>.
    I've got the experience, that some printerdrivers tend to fail to receive keys sent to them.

    I have no sure resolution to your problem, since I cannot test it, not having your setup.

    What you might try is instead of using
    ActiveWorkbook.PrintOut
    use
    ActiveWorkbook.Worksheets.PrintOut
    (Note that that would omit chart sheets)
    If that's a problem, use

    ActiveWorkbook.Sheets.PrintOut

    Another thing you might try is adding DoEvents after the printout command.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print to File (Excel 97 )

    Now I seem to be faced with a new issue. The code is not working at all in Excel 2002. When run the macro, get a run time error 1004, method 'activeprinter' of object '_global' failed. Click Debug and takes me to the line saying ActivePrinter = destPrinter.

    This is code created by someone else who has since left and I don't have a clue! [img]/forums/images/smilies/smile.gif[/img] Any suggestions would greatly be appreciated!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print to File (Excel 97 )

    DO you have a printer driver loaded/installed for:
    "QMS-PS 810 on FILE:" ?

    If not add the driver via windows (start -settings - printers - add printer in Win 95)

    Steve

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print to File (Excel 97 )

    Yes, the QMS print driver is installed and identified the same as in the macro.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print to File (Excel 97 )

    Best is to record a macro setting the active printer to the one needed. Then read the macro to see what exactly is the string recorded. If it doesn't match the string in the other macro, that is your problem.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print to File (Excel 97 )

    Recording the macro to print to file (the QMS printer) is what we needed to do (not sure why didn't think of this). What is happening is that under our old environment (Win95 clients in NT4.0 domain) each computer had a QMS PS 810 print driver installed and things ran fine. In our new environment where we're using Citirx, it seems to look at the printers differently. So instead of Excel seeing the printer as "QMS PS 810" when running the macro, it now sees it as "ClientComputerAQMS-PS 810 [UPD:PCL4] on Ne80:". Of course, this would change with each user. Not sure how we would accomodate for this???

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print to File (Excel 97 )

    This code gets the username:

    Option Explicit
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
    ' Copyright
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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