Is it possible to add subfolders in a dynamic way

Question

Hi

VBA code let me save Reports as PDF in a specific folder. Works just fine.

Is it possible to have VBA adding subfolders in a dynamic way, to that specific folder? Giving the subfolders name like 2018 January, 2018 February etc.

C:/SpecificFolder/2018 January

C:/SpecificFolder/2018 February

C:/SpecificFolder/2019 January

And if that subfolder already exists? Just leave it as is. And save the PDF:s to the existing subfolder

---

Cheers // Peter Forss Stockholm

Answer 1

Hi Peter,

The Dir() function can tell you if a folder already exists, and the MkDir() function can create it, if not.

Hope it helps...

Answer 2

Is it possible to have VBA adding subfolders in a dynamic way, to that specific folder?

...

And if that subfolder already exists? Just leave it as is. And save the PDF:s to the existing subfolder

Hi Peter,

Yes, it can.

For this kind of purposes I use a small function:

Function Check_map(cur_path As String, cur_map As String) As Boolean
  Dim result_map As String
  
  result_map = Dir(cur_path & "\" & cur_map, vbDirectory)
  If (result_map = "") Then
    If (Not Sure(cur_map & " aanmaken?")) Then Exit Function
    MkDir cur_path & "\" & cur_map
  End If
  Check_map = True
  
End Function

If Check_map = True, then the new folder (in Dutch: map) is or exists already, or new made.

I have stored this function in a general module in a shared code database. It is therefor available in any application.

"aanmaken" means: create.

Just play around with the function.

Imb.

Addition:

You can call the function in this way:

  If (Check_map(…)) then

      'move your PDF-file

  end If

Answer 3

You might like to take a look at InvoicePDF.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the following is the code behind a button which creates a PDF file of the current invoice, naming it with the customer name and invoice number.  The file is saved in a subfolder named as the current customer in a parent folder which is selected in a dialogue form at startup.  If the folder does not yet exist it is created:

Private Sub cmdPDF_Click()

  On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current invoice."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varFolder As Variant
    
    If Not IsNull(Me.InvoiceNumber) Then
        ' build path to save PDF file
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
            varFolder = varFolder & "\" & Me.Customer.Column(1)
            MkDir varFolder
            strFullPath = varFolder & "\" & Me.Customer.Column(1) & " " & Me.InvoiceNumber & ".pdf"
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, strFullPath, True
        End If
    Else
        MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
    End If

Exit_Here:
    Exit Sub
    
Err_Handler:
    Select Case Err.Number
        Case FOLDER_EXISTS
        Resume Next
        Case Else
        MsgBox Err.Description
        Resume Exit_Here
    End Select

End Sub

It should not be difficult to amend the code to save the file to a subfolder named as the current month:

            ' create folder if does not exist
            varFolder = varFolder & "\" & Format(VBA.Date,”yyyy mmmm”)
            MkDir varFolder

If the folder already exists the error handler resumes code execution at the next line.

---

Ken Sheridan, Stafford, England

Answer 4

Thank you DB, Imb and Ken

During the years I have learned a lot from you.

I studied your tips and advices. Made some changes and am pleased with the result.

Private Sub Kommandoknapp33_Click()
On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT2 = "No folder set for storing files."
    Dim strFullPath As String
    Dim varFolder As Variant

            ' build path to save file
        varFolder = [Forms]![Alla Val]![EgenPathSäkKopia]
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
            varFolder = varFolder & "\" & Forms![Redovisa]![UNIVYEAR] & " " & MonthName(Forms![Redovisa]![UNIVMONTH])
            MkDir varFolder
        End If

 varFolder = varFolder & "\" & [Forms]![Redovisa]![UNIVYEAR] & " " & StrConv(MonthName([Forms]![Redovisa]![UNIVMONTH]), 3) & " Excise Declaration " & [Forms]![Alla Val]![Firma] & "" & ".pdf"
    DoCmd.OutputTo acOutputReport, "Underlag för alkoholskattedeklarationen", "PDFFormat(*.pdf)", varFolder, False, "", , acExportQualityPrint

Exit_Here:
    Exit Sub
    
Err_Handler:
    Select Case Err.Number
        Case FOLDER_EXISTS
        Resume Next
        Case Else
        MsgBox Err.Description
        Resume Exit_Here
    End Select

End Sub

---

Cheers // Peter Forss Stockholm

Answer 5

Made some changes and am pleased with the result.

Hi Peter,

I hesitated long to give an additional comment.

I you only want to manage Subfolders in this one place, you succeeded, and thus "pleased with the result".

If it happens someday that you also need the management of Subfolders in another place, or in a different application, then it has advantages to separate the functionality of the selection/creation of Subfolders and the moving of a file to a certain Subfolder.

All typicalities around Subfolders can be placed in the Subfolder-tool, including all error code, if necessary. It makes code - in the long term - re-usable, robust and easy to maintain.

Especially the inclusion of error handling in the Subfolder-tool, makes that in the calling form you need not include error handling regarding Subfolders. I went quite a way around this approach, with the result that I do not use error handling in the user interface any more, because all is handled in the underlying routines. This makes programming and maintenance very, very easy.

I can imagine that this all is a couple steps ahead, but give it a thought in future development.

Imb.