Link to home
Start Free TrialLog in
Avatar of John Smith
John SmithFlag for Australia

asked on

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, _
    Next ws
End Sub

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

Any assistance would be appreciated.

Avatar of Anne Troy
Anne Troy
Flag of United States of America image

Where is the folder "Temp_PDF"? ON the C drive? In My Documents?
Avatar of John Smith


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
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
Yes, workshop1 is the PC's username.

Hope this helps?
Avatar of zedgama3

This is not my forte, but referencing

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 )
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
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 

Open in new window

The above code is stopping at "activeworksheet" would i run the code. Do you have any thoughts as to why? Sorry for taking up your time.

Avatar of zedgama3

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All the information provided, has assisted in finding a final solution to the problem.

Thanks for your assistance.