HELP !! VBA Folders & PDF

Manchesterisred

New Member
Joined
Aug 20, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone, I need your help on my Excel macro.

I have an excel file with 20 worksheets. I need the worksheets Individually converted to PDF and then saved in their own folder within a master folder. This is the macro i have so far - its basically been put together from previous macros i have used.

At the moment when i run it, it creates a new folder which is perfect but then it saves the PDF's outside of the folder.

I need the PDF's to be saved in their individual folders ( names the same as the worksheet and also a cell range name, inside of the main the master folder.

Can anyone suggest any ideas ??




Sub DD Letters()
'
' Direct debit Letters convert excel to PDF and save to drive
'
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationAutomatic
End With

'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook

'Create new folder to save the new files in
DateString = Format(Now, "yyyy-mm-dd")
FolderName = Sourcewb.Path & "\" & "Direct Debit Letters dated - " & Format(Sourcewb.Worksheets("DD").Range("C32"), "dd.mm.yyyy")
MkDir FolderName

'Loop through all worksheets and save as individual PDF in same folder as the Excel file
For Each ws In ActiveWorkbook.Worksheets

ws.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"
FolderName = Path & "/" & ws.Name & ".pdf"

Next
'
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,082
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Welcome to the MrExcel forum.

A couple of things first.

1. Try to use code tags when posting code. It helps a lot with readabilty.


2. I noticed that even though you declared most of your variables, there were still a couple of undeclared ones. Use Option Explicit at the top of your code module. This will force you to declare all variables, which help a lot when it comes to debugging.

The problem with your code is that while you are successfully creating your master level folder, you an not creating the needed individual level file folder that you want to store the new files in. Here's one example of how it could be done (not tested).


VBA Code:
Sub DDLetters()
    '
    ' Direct debit Letters convert excel to PDF and save to drive
    '
    Dim Sourcewb As Workbook
    Dim WS As Worksheet
    Dim DateString As String
    Dim FolderName As String, Path As String, FName As String
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
    End With
    
    'Copy every sheet from the workbook with this macro
    Set Sourcewb = ThisWorkbook
    
    'Create new MASTER folder to save the new files in
    DateString = Format(Now, "yyyy-mm-dd")
    FolderName = Sourcewb.Path & "\" & "Direct Debit Letters dated - " & Format(Sourcewb.Worksheets("DD").Range("C32"), "dd.mm.yyyy")
    MkDir FolderName
    
    'Loop through all worksheets and save as individual PDF in same folder as the Excel file
    For Each WS In ActiveWorkbook.Worksheets
        'Create new INDIVIDUAL file folder to save the new file in
        Path = FolderName & "\" & WS.Name
        MkDir Path
        
        FName = Path & "\" & WS.Name & ".pdf"
        
        WS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName
    Next WS
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 

Manchesterisred

New Member
Joined
Aug 20, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel forum.

A couple of things first.

1. Try to use code tags when posting code. It helps a lot with readabilty.


2. I noticed that even though you declared most of your variables, there were still a couple of undeclared ones. Use Option Explicit at the top of your code module. This will force you to declare all variables, which help a lot when it comes to debugging.

The problem with your code is that while you are successfully creating your master level folder, you an not creating the needed individual level file folder that you want to store the new files in. Here's one example of how it could be done (not tested).


VBA Code:
Sub DDLetters()
    '
    ' Direct debit Letters convert excel to PDF and save to drive
    '
    Dim Sourcewb As Workbook
    Dim WS As Worksheet
    Dim DateString As String
    Dim FolderName As String, Path As String, FName As String
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
    End With
   
    'Copy every sheet from the workbook with this macro
    Set Sourcewb = ThisWorkbook
   
    'Create new MASTER folder to save the new files in
    DateString = Format(Now, "yyyy-mm-dd")
    FolderName = Sourcewb.Path & "\" & "Direct Debit Letters dated - " & Format(Sourcewb.Worksheets("DD").Range("C32"), "dd.mm.yyyy")
    MkDir FolderName
   
    'Loop through all worksheets and save as individual PDF in same folder as the Excel file
    For Each WS In ActiveWorkbook.Worksheets
        'Create new INDIVIDUAL file folder to save the new file in
        Path = FolderName & "\" & WS.Name
        MkDir Path
       
        FName = Path & "\" & WS.Name & ".pdf"
       
        WS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName
    Next WS
   
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
Thank you very much Riv01,

can i just ask you : When it is doing this, how can adjust the macro so that it save the name of the PDF as the worksheet AND also the name of a cell in the worksheet. For example - "worksheet 1 - color Red"

Also if i dont want the control sheet to be have folders or PDF's, how do i excluded them from the macro ?


Loop through all worksheets and save as individual PDF in same folder as the Excel file
For Each WS In ActiveWorkbook.Worksheets
'Create new INDIVIDUAL file folder to save the new file in
Path = FolderName & "\" & WS.Name
MkDir Path

FName = Path & "\" & WS.Name & ".pdf"

WS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName
Next WS
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,082
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
can i just ask you : When it is doing this, how can adjust the macro so that it save the name of the PDF as the worksheet AND also the name of a cell in the worksheet. For example - "worksheet 1 - color Red"
Which cell?

Also if i dont want the control sheet to be have folders or PDF's, how do i excluded them from the macro ?
What is the name of the control sheet?
 

