add a year folder per year into my directory

[rainbows]

rivate Sub Command437_Click()
Dim FILENAME As String
Dim strFullPath As Variant
    Dim folderpath As Variant
    Dim foldername As String
      Dim varfolder2 As String
      Dim company1 As String
      Dim a As String
      
    Dim varfolder As String
    
   If company = 1 Then company1 = "PPI-Engineering"
  
   If company = 2 Then company1 = "API-Engineering"

   If company = 3 Then company1 = "API-Capacitors"
 
   If company = 4 Then company1 = "temp"
  
   a = Year(Now)
  
             '   FILENAME = "ARNO" & "  " & Me.ARNO
  
               varfolder = DLookup("filepath", "company")
              
               FILENAME = Left(company1, 5) & "   " & "ARNO" & "  " & Me.ARNO
                  
               varfolder1 = varfolder & "\" & company1 & "\" & a & "\" & FILENAME & ".pdf"
                
               Me.Dirty = False
            
DoCmd.OutputTo acOutputReport, "INDIVIDUAL ARNO", acFormatPDF, varfolder1

at present i have a table with a file path " "c:\users\steve\documents\all action request reports. " it then adds the folder to the file path " company1" and then gives me the name of the report . how can it add a yearly folder if it don't exist so the files go into yearly folders

thanks
steve

 

[CJ_London]

investigate the mkdir function

 

[arnelgp]

rivate Sub Command437_Click()
Dim FILENAME As String
Dim strFullPath As Variant
    Dim folderpath As Variant
    Dim foldername As String
      Dim varfolder2 As String
      Dim company1 As String
      Dim a As String
      
    Dim varfolder As String
    
   If company = 1 Then company1 = "PPI-Engineering"
 
   If company = 2 Then company1 = "API-Engineering"

   If company = 3 Then company1 = "API-Capacitors"
 
   If company = 4 Then company1 = "temp"
 
   a = Year(Now)
 
             '   FILENAME = "ARNO" & "  " & Me.ARNO
 
               varfolder = DLookup("filepath", "company")
              
               FILENAME = Left(company1, 5) & "   " & "ARNO" & "  " & Me.ARNO
              
               Call fnForceMkDir(varfolder & "\" & company1 & "\" & a)
              
               varfolder1 = varfolder & "\" & company1 & "\" & a & "\" & FILENAME & ".pdf"
                
               Me.Dirty = False
            
DoCmd.OutputTo acOutputReport, "INDIVIDUAL ARNO", acFormatPDF, varfolder1

...
...

Public Function forceMKDir(ByVal sPath As String)
    Dim v As Variant
    Dim s As String
    Dim i As Integer
    v = Split(sPath, "\")
    On Error Resume Next
    For i = 0 To UBound(v)
        s = s & v(i)
        VBA.MkDir s
        s = s & "\"
    Next
End Function

 

[rainbows]

I m nor sure if I am doing this correctly but this is the error I go.t thanks steve

 

[CJ_London]

check the actual name of the function - looks like it doesn't start with fn

 

[rainbows]

yes I changed that and run it again and got this error

 

[CJ_London]

Suggest you step through the code and check the values are what you expect for varfolder1 (I presume you have a report called INDIVIDUAL ARNO - otherwise better wait for Arnel to come back to you

 

[theDBguy]

Hi. Pardon me for jumping in... When dealing with file and folder names, there are some rules about which characters you can use. Make sure you're not violating any of them.

 

[rainbows]

The full file path is " "c:\users\steve\documents\all action request reports \ PPI- engineering file name " PPI-E ARNO 4 ) 4 is this record number

This does work ok , but they will all stay lie this so I want to ty and get it |"The full file path is " "c:\users\steve\documents\all action request reports \ PPI- engineering/2021 , Then next year it creates a new folder called 2022 for the files to go into etc etc

thanks for you help steve

 

[rainbows]

If I put the folder in the path it works , but I wanted to put the folder ( 2021 in automatically if it was not there. and so on per year

 

[Pat Hartman]

You can create one level of folder at a time. Create the company folder if it isn't there, then create the year folder.

Also, hardcoding the company names in the procedure is not how we do things in an application program based on relational databases. You must have a company table, just put the abbreviation you want to use for that company in the record. That allows you to eliminate all code related to getting the company abbreviation. To ensure the abbreviation is unique and present in the company record, add a unique index for the abbreviation field and make it required and set the Allow ZLS property to NO

 

[CJ_London]

The full file path is " "c:\users\steve\documents\all action request reports \ PPI- engineering/2021

a couple of things wrong with this, not clear whether it is typo's or the actual requirement

you can't have spaces beginning/end of folder names or beginning of file names

and you can't use / in folder or file names