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, _
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

Anne Troy

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

John Smith

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

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

John Smith

ASKER

Yes, workshop1 is the PC's username.

Hope this helps?

zedgama3

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 )

John Smith

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

zedgama3

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

John Smith

ASKER

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.

Thanks

ASKER CERTIFIED SOLUTION

zedgama3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Smith

ASKER

All the information provided, has assisted in finding a final solution to the problem.

Thanks for your assistance.