Save an Excel file as pdf on a path

Question

Hi all,

I would like to save an excel file as a .pdf file on a specific location and 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 on a specific location.

It should be like this: Save pdf file here: "C:\path\file.pdf"

I hope some of you can help me

regards Anders

Answer 1

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