conerting excel to PDF and saving the PDF File in a folder name

The code below, converts an excel file to a PDF file. The problem I'm having is the PDF file is saved in "My Documents". I would like to save the PDF in a folder named "Temp_PDF" using existing file name of the document.

Sub CopyPDF()
Dim ws As Worksheet
Dim Fname As String
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next

Fname = ws.Name

ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False
Next ws
End Sub

Can anyone assist me with code to save the file in a folder "Temp_PDF".

Any assistance would be appreciated.

Thanks

Microsoft Excel

Last Comment

user2073

8/22/2022 - Mon

Anne Troy

7/10/2012

Where is the folder "Temp_PDF"? ON the C drive? In My Documents?

user2073

7/10/2012

ASKER

The folder path is:

C:\Documents and Settings\Workshop1\Desktop

Basically i'm hoping the folder is placed on the desktop of any computer than runs the code. So the operator can process the PDF file at a later time.

Thanks Dreamboat

Anne Troy

7/10/2012

Okay, then I need to know where, in the path you gave me, is the username.

Workshop1? Is that your PC's username?

On my Windows 7 PC, the path to my Desktop is C:\Users\Anne\Desktop

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

user2073

7/10/2012

ASKER

Yes, workshop1 is the PC's username.

Hope this helps?

zedgama3

7/10/2012

This is not my forte, but referencing http://www.vbaexpress.com/forum/showthread.php?t=40886

I believe that you need to do:

'ChDrive "c:"
'ChDir GetFolderName(fName)

Open in new window

Before executing your export.

As for the path to desktop, it can be found using SpecialFolderPath = objWSHShell.SpecialFolders("Desktop"). ( Reference http://www.ozgrid.com/forum/showthread.php?t=24985 )

user2073

7/10/2012

ASKER

Thanks for the links and the code but I still can't place the PDF file in the folder required.
I feel the idea is good but where within the code above should I place the path?

Thank you

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

zedgama3

7/10/2012

Using the following example, I would assume that ws.ExportAsFixedFormat will output to current directory. In the example below it appears that the developer instituted a ChDrive and ChDir to specify where the file should be written.

In your case you want the file to output to the desktop, so your ChDir statment should probably look something like 'ChDir objWSHShell.SpecialFolders("Desktop")

I'm sure if this works for you then you could also add additional code to put it into the folder you specified on the desktop, making sure first of course to check that the folder existed and created if needed.

Sub Test_PublishToPDF() 
    Dim sDirectoryLocation As String, sName As String 
     
    sDirectoryLocation = ThisWorkbook.Path 
    sName = sDirectoryLocation & "\" & Range("E4").Value2 & ".pdf" 
    PublishToPDF sName, activeworksheet 
End Sub 
 
 
Sub PublishToPDF(fName As String, ws As Worksheet) 
    Dim rc As Variant 
     
     'ChDrive "c:"
     'ChDir GetFolderName(fName)
    rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF") 
    If Not rc Then Exit Sub 
     
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _ 
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ 
    :=False, OpenAfterPublish:=False 
End Sub