none
Is it possible to add subfolders in a dynamic way RRS feed

  • 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

    Tuesday, December 4, 2018 8:23 PM

Answers

  • 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

    • Edited by Imb-hb Tuesday, December 4, 2018 9:40 PM addition
    • Marked as answer by ForssPeterNova Wednesday, December 5, 2018 6:16 AM
    Tuesday, December 4, 2018 9:34 PM
  • 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


    • Edited by Ken Sheridan Tuesday, December 4, 2018 10:06 PM Typo corrected.
    • Marked as answer by ForssPeterNova Wednesday, December 5, 2018 6:16 AM
    Tuesday, December 4, 2018 10:05 PM

All replies

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

    Tuesday, December 4, 2018 9:33 PM
  • 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

    • Edited by Imb-hb Tuesday, December 4, 2018 9:40 PM addition
    • Marked as answer by ForssPeterNova Wednesday, December 5, 2018 6:16 AM
    Tuesday, December 4, 2018 9:34 PM
  • 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


    • Edited by Ken Sheridan Tuesday, December 4, 2018 10:06 PM Typo corrected.
    • Marked as answer by ForssPeterNova Wednesday, December 5, 2018 6:16 AM
    Tuesday, December 4, 2018 10:05 PM
  • 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

    Wednesday, December 5, 2018 6:27 AM
  • 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.

    Wednesday, December 5, 2018 10:26 AM