save PDF file into sub-folder

GedSalter

New Member
Joined
Apr 24, 2019
Messages
27
I have a macro to save a worksheet and protect it. It then also saves the new worksheet as a PDF. I want to save the PDF into a particular subfolder called "Invoices" The sub folder will always be in the main folder. I cant use a spefic path at the main folder needs to work on different computers. this is what I have so far.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub AddSheet()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim ws As Worksheet
Dim wh As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
Set wh = Worksheets(Sheets.Count)
If ws.Range("e9").Value <> "" Then
wh.Name = ws.Range("E9").Value
ActiveSheet.Protect
End If
wh.Activate
Range("A1").Select

ChDir ActiveWorkbook \ Invoices

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E9")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E9"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] End Sub
[/FONT]


It all works except for the location of the PDF file. If I remover the following line

ChDir ActiveWorkbook \ Invoices

it saves in the same folder as the excel file. But I want it into a subfolder called Invoices which is located in the same folder.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,769
Office Version
2013
Platform
Windows
Hi,

I think from what you are saying is that the macro workbook is in the main folder?
If so you can use ActiveWorkbook.Path


Code:
Sub AddSheet()
Dim ws As Worksheet
 Dim wh As Worksheet
 Set ws = Worksheets(ActiveSheet.Name)
 ActiveSheet.Copy After:=Worksheets(Sheets.Count)
 Set wh = Worksheets(Sheets.Count)
 If ws.Range("e9").Value <> "" Then
 wh.Name = ws.Range("E9").Value
 ActiveSheet.Protect
 End If
 wh.Activate
 Range("A1").Select


[COLOR="#FF0000"]strPath = ActiveWorkbook.Path & "\Invoices\"[/COLOR]

 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[COLOR="#FF0000"]strPath & [/COLOR]Range("E9"), _
 Quality:=xlQualityStandard, IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, OpenAfterPublish:=False

 End Sub
 
Last edited:

GedSalter

New Member
Joined
Apr 24, 2019
Messages
27
sorry Dave but not working.

Its saving the PDF file in the same location as workbook and not in the folder Invoices,



Ged
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
It worked for me. Try this and see what path comes up in the message box

Code:
Sub AddSheet()
    Dim ws As Worksheet
    Dim wh As Worksheet
    Set ws = Worksheets(ActiveSheet.Name)
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    Set wh = Worksheets(Sheets.Count)
    If ws.Range("e9").Value <> "" Then
        wh.Name = ws.Range("E9").Value
        ActiveSheet.Protect
    End If
    wh.Activate
    Range("A1").Select




    strpath = ActiveWorkbook.Path & "\Invoices\"
[COLOR=#ff0000]    MsgBox strpath[/COLOR]
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strpath & Range("E9"), _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False


End Sub
 

GedSalter

New Member
Joined
Apr 24, 2019
Messages
27
This time it worked for me. I must have done a typo.

Thanks to Dave and Paul
 

Watch MrExcel Video

Forum statistics

Threads
1,090,128
Messages
5,412,628
Members
403,435
Latest member
Rickywilson89

This Week's Hot Topics

Top