Solved file path (1 Viewer)

[rainbows]

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.id) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
                                   
            varFolder = varFolder & "\" & [Order Details subform]![CustomerName]
            MkDir varFolder
            'strFullPath = varFolder & "\" & ME.CustomerName & " " & Me.Invoicenumber & ".pdf"
          strFullPath = varFolder & "\" & "NCO Number " & " " & Me.NCO_No & ".pdf"
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "order acknowledgement", acFormatPDF, strFullPath
        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

Above is the code i use to create a file path and put a report into it

this is the folder path in a table called pdffolder


\\192.168.2.13\contdocs\sales\orders\current orders 2023

could the code be changed so that the year is generated within the code just before the customer name
and the folder path is just \\192.168.2.13\contdocs\sales\orders\current orders


thanks steve

 

[The_Doc_Man]

You are already using concatenation to generate the path. IF you want the current year in that path, just concatenate using the result of this call:

Year = CStr( DatePart( "yyyy", Now() ) )

MS Access: DatePart Function

This MSAccess tutorial explains how to use the Access DatePart function with syntax and examples. The Microsoft Access DatePart function returns a specified part of a given date.

www.techonthenet.com

I used CStr() function because DatePart returns a number.

 

[rainbows]

Do you mind telling me where i would put that in the code

thanks steve

 

[The_Doc_Man]

At some point in the code, you will have insert a concatenation via ampersands. But where you do that is going to depend on where you want it to go. Look at the area in that code sample where you were concatenating strFullPath. You have several distinct things you were concatenating. Generate the year string into a string variable and add one more concatenation term there, for the case where you are generating that path. Where you use an extant path, it appears that you would not have to concatenate anything since, if you were using that path, it already was there and, by implication, was already correct. (Else why would you have used it?)

 

[rainbows]

this problem with it being in the table is i would have to change the year every year
i want to get this final path where my file will go to

\\192.168.2.14\contdocs\sales\orders\current orders \2023\customer name \ NCO No
and the year is varible as is the customername and nco number

the file in the screen shot has a folder called "current orders 2003" and i dont want that just current orders and the code giving me the customer name and nco number


varFolder = varFolder & "\" & [Order Details subform]![CustomerName] is now

but i think it need to go here varFolder = varFolder & year "\" & [Order Details subform]![CustomerName]


hope this helps

steve

 

[The_Doc_Man]

Steve, I can't actually tell whether that helps, hurts, or does nothing - because YOU are the one who has to decide what you want to do. Here is a hint, though. During debugging, if you can set a breakpoint on the line of code that builds your path, let it build that path. Then EITHER using a mouse-over, examine the generated path..., or open the immediate window and do a DEBUG.PRINT of the variable containing the freshly-built path. You will find very quickly whether it is doing what you wanted. Remember, using breakpoints, the break occurs BEFORE the line is executed - so you might have to use the F8 key to single-step, depending on where you placed the relevant breakpoint.

 

[moke123]

I would consider using FSO so you can avail yourself to fso.buildpath, fso.folderexists, fso.fileexists, fso.createfolder, etc.
https://analystcave.com/vba-filesystemobject-fso-in-excel/

 

[moke123]

Some air code of what using FSO might look like. (need reference to Microsoft Scripting Runtime)

Public Sub MyPaths()

    Dim BasePath As String
    Dim YearPath As String
    Dim CustomerPath As String
    Dim FileName As String
    Dim FilePath As String

    Dim fso As New FileSystemObject
  
'get variables for each segment of the folder paths
    BasePath = "\\192.168.2.14\contdocs\sales\orders\current orders"     '\\192.168.2.14\contdocs\sales\orders\current orders\

    YearPath = fso.BuildPath(BasePath, CStr(Year(Date)))                '\\192.168.2.14\contdocs\sales\orders\current orders\2023

    CustomerPath = fso.BuildPath(YearPath, Me.CustomerName)             '\\192.168.2.14\contdocs\sales\orders\current orders\2023\AcmeWidgets

    MyFileName = "NCO Number " & Me.NCO_No & ".pdf"                     'NCO Number 4.pdf

    MyFilePath = fso.BuildPath(CustomerPath, MyFileName)                '\\192.168.2.14\contdocs\sales\orders\current orders\2023\AcmeWidgets\NCO Number 4.pdf


'check if each folder exists and create it if not. a new year folder will be created every January 1
    If Not fso.FolderExists(BasePath) Then fso.CreateFolder (BasePath)

    If Not fso.FolderExists(YearPath) Then fso.CreateFolder (YearPath)

    If Not fso.FolderExists(CustomerPath) Then fso.CreateFolder (CustomerPath)

    'output to pdf
    DoCmd.OutputTo acOutputReport, "order acknowledgement", acFormatPDF, MyFilePath

