Check for/Create Folder, then export Query

[JoeBruce]

After some searching I think I've got all the pieces to my necessary answer. But my limited knowledge of VBA makes it hard to put it all together.

I have a button on a report, and on click it exports the query (which the report is based on) to an Excel file. However, I realized that if someone starts changing the folder names, then the export won't work. So, I want to verify that the folder exists; if not, the code should make the folder and export; if so, it should just go ahead with the export.

Below are the pieces of code I have so far. The top part I copied from some suggestion found while searching the net/forums. Partly I'm struggling with how/where to stick the "TestForDir()" part - I used the code builder for the exporting part, now I'm trying to add the test for directory part. I still don't understand the hierarchy of Subs (or what the heck "sub" and "dim" even mean...)

I also feel that once I define the folder path name as a string, I should be able to use that in naming the file for the Output Query command (instead of the entire drive name and folders/sub-folders I have now).

Sub TestForDir()
    Dim strDir As String
    strDir = "P:\Interpretation\Education\Teacher Information\Reports and Data\"
     
    If Dir(strDir, vbDirectory) = "" Then
        MkDir strDir
    Else
        MsgBox "Directory exists."
    End If
End Sub

Private Sub btnExport_Click()
     
    DoCmd.OutputTo acOutputQuery, "qryLocalTeacherEmails", acFormatXLSX, "P:\Interpretation\Education\Teacher Information\Reports and Data\Local Teacher Emails " & (Format(Date, "mm-dd-yyyy")) & ".xlsx", True
   
    MsgBox "File exported to P:\Interpretation\Education\Teacher Information\Reports and Data", vbInformation, "Export Successful!"
End Sub

Can someone help out and put this stuff in the correct hierarchy and order? Thanks as always.

 

[Cronk]

I would not see a program as having aThere is no "hierachy". Something cause code to start running (in your case, a button click) and the code will be executed in statement order depending on if statements, loops and calls to other procedure.

You just need to call the TestDirectory sub by inserting the line in red

Sub TestForDir()
     Dim strDir As String     
      strDir = "P:\Interpretation\Education\Teacher Information\Reports and Data"           
      If Dir(strDir, vbDirectory) = "" Then         
          MkDir strDir     
      Else         
         MsgBox "Directory exists."
      End 
If End Sub  

Private Sub btnExport_Click()     
    [B][COLOR=Red]TestForDir [/COLOR][/B]     
    DoCmd.OutputTo acOutputQuery, "qryLocalTeacherEmails", acFormatXLSX, "P:\Interpretation\Education\Teacher Information\Reports and Data\Local Teacher Emails " & (Format(Date, "mm-dd-yyyy")) & ".xlsx", True         
   MsgBox "File exported to P:\Interpretation\Education\Teacher Information\Reports and Data", vbInformation, "Export Successful!" 
End Sub

Incidentally, are you sure the folders further up the chain will not be renamed/deleted?

 

[JoeBruce]

statement order = hierarchy

At least the hierarchy the programmer creates. I guess I'm using it somewhat incorrectly just to make an analogy in my head. But let's not argue semantics

Okay thanks I think I follow that. In reply to your end question: will this code only make the "Reports and Data" folder? I assumed it would work through and create the chain if necessary, as long as there was a "P" drive to write to. If not, then my approach might be bunk...

 

[Cronk]

It would be nice if MkDir would create all the folders making up the path but it does not. In fact, if P is a mapped drive, you cannot assume that the user has that mapping.

I see a program as steps butI won't argue "head analogies".

 

[JoeBruce]

Always learning! Good to know about the MkDir command.

After mulling over this new information, and doing some more research, I decided to export the files to a folder on the user's desktop. For the way this database will be utilized (and restrictions on our network), this will work perfectly.

Sub TestForDir()
    Dim strDir As String
    strDir = "C:\Users\" & Environ("username") & "\Desktop\Database Reports"
    If Dir(strDir, vbDirectory) = "" Then
        MkDir strDir
        MsgBox "A new folder has been created on your desktop called 'Database Reports.' All exported files will be sent there.", vbInformation, "Desktop Folder Created"
    Else
        
    End If
End Sub
'**************************************************************

Private Sub btnExport_Click()
    TestForDir
    Dim strfilename As String
    strfilename = "C:\Users\" & Environ("username") & "\Desktop\Database Reports\Local Teacher Emails from " & (Format(Date, "mm-dd-yyyy")) & ".xlsx"
    DoCmd.OutputTo acOutputQuery, "qryLocalTeacherEmails", acFormatXLSX, strfilename, True
    MsgBox "File exported to 'Database Reports' on your desktop. Please move the file to 'P:\Interpretation\Education\Teacher Information\Reports and Data' to share it on the network.", vbInformation, "Export Successful!"
End Sub

Just wanted to post my solution and give a thanks to Cronk for the advice.