Save an Excel file as PDF to a specific path

Question

I would like to save an Excel file as a .pdf file to a specific location and then send the file in a mail.

I'm using Office 2000 :|

This is my code so far:

Application.ActivePrinter = "PDF995 on Ne00:"
ActiveSheet.PageSetup.PrintArea = Range("A68").Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "PDF995 on Ne00:", Collate:=True
        Set WB = ActiveWorkbook
        Set oApp = CreateObject("Outlook.Application")
        Set omail = oApp.Createitem(0)
        With omail
            .To = Range("B61").Value
            .Subject = "Approved"
            .Body
            .Display
            Rows("81:134").Select
            Selection.EntireRow.Hidden = True
        End With

I can easily save the file and mail it, but I can't save it to a specific location.

I need to be able to specificy a path like "C:\path\file.pdf".

I would really be glad, if anybody "out there" could help me with this one. — Anders, Oct 11 '10 at 22:21

score 0 · Answer 1 · answered Jan 10 '13 at 23:34

It's a little complicated, as you have to set registry keys. Assuming that you have a full version of Adobe Acrobat that has installed the initial registry keys:

First, you have the registry accessing functions, which you put in a non-sheet module:

Private Const HKEY_CURRENT_USER As Long = &H80000001
Private Const HKCU = HKEY_CURRENT_USER
Private Const KEY_SET_VALUE = &H2&
Private Const REG_SZ = 1

Private Declare Function RegOpenKeyEx Lib "advapi32" _
    Alias "RegOpenKeyExA" ( _
    ByVal hKey As Long, _
    ByVal lpSubKey As String, _
    ByVal ulOptions As Long, _
    ByVal samDesired As Long, _
    phkResult As Long) As Long

Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, _
     ByVal sValueName As String, _
     ByVal dwReserved As Long, _
     ByVal dwType As Long, _
     ByVal sValue As String, _
     ByVal dwSize As Long) As Long

Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" ( _
    ByVal hKey As Long) As Long

Then, you use the following code to set the registry key that tells Adobe where to save the file. Note, it has to be set everytime you print.

Dim RegResult As Long, Result As Long

RegResult = RegOpenKeyEx(HKCU, "Software\Adobe\Acrobat Distiller\PrinterJobControl", _
                         0&, KEY_SET_VALUE, Result)
RegResult = RegSetValueExA(Result, "C:\Windows\splwow64.exe", 0&, REG_SZ, _
                          FileName, Len(FileName))
RegResult = RegCloseKey(Result)

Also Note, the "C:\Windows\splwow64.exe" is what I needed for my Excel 2010 32-bit, and it may be different for you. To determine it (which won't change) first print manually to PDF, then go to the registry key and see what application is used in the HKCU\Software\Adobe\Acrobat Distiller\PrinterJobControl LastPDFPortFolder key. Then use the name of the full application path for that executable.

score 0 · Answer 2 · answered May 11 '15 at 09:58

0

Try this:

sName = "C:\path\file.pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sName

answered May 11 '15 at 09:58

score 0 · Answer 3 · answered Oct 25 '10 at 14:09

If you have the file saved to fixed location but you're unable to choose where, as a last resort you could always use fso's MoveFile to move it to your specified location

eg. If the file is always saved as "C:\temp\file1.pdf", and you want it on desktop

'Initialise first'
set fso = CreateObject("Scripting.FileSystemObject")
...
'After procedure'
desired_destination = "c:\windows\desktop\"
target_file = "C:\temp\file1.pdf"

fso.MoveFile target_file, desired_destination

If you want to check for an existing file conflict (I believe fso's Move doesn't allow for overwrite), use CopyFile with over-write switched on then Delete the source file if necessary

If you'd like to use a file dialog to choose the destination, you can use the UserAccounts.CommonDialog object (although that doesn't work with Vista) or SAFRCFileDlg.FileOpen (pretty much only works on XP) or borrow an IE prompted box. (Unfortunately the options aren't all that great with VBS to my knowledge)

Check them out here: http://www.robvanderwoude.com/vbstech_ui_fileopen.php

Hi NSN, Thank you for your interest. I would like to describe my problem a bit more, but I find it really hard. The file is named something new everytime. There is no need for a file dialog. It has to be as automatic as possible - no manual inputs :) — Anders, Nov 08 '10 at 15:01

Save an Excel file as PDF to a specific path

3 Answers3