End Sub

 

[rainbows]

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
     Dim CustomerPath As String
    Dim YearPath As String
 

YearPath = CStr(year(date))
 
   If Not IsNull(Me.id) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
      
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
                                    
10          varFolder = varFolder & "\" & YearPath & "\" & [order details subform]![CustomerName]
20      ' varFolder = varFolder & "\" & [order details subform]![CustomerName]
30           '        varFolder = varFolder & "\" & Me.CustomerName
            MkDir varFolder
          
            'strFullPath = varFolder & "\" & ME.CustomerName & " " & Me.Invoicenumber & ".pdf"
          strFullPath = varFolder & "\" & "NCO Number " & " " & Me.NCO_No & ".pdf"
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "order acknowledgement", acFormatPDF, strFullPath
        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

hi . i am still trying to do this but as you can see i am useless at vba

line 10 gives me error message path not found . line 20 and 30 do work as they always have but without the year folder i am trying to generate thro the code as we did with the customer name

i am trying to create one folder that is the year not sure if i am calling it up in the correct place


thanks for your help

steve

 

[arnelgp]

here, try your modified code.
put subUNCFolder sub in a Module so it can be called from anywhere your db.

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.Id) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
                                  
            ' arnelgp
            ' add Year before CustomerName
            ' add subUNCFolder() to create folder
            varFolder = varFolder & "\" & Year(Date) & "\" & [Order Details subform]![CustomerName]
            'MkDir varFolder
            Call subUNCFolder(varFolder)
            'strFullPath = varFolder & "\" & ME.CustomerName & " " & Me.Invoicenumber & ".pdf"
          strFullPath = varFolder & "\" & "NCO Number " & " " & Me.NCO_No & ".pdf"
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "order acknowledgement", acFormatPDF, strFullPath
        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

' NOTE!!!
' put below code to another Module
'
Public Sub subUNCFolder(ByVal path As String)
Dim var, s As String
Dim i As Integer
var = Split(path, "\")
On Error Resume Next
For i = 0 To UBound(var)
    s = s & var(i) & "\"
    VBA.MkDir s
    'Debug.Print s
Next
End Sub

 

[rainbows]

arnelgp , that works great thank you . if i wanted just to create the folder path without sending the pdf file would i just have to remove the line​

DoCmd.OutputTo acOutputReport, "order acknowledgement", acFormatPDF, strFullPath?

thanks steve

 

[moke123]

arnelgp , that works great thank you . if i wanted just to create the folder path without sending the pdf file would i just have to remove the line​

DoCmd.OutputTo acOutputReport, "order acknowledgement", acFormatPDF, strFullPath?

thanks steve

yes, but I still think you should consider Fso . Its much simpler.

 

[arnelgp]

arnelgp , that works great thank you . if i wanted just to create the folder path without sending the pdf file would i just have to remove the line

yes. you can also have a separate button just to create the folder/path.

 

[The_Doc_Man]

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
     Dim CustomerPath As String
    Dim YearPath As String


YearPath = CStr(year(date))

   If Not IsNull(Me.id) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
     
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
                                   
10          varFolder = varFolder & "\" & YearPath & "\" & [order details subform]![CustomerName]
20      ' varFolder = varFolder & "\" & [order details subform]![CustomerName]
30           '        varFolder = varFolder & "\" & Me.CustomerName
            MkDir varFolder
         
            'strFullPath = varFolder & "\" & ME.CustomerName & " " & Me.Invoicenumber & ".pdf"
          strFullPath = varFolder & "\" & "NCO Number " & " " & Me.NCO_No & ".pdf"
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "order acknowledgement", acFormatPDF, strFullPath
        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

hi . i am still trying to do this but as you can see i am useless at vba

line 10 gives me error message path not found . line 20 and 30 do work as they always have but without the year folder i am trying to generate thro the code as we did with the customer name

i am trying to create one folder that is the year not sure if i am calling it up in the correct place


thanks for your help

steve

Just an observation for you. While technically possible, it is generally considered a bad idea to have line numbers in one part of a routine and a bunch of lines without numbers in another part of the same routine. No, you don't HAVE to do that numbering on every line. But at some time down the road, if you wanted to repair or upgrade something, inconsistent numbering can present a problem of its own in the form of confusion. Usually manifested by someone reading the code before attempting maintenance, and to have the maintainer unable to understand the methods used. This is how modules get re-written rather than just modified.

 

[rainbows]

I only used the numbering on those 3 line items just to show the 3 ways i was trying to make it work .i dont normally do that

thanks steve