save PDF file into sub-folder

[GedSalter]

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.
​

 

[daverunt]

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

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

 

[GedSalter]

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]

It worked for me. Try this and see what path comes up in the message box

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]

This time it worked for me. I must have done a typo.

Thanks to Dave and Paul

 

[Paul Ked]