Printing a PDF from Excel 2003

[shabbaranks]

Hi all...

Ive been beating my head against a google brick wall and I cant get it working. Im not by far an Excel expert but Im trying to get Excel 2003 printing to a pdf via a macro. The current pdf software Im using is doPDF but Im open to suggestions. Its a **** site easier within Excel 2007 as it has the function built in but I cant get anywhere in 2003 - help is extremely appreciated thank you

 

[shabbaranks]

Ok so Im making progress - I can get the active sheet printing to a pdf. Can anyone tell me how I save the pdf as a specified name-(cell content) please?

for example test-(CellA content).pdf?

Sub PrintPDF()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"doPDF v7 on DOP7:", Collate:=True
End Sub

Thanks

 

[Kenneth Hobson]

Welcome to the forum!

If you don't have command line parameters for dopdf, you must use their dialog. You can do that but you need to use a SendKeys() method.

Use an API spy program to check some controls too as Sendkeys() methods depend on proper focus and timing. API methods are used to find the window title/caption and classname. A free spy program that I like is the API spy at: http://patorjk.com/blog/software/

 

[shabbaranks]

Thanks Kenneth, I dont suppose you could elaborate on this a little for me? I get what you mean with regards to the 3rd party printer having specific code requirements but Im not too sure how to achieve my goal.

Thanks again

 

[Jose_Chasez]

I use PrimoPDF (a freeware PDF writer) for this purpose. In case it's helpful, my code is below:

    Application.ActivePrinter = "PrimoPDF"
    Sheets(Array("Dashboard", "Raw Data")).PrintOut Copies:=1, Collate:=True

 

[Kenneth Hobson]

If you don't want to use the API approach, a simple Wait method with SendKeys() will work "most" of the time. Basically, write down each key that you type. Do not use the mouse.

To use Sendkeys(), Vista+ users must disable UAC.

Here is a simple example for Notepad:

Sub SavePartcorrect()
  Dim myPath As String, txtPath As String
  Dim rc As Long
  Dim wb As Workbook
  
  Set wb = ActiveWorkbook
  myPath = ThisWorkbook.Path & "\"
  txtPath = myPath & "Test.txt"
    
  rc = Shell("NOTEPAD.EXE " & txtPath, vbNormalFocus)
  AppActivate rc
  Application.Wait Now + TimeValue("00:00:05")
  SendKeys Application.UserName, True
  SendKeys "{Enter}", True
End Sub

For NotePad, here is a method that uses some API routines.

'Wigi, http://www.mrexcel.com/forum/showthread.php?p=2823315
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
  (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" _
  (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
  (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Sub Wigi()
    Shell "NotePad", vbNormalFocus
    AppActivate fWindowText("Untitled - Notepad", "")
    
    Application.Wait Now + TimeValue("00:00:01") / 100
    
    With New MSForms.DataObject
        .SetText "I am sending this from Excel VBA to NotePad"
        .PutInClipboard
    End With
    
    SendKeys "^V", True
    
End Sub

Function fWindowText(sWindowText As String, Optional sClass As String) As String

    Dim hWnd As Long, lRet As Long, sText As String
    hWnd = FindWindowEx(0, 0, vbNullString, vbNullString)
    Do While hWnd <> 0
        'ClassName
        sText = String(100, Chr(0))
        lRet = GetClassName(hWnd, sText, 100)
        If Len(sClass) = 0 Or Left(sText, lRet) = sClass Then
            'WindowText
            sText = String(100, Chr(0))
            lRet = GetWindowText(hWnd, sText, 100)
            If InStr(sText, sWindowText) Then
                fWindowText = Left(sText, lRet)
                Exit Function
            End If
        End If
        hWnd = FindWindowEx(0, hWnd, vbNullString, vbNullString)
    Loop
End Function

 

[shabbaranks]

Thanks Jose - have you got a link please? Ive just looked and when I try and download I get the message it could be harmful to your computer.

Can you also tell me can you manipulate the pdf with primo? By this I mean can you specify which sheet you want to print from the macro and also can you name the pdf based on a template\cell content?

Thanks

 

[shabbaranks]

Im sorry I dont really get the sendkeys approach - is it capturing everything you are doing? Ive just tested it and it launched notepad - that was it. Unless I am missing something?

Thanks

 

[Andrew Poulsom]

If you are able to download, PDFCreator has an API:

http://www.excelguru.ca/node/21

 

[shabbaranks]

Thanks Andrew, I did have pdfcreator installed previously. And I tried using that code in which you referenced but it didnt work I just got an error. So I kind of gave up. Im not too sure if its because that guide was referencing an older version of PDFcreator? Thanks again