Solved change folder path with varible

R

rainbows

Registered User.
Local time
Today, 02:38
Joined
Apr 21, 2017
Messages
428
when i create a bill on materials the file goes into the first location i have listed which is my folder path

\\192.168.2.11\contdocs\Engineering\material list in pdf


now they want to move it to another location listed below


\\192.168.2.11\contdocs\Engineering\Document libraries\approved documents-controlled\product library-controlled\ ????

the product numbers are as below as you can see the first Charectors could be different
how can the code be changed so a folder is created where i have put the ??? marks


thanks

Products Products

Product No
AC4619M1
DC4521M1
WE4573M1
AZ4636M1
EC1363M1





Code: 
On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current product ."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varfolder As Variant
    
   If Not IsNull(Me.Product_No) Then
        ' build path to save PDF file
      
        varfolder = DLookup("Folderpath", "pdfFoldere")
        If IsNull(varfolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
                                    
          
            MkDir varfolder
                     strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & "   " & "issue " & " " & Me.issueNo & ".pdf"
          
        
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "material list in pdf", acFormatPDF, strFullPath
        End If
    Else




        MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
    End If
 
Append

left(product_no,2)

to varfolder
 
Exactly what do you want to name this new folder? What characters of Product_No do you want to use?
 
You concatenate it wherever you want it to be located in the folder path.

MkDir varfolder & "\" & Left(Me.Product_No, 2)
strFullPath = varfolder & "\" & Left(Me.Product_No, 2) & "\Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"
 
Code: 
Private Sub report_to_file_Click()
On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current product ."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varfolder As Variant
    
   If Not IsNull(Me.Product_No) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varfolder = DLookup("Folderpath", "pdfFoldere")
        If IsNull(varfolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
          
           strFullPath = varfolder & "\" & Left(Me.Product_No, 2) & "\" & "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"
         Call subUNCFolder(varfolder)
          ' MkDir varfolder
          
        ' strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & "   " & "issue " & " " & Me.issueNo & ".pdf"
           ' strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & ".pdf"
        
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "material list in pdf", 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



Code: 
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


it breaks down due to the amount of "\" in it so the second code should have taken care of it but for some reason it dont output the file


1692263500709.png
 
Uncomment your Debug.Prints add a few more and walk through your code.
 
Code: 
strFullPath = varfolder & "\" & Left(Me.Product_No, 2) & "\" & "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"

strFullPath = varfolder & "\" & Left(Me.Product_No, 2) & "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"


wrong result   “ ACProduct Number  AC1053M1 issue  “


strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & "   " & "issue " & " " & Me.issueNo & ".pdf"

if i use the first line it dont work
if i se the second line it works but the wrong result as the AC has just gone in front of the word product number
if i use the 3rd line it works but i have not put in the & "\" & left ( me .product _no,2)
the problem only accurs it i use 2 "/" in the code

steve
 
rainbows said:
Code: 
strFullPath = varfolder & "\" & Left(Me.Product_No, 2) & "\" & "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"

strFullPath = varfolder & "\" & Left(Me.Product_No, 2) & "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"


wrong result   “ ACProduct Number  AC1053M1 issue  “


strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & "   " & "issue " & " " & Me.issueNo & ".pdf"

if i use the first line it dont work
if i se the second line it works but the wrong result as the AC has just gone in front of the word product number
if i use the 3rd line it works but i have not put in the & "\" & left ( me .product _no,2)
the problem only accurs it i use 2 "/" in the code

steve
Click to expand...
The strings are only going to be placed where you place them? :(
If AC is meant to be a folder then you need to append a \ to indicate that.
You were shown that in post 5?

You cannot just make a folder path up if it does not exist. So if whatever \AC\ does not exist, YOU need to create it.

Your varfolder should include the AC or whatever those two characters are.

AGAIN! if you debug and walk your code, these errors will be obvious.
 
Last edited:
So what result do you want? Provide a complete example of a file path you want to build. Is "AC" supposed to be a folder?
 
I would build the path piece by piece.

something like:


Code: 
Dim fso As New FileSystemObject

    Dim strFullPath As String
    Dim MyFileName As String

    MyFileName = "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"

    strFullPath = varfolder & "\"

    ChkFolder (strFullPath)

    strFullPath = strFullPath & Left(Me.Product_No, 2) & "\"

    ChkFolder (strFullPath)

    strFullPath = fso.BuildPath(strFullPath, MyFileName)

Code: 
Sub ChkFolder(strFold As String)

    Dim fso As New FileSystemObject

    If Not fso.FolderExists(strFold) Then
        fso.CreateFolder (strFold)
    End If

End Sub
 
Code: 
Private Sub report_to_file_Click()
On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current product ."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varfolder As Variant
    
   If Not IsNull(Me.Product_No) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varfolder = DLookup("Folderpath", "pdfFoldere")
        If IsNull(varfolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
          MkDir varfolder & "\" & Left(Me.Product_No, 2) & "\" & Me.Product_No & "\"
           strFullPath = varfolder & "\" & Left(Me.Product_No, 2) & "\" & Me.Product_No & "\" & "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"
         Call subUNCFolder(varfolder)
          ' MkDir varfolder
          
        ' strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & "   " & "issue " & " " & Me.issueNo & ".pdf"
           ' strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & ".pdf"
        
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "material list in pdf", 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

This works other than if the varible folder ca, bc, dc etc is not there it will not create it of which i need it to do
the DC , AC, ETC are the first 2 charectors within the product number , and there could be many products that start with ac or dc etc so it kkes them all in the correct folder


steve

1692302450939.png
 
You have to create folders in their hierarchy order.
So if var folder is c:\temp\product and the product folder does not exist, then adding ac\ac12345 or whatever is not going to work.
 
There is already a procedure ("subUNCFolder") that does this. But this one has a name so that you don't suspect that it creates the directory structure. ;)

I once allowed myself to structure the code a bit:
Code: 
Private Sub report_to_file_Click()

On Error GoTo Err_Handler

    Const NoProductMessageText = "No current product ."

    With Me.Product_No
        If Len(.Value) > 0 Then
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            SaveMaterialListPdf .Value, Me.IssueNo.Value
        Else
            MsgBox NoProductMessageText, vbExclamation, "Invalid Operation"
        End If
    End With

Exit_Here:
    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here

End Sub

Private Sub SaveMaterialListPdf(ByVal ProductNo As String, ByVal IssueNo As Variant)

    Dim PdfFolderPath As String
    Dim strFullPath As String

    PdfFolderPath = GetMaterialListPdfPath(ProductNo)
    subUNCFolder PdfFolderPath
    ' ^- Procedure name without indication of responsibility ... what would be with CreateFolderIfMissing

    strFullPath = PdfFolderPath & "\" & GetMaterialListPdfFileName(ProductNo, vIssueNo)

    DoCmd.OutputTo acOutputReport, "material list in pdf", acFormatPDF, strFullPath
    ' no filter?

End Sub

Private Function GetMaterialListPdfPath(ByVal ProductNo As String) As String

    Const NoPdfFolderSetErrMessageText = "No folder set for storing PDF files."
    Const NoPdfFolderSetErrNumber = 123
    Dim BaseFolder As String

    BaseFolder = Nz(DLookup("Folderpath", "pdfFoldere"), vbNullString)
    If Len(BaseFolder) = 0 Then
        Err.Raise NoPdfFolderSetErrNumber, "GetMaterialListPdfPath", NoPdfFolderSetErrMessageText
    End If

    GetMaterialListPdfPath = BaseFolder & "\" & Left(ProductNo, 2) & "\" & ProductNo

End Function

Private Function GetMaterialListPdfFileName(ByVal ProductNo As String, ByVal IssueNo As Variant) As String
    GetMaterialListPdfFileName = "Product Number " & " " & ProductNo & " " & "issue " & " " & IssueNo & ".pdf"
                                                ^-----^  2x " " ?
End Function
 
Last edited:
Code: 
Private Sub report_to_file_Click()
On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current product ."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varfolder As Variant
  
    
   If Not IsNull(Me.Product_No) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varfolder = DLookup("Folderpath", "pdfFoldere")
      
        If IsNull(varfolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
          
         varfolder = varfolder & "\" & left(Me.Product_No, 2) & "\" & Me.Product_No & "\"
         ' varfolder = varfolder & "\" & Me.Text52 & "\" & Me.Product_No & "\"
                  '''  MkDir varfolder & "\" & Left(Me.Product_No,2) & "\" & Me.Product_No & "\"
           strFullPath = varfolder & "\" & "Product Number " & " " & Me.Product_No & " " & "issue " & " " & Me.issueNo & ".pdf"
         '
        
         Call subUNCFolder(varfolder)
        
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "material list in pdf", acFormatPDF, strFullPath
        End If
      
       '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

this is now working and still creates the missing folder like AC, EC, BC ETC I can either use - left(Me.Product_No, 2) or create a test box on form and use - Me.Text52 of which i still used the left(Me.Product_No, 2) is there a correct way from these 2 options ?

thanks steve
 
Either way is correct.

Do you feel it is necessary to display the AC prefix in textbox on form?
 
no. it was a case of me not really knowing how to do it so i tried about 500 ways and got frustrated and that was one of them

thanks
steve
 
Might want to give your control a more meaningful name?
Text52 is not going to mean much a few months down the road.
 
You must log in or register to reply here.

Similar threads

R
4 tables into one
Replies
6
Views
619
The_Doc_Man
The_Doc_Man
R
Solved file path
Replies
14
Views
1,254
rainbows
R
R
struggling with error on e.mail address
Replies
8
Views
661
rainbows
R
R
call up second event proceedure within the first one
Replies
3
Views
450
Gasman
Gasman
R
add a year folder per year into my directory
Replies
11
Views
871
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom