Create a new directory if it does not exist (1 Viewer)

[mshoems]

Hello!

I have a db that needs to export a query to an excel spreadsheet in the my documents folder on the users hard drive. I can make that work fine but if the directory does not exist, I need to have it created and then have the file put in the new directory. Here is the code that I use to export if the dir exists.

Private Sub cmdExportFile_Click()
On Error GoTo Err_cmdExportFile_Click

Dim Myfile

Myfile = "C:\Documents and Settings\" & winUserName & "\My Documents\INV\ " & "query1.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", Myfile, True

MsgBox ("This file has been saved to your documents")
Exit_cmdExportFile_Click:
Exit Sub
Err_cmdExportFile_Click:
MsgBox Err.Description
Resume Exit_cmdExportFile_Click

End Sub

I have tried a couple of things to create a new dir but it always errors out.

This is the first code that I used that did not work. I added this directly after MyFile.

If Not oFileObject.FolderExists("C:\Documents and Settings\" & winUserName & "\My Documents\INV") Then
oFileObject.CreateFolder "C:\Documents and Settings\" & winUserName & "\My Documents\INV"
End If

Any help would be great.

Thanks.

 

[vbaInet]

Look into the MkDir() function.

 

[mshoems]

Okay, I added the mkdir function and when I hit the command button to export the file, it creates the dir and adds the file. Now, when I go to export the file again with the mkdir function still there, I get an error, Path/File access error. This function will only have to happen once because the users will be be putting the files in the same location after the initial directory creation. I was just trying to get away from having each user manually add the new directory before they could export. I will have to find a way to check to see if the dir exists and if it does, not do anything.

Thanks for the help.

 

[vbaInet]

Have a look at the Dir() function. You can google it too.

 

[mshoems]

I got it all figured out. Thanks for pointing me in the right direction.

 

[vbaInet]

You're welcome. Glad we could help.