save PDF file into sub-folder

GedSalter

New Member
Joined
Apr 24, 2019
Messages
25
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,722
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
25
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
441
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
25
This time it worked for me. I must have done a typo.

Thanks to Dave and Paul
 

Forum statistics

Threads
1,078,451
Messages
5,340,365
Members
399,371
Latest member
wilbot

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top