#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2009
    Posts
    12
    Rep Power
    0

    Force pdf file name from query


    Hi,

    I have an MS Access 97 query that prints a Report to an Adobe Acrobat printer. The query creates multiple reports (up to 200) and each time it creates a new report, it asks me for a file name for each one. I would like to have it use a specific file name from a query I created. Is there a way to force it to use the file name I have in my query? How about a specific location (ex. C:\reports\)? Also, is there a way to force Access to use a specific printer (Acrobat is not my default printer, but I'll make it the default before I run this query/macro if it can't be done with code). Thnx!
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    For handling the file name programmatically, check this link:
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=789

    Apparently, can direct print output to specific printer with printer class. (Found code, haven't actually put it to use.) Don't know if has to be in report Open event or if can be somewhere else.
    Code:
    Private Sub Report_Open(Cancel As Integer)
    'On Open event of report
    
    'This procedure shows how to change printers within an application and
    'then change back to the default printer when done.
    
    Dim strDefaultPrinter As String
    Dim strNewPrinter As String
    
    'load the current default printer into the variable strDefaultPrinter
    strDefaultPrinter = Application.Printer.DeviceName
    'load the 2nd printer name to be used into the variable strNewPrinter
    strNewPrinter = "Adobe PDF"
    
    ' switch to printer of your choice:
    Set Application.Printer = Application.Printers(strNewPrinter)
    
    '  ...
    '  insert your print code here to do whatever you want with the new printer
    '  ...
    
    '  change the printer back to the default printer
    Set Application.Printer = Application.Printers(strDefaultPrinter)
    
    End Sub
    Last edited by June7; March 25th, 2010 at 04:04 PM.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2009
    Posts
    12
    Rep Power
    0
    Thanks for your response June7, helpful code and links, but....

    I got an error code for the "Application.Printer(strNewPrinter)". (apparently .Printer isn't available in Access 97).

    The link you sent me was great, and perfect for what I want to do (save the file name as a name and date) but it also doesn't work. It saves the file, but with the default name, not the new name (the strSave variable) as I hoped. It creates a directory on my desktop called "Reports" (which is also great!), but it doesn't save the file in it, with the new name (the strSave variable). My experience in VB is minimal but my guess is its because it does a registry change and I can't edit my registry (the Admin has closed off all users RegEdit privileges).


    Code:
    Public Function WriteRegistryEntry(strPDF As String) 
         '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         ' Purpose:      Create a registry file in order to set the name and path
         '               of the PDF file
         '
         ' Reference:    Concept developed from post at
         '               http://www.tek-tips.com/viewthread.cfm?qid=1112992
         '
         ' Assumptions:  Registry file is created in same folder as current database,
         '               then deleted once it has been merged into the registry
         '
         ' Inputs:       strPDF      Name of PDF file to create
         '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        Dim strPath As String 
        Dim x 
         
         
        strPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\", , vbTextCompare)) 
         
         ' make sure reports folder exists
        If Dir(strPath & "Reports\", vbDirectory) = "" Then 
            MkDir strPath & "Reports\" 
        End If 
         
         ' registry key needs "\\" in file path
        strPDF = strPath & "Reports\" & strPDF 
        strPDF = Replace(strPDF, "\", "\\") 
         
         ' delete the registry file if it exists
        On Error Resume Next 
        Kill strPath & "CreatePDF.reg" 
         
         ' create the registry file
        On Error Goto ErrHandler 
        Open strPath & "CreatePDF.reg" For Append As #1 
         
        Print #1, "Windows Registry Editor Version 5.00" 
        Print #1, "" 
        Print #1, "[HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter]" 
        Print #1, """PDFFilename""=" & Chr(34) & strPDF & Chr(34) 
        Close #1 
         
         ' merge into registry
        x = Shell("regedit.exe /s " & strPath & "CreatePDF.reg", vbHide) 
         
         
    ExitHere: 
        On Error Resume Next 
        Close #1 
        Kill strPath & "CreatePDF.reg" 
        Exit Function 
         
    ErrHandler: 
        MsgBox Err.Description 
        Resume ExitHere 
    End Function
    Full code here:
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=789



    Is there some sort of work around for this? I did change the printer name to my correct printer so that part wasn't the problem, however, I couldn't see what its called in the registry, in case that might be the problem.
  6. #4
  7. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Sorry, can't help on the printing to PDF code. I haven't used, just found the link. There are utilities available that claim to accomplish this. Here is one for a nominal cost, again I just found the link, not used: http://<br /> http://www.verypdf.co...ss-to-pdf.html
    Here is code that will show device name and path of printers your computer has setup. Unfortunately, it also uses the printer class.
    Code:
    Public Sub PrintersList()
    
    Dim prtLoop As Printer
    Dim strString As String
    
    For Each prtLoop In Application.Printers
        With prtLoop
            strString = strString & "Device name: " & .DeviceName & vbCr _
                & "Driver name: " & .DriverName & vbCr _
                & "Port: " & .Port & vbCr & vbCr
        End With
    Next prtLoop
    
    MsgBox strString
    
    End Sub

Similar Threads

  1. Writing Query results to a file
    By SmileSmita in forum ASP Development
    Replies: 10
    Last Post: September 6th, 2006, 08:02 PM
  2. IIS 6.0 Crashing Daily on Production Server
    By JMelsoner in forum Microsoft IIS
    Replies: 5
    Last Post: April 12th, 2006, 06:43 PM
  3. WSH .vbs Script which will Kill a process when a file is created.
    By zr11 in forum Visual Basic Programming
    Replies: 0
    Last Post: March 11th, 2005, 02:44 AM
  4. Securing A Database
    By TBÁrpi in forum Microsoft Access Help
    Replies: 31
    Last Post: January 25th, 2005, 06:23 PM

IMN logo majestic logo threadwatch logo seochat tools logo