Create Folder using field data

[chuckcoleman]

Hi, I have some code that works, but I need to tweak it to create a folder that uses the data in a field as the folder name. Here is the code that works, (Note, ignore the MsgBox entries I've used for testing):

Dim fs, cf, strFolder
Dim DestPath As String
Dim SrcFile As String
Dim FileName As String
Dim ShowPDF As Boolean
Dim strHyperlinkFile As String
Dim strSelectedFile As String
Dim StrSelectedFileSubDirectory As String
Dim MyDate As String

strFolder = "C:\HOA\Directories\"
Set fs = CreateObject("Scripting.FileSystemObject")
If FolderExists("C:\HOA\Directories") Then
'MsgBox ("The folder exists")
Else
Set cf = fs.CreateFolder(strFolder)
If FolderExists("C:\HOA\Directories") Then
Else
End If
End If
' there is more code that runs query's and creates a PDF file after this.

----------------------------------------------------------------------------------------------------
This is where I'm having trouble. I want this folder created: C:\HOA\Directories\FoxMeadowsWest

Dim fs, cf, strFolder
Dim DestPath As String
Dim SrcFile As String
Dim FileName As String
Dim ShowPDF As Boolean
Dim strHyperlinkFile As String
Dim strSelectedFile As String
Dim StrSelectedFileSubDirectory As String
Dim MyDate As String
Dim HOA As String
Dim strFolder2 As String

HOA = Replace([SubName], " ", "") ' & """" ([SubName] is the name of a field and the data can have spaces in it. 'Replace' removes the spaces. This works)
MsgBox ("1 The HOA is: " & HOA) (The [SubName] data is "Fox Meadows West" and HOA Returns "FoxMeadowsWest" which is correct)
MsgBox ("1a The path is ""C:\HOA\Directories\" & HOA) NOT Correct, returns "C:\HOA\Directories\FoxMeadowsWest

strFolder = """C:\HOA\Directories\" & HOA & """"
strFolder2 = strFolder
' strFolder = "C:\HOA\Directories\" & HOA
MsgBox ("2 The folder is: " & strFolder2) LOOKS correct, returns "C:\HOA\Directories\FoxMeadowsWest"
Set fs = CreateObject("Scripting.FileSystemObject")
If FolderExists(strFolder2) Then
MsgBox ("3 The folder exist") OK
Else
MsgBox ("5 The folder does NOT exist") OK
MsgBox ("6 The value of strFolder: " & strFolder) LOOKS OK, returns "C:\HOA\Directories\FoxMeadowsWest"
Set cf = fs.CreateFolder(strFolder2) ERROR this is when I get "Run-time error 52 Bad file name or number"
If FolderExists("""C:\HOA\Directories\" & HOA) Then
MsgBox ("4 I've created the folder")
Else
End If
End If
More lines of code to run queries and create a PDF

Any ideas?

Chuck

 

[pbaldy]

I'm confused, what you said you want and what you're getting appear the same:

This is where I'm having trouble. I want this folder created: C:\HOA\Directories\FoxMeadowsWest


NOT Correct, returns
C:\HOA\Directories\FoxMeadowsWest

 

[chuckcoleman]

Thank you Paul. I was at this too long yesterday and lost sight of what was causing the problem. I added some MsgBox's to the code that worked so I could see what was going on and the problem I had with the code that wasn't working is that I was adding quotation marks before the full path and after it. After I figured that out I removed the quotation marks and works like it should. I appreciate your help! Chuck

 

[Isaac]

Please indent code to make it readable, then use code tags when posting to make it readable in the post

 

[gemma-the-husky]

Note you don't need to leave vba to do this. vba has a lot of commands to help.
Among others:

dir() will check the existence of a folder
mkdir() will create a folder
rmdir() will delete a folder
kill() will delete a file
name() will rename a file

mkdir "C:\HOA\Directories\FoxMeadowsWest"

Now, offhand I am not sure if that command will ALSO create folders "C:\HOA" and "C:\HOA\Directories" if they do not already exist.
Maybe that is your issue.

 

[pbaldy]

Thank you Paul.

Happy to help!

 

[Abo Abd_Allah]

you can write code with few line as this:

If len(dir("C:\HOA", vbDirectory) & "") = 0 then
mkdir("C:\HOA\Directories\FoxMeadowsWest")
Else
'write here what you want happen after create folder
'and move your PDF to the folder
End if

test code and tell us what happened
good luck

 

[chuckcoleman]

Please indent code to make it readable, then use code tags when posting to make it readable in the post

I realized when I wrote it, after a long day, it could have been written to be more readable. I'll do better next time. Thank you.

 

[chuckcoleman]

Note you don't need to leave vba to do this. vba has a lot of commands to help.
Among others:

dir() will check the existence of a folder
mkdir() will create a folder
rmdir() will delete a folder
kill() will delete a file
name() will rename a file

mkdir "C:\HOA\Directories\FoxMeadowsWest"

Now, offhand I am not sure if that command will ALSO create folders "C:\HOA" and "C:\HOA\Directories" if they do not already exist.
Maybe that is your issue.

That's great feedback/input. Thank you.

 

[chuckcoleman]

you can write code with few line as this:

If len(dir("C:\HOA", vbDirectory) & "") = 0 then
mkdir("C:\HOA\Directories\FoxMeadowsWest")
Else
'write here what you want happen after create folder
'and move your PDF to the folder
End if

test code and tell us what happened
good luck

Here is the code that I finally worked through that works. [SubName] is the name of a field in the forms query. Thank you for your suggestion.

Dim fs, cf, strFolder
Dim DestPath As String
Dim SrcFile As String
Dim FileName As String
Dim ShowPDF As Boolean
Dim strPathAndFile As String
Dim strSelectedFile As String
Dim MyDate As String
Dim HOA As String
Dim strFolder2 As String


HOA = Replace([SubName], " ", "") ' & """"
strFolder = "C:\HOA\Directories\" & HOA
strFolder2 = strFolder
Set fs = CreateObject("Scripting.FileSystemObject")
If FolderExists(strFolder2) Then
GoTo XYZ
Else
Set cf = fs.CreateFolder(strFolder2)
End If
XYZ:
DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete Temp Directory Table data-2"
DoCmd.Close acQuery, "Delete Temp Directory Table data-2"
DoCmd.OpenQuery "Append FN and LN to Temp Directory Table-2"
DoCmd.Close acQuery, "Append FN and LN to Temp Directory Table-2"
DoCmd.OpenQuery "Append FN2 and LN2 to Temp Directory Table-2"
DoCmd.Close acQuery, "Append FN2 and LN2 to Temp Directory Table-2"
MyDate = Format(Date, "mmddyy")
DestPath = "C:\HOA\Directories\" & HOA
strSelectedFile = "Directory - Alpha - " & MyDate & ".pdf"
strPathAndFile = DestPath & "\" & strSelectedFile
ShowPDF = False
SrcFile = "Directory-Alpha"
FileName = Application.CurrentProject.Path & strPathAndFile
DoCmd.OutputTo acOutputReport, SrcFile, "*.pdf", strPathAndFile, ShowPDF, "", 0, acExportQualityPrint
MsgBox ("I've created your Directory-Alpha Sort PDF file.")
DoCmd.SetWarnings True