Manchesterisred

New Member
Joined
Aug 20, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which cell?


What is the name of the control sheet?
The cell would be be for example D22 which is on each worksheet that is being converted to PDF. ie, the name of each company. This would then also appear on the sub folders and also each PDF alongside each worksheet name.

There are 3 control sheet called - instructions, DDsap and DD.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,082
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
VBA Code:
Sub DDLetters()
    '
    ' Direct debit Letters convert excel to PDF and save to drive
    '
    Dim Sourcewb As Workbook
    Dim WS As Worksheet
    Dim DateString As String
    Dim FolderName As String, Path As String, FName As String
    Dim CellData As String
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
    End With
    
    'Copy every sheet from the workbook with this macro
    Set Sourcewb = ThisWorkbook
    
    'Create new MASTER folder to save the new files in
    DateString = Format(Now, "yyyy-mm-dd")
    FolderName = Sourcewb.Path & "\" & "Direct Debit Letters dated - " & Format(Sourcewb.Worksheets("DD").Range("C32"), "dd.mm.yyyy")
    MkDir FolderName
    
    'Loop through all worksheets and save as individual PDF in same folder as the Excel file
    For Each WS In ActiveWorkbook.Worksheets
        Select Case WS.Name
            Case "instructions", "DDsap", "DD" 'This is the worksheet ignore list. Edit as needed
            Case Else
                CellData = Trim(CStr(WS.Range("D22").Value)) 'Cell data to use in names. Must not contain illegal chars.
                
                'Create new INDIVIDUAL file folder to save the new file in
                Path = FolderName & "\" & WS.Name & "_" & CellData
                MkDir Path
                                
                FName = Path & "\" & WS.Name & "_" & CellData & ".pdf"
                
                WS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName
        End Select
    Next WS
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 

Manchesterisred

New Member
Joined
Aug 20, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
Sub DDLetters()
    '
    ' Direct debit Letters convert excel to PDF and save to drive
    '
    Dim Sourcewb As Workbook
    Dim WS As Worksheet
    Dim DateString As String
    Dim FolderName As String, Path As String, FName As String
    Dim CellData As String
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
    End With
   
    'Copy every sheet from the workbook with this macro
    Set Sourcewb = ThisWorkbook
   
    'Create new MASTER folder to save the new files in
    DateString = Format(Now, "yyyy-mm-dd")
    FolderName = Sourcewb.Path & "\" & "Direct Debit Letters dated - " & Format(Sourcewb.Worksheets("DD").Range("C32"), "dd.mm.yyyy")
    MkDir FolderName
   
    'Loop through all worksheets and save as individual PDF in same folder as the Excel file
    For Each WS In ActiveWorkbook.Worksheets
        Select Case WS.Name
            Case "instructions", "DDsap", "DD" 'This is the worksheet ignore list. Edit as needed
            Case Else
                CellData = Trim(CStr(WS.Range("D22").Value)) 'Cell data to use in names. Must not contain illegal chars.
               
                'Create new INDIVIDUAL file folder to save the new file in
                Path = FolderName & "\" & WS.Name & "_" & CellData
                MkDir Path
                               
                FName = Path & "\" & WS.Name & "_" & CellData & ".pdf"
               
                WS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName
        End Select
    Next WS
   
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 

Manchesterisred

New Member
Joined
Aug 20, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi Triv01, thank you very much. The formula is working perfectly so far.

Can i just ask you If i wanted to add cell E20 to also be included in the title with a dash separator, is there a particular way this should be written ?

And finally, if i wanted for each sub folders contents ( this could be multiple pdfs) to be sent by email to recipients whose email address would be on a separate list, is this possible ? - The criteria for matching the sub folder to the list could be the sub folder name. - what's your thoughts on this one ?

Select Case WS.Name
Case "instructions", "DDsap", "DD" 'This is the worksheet ignore list. Edit as needed
Case Else
CellData = Trim(CStr(WS.Range("D22").Value)) 'Cell data to use in names. Must not contain illegal chars.

'Create new INDIVIDUAL file folder to save the new file in
Path = FolderName & "\" & WS.Name & "_" & CellData
MkDir Path

FName = Path & "\" & WS.Name & "_" & CellData & ".pdf"

WS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName
End Select
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,082
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Triv01, thank you very much. The formula is working perfectly so far.

Can i just ask you If i wanted to add cell E20 to also be included in the title with a dash separator, is there a particular way this should be written ?

That's a bit too vague for meaningful comment. I think you should experiment by changing the code for variable 'CellData' to pull data from cell E20, or any other cells you want, until the path and file name is exactly as you like it.

And finally, if i wanted for each sub folders contents ( this could be multiple pdfs) to be sent by email to recipients whose email address would be on a separate list, is this possible ? - The criteria for matching the sub folder to the list could be the sub folder name. - what's your thoughts on this one ?
Yes of course it is possible if you have the time to spend working on the code. However I think that question is too far away from the original topic of this thread, so you should probably start a new thread for that.
 

Manchesterisred

New Member
Joined
Aug 20, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Thanks Triv01, Just finally, i tried to adjust the cell to add in E20 but didn't work. Is the below not correct ?

CellData = Trim(CStr(WS.Range("B22" & "E20").Value))
 

Forum statistics

Threads
1,144,625
Messages
5,725,365
Members
422,621
Latest member
dfrare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top