Create Folder using field data (1 Viewer)

chuckcoleman

Registered User.
Local time
Yesterday, 20:48
Joined
Aug 20, 2010
Messages
243
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

Wino Moderator
Staff member
Local time
Yesterday, 18:48
Joined
Aug 30, 2003
Messages
35,029
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

Registered User.
Local time
Yesterday, 20:48
Joined
Aug 20, 2010
Messages
243
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

Lifelong Learner
Local time
Yesterday, 18:48
Joined
Mar 14, 2017
Messages
4,459
Please indent code to make it readable, then use code tags when posting to make it readable in the post
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 02:48
Joined
Sep 12, 2006
Messages
14,419
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.
 

Abo Abd_Allah

New member
Local time
Today, 04:48
Joined
Sep 23, 2016
Messages
16
you can write code with few line as this:
Code:
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

Registered User.
Local time
Yesterday, 20:48
Joined
Aug 20, 2010
Messages
243
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

Registered User.
Local time
Yesterday, 20:48
Joined
Aug 20, 2010
Messages
243
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

Registered User.
Local time
Yesterday, 20:48
Joined
Aug 20, 2010
Messages
243
you can write code with few line as this:
Code:
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
 

Users who are viewing this thread

Top Bottom