Need cbox selction to be used to mkdir

[Punice]

I have a cbox (cboBizYear) bound to 'BizYear' on an Access 2007 form. I want the value selected (eg., 'nn_R11_TY-yy) to be used to be the name of the folder to be created, with 'Marketing' as a sub-folder. Here is my code:

Private Sub cboBizYear_Click()
Dim strFolder_Path As String
Dim BizYear As String
Dim strWhere As String

'Captures the user selected 'BizYear'
strWhere = "[BizYear] =" & Chr(34) & Me.[cboBizYear] & Chr(34)
Debug.Print strWhere 'Displays: '[BizYear] ="12_R11_TY-18" in the IA Window, for this example selection.

'Creates path for folder & sub-folder to hold files
strFolder_Path = "C:\strWhere" & "\" & "Marketing\"
Debug.Print strFolder_Path 'Displays: 'C:\strWhere\Marketing\'; NOT 'C:\12_R11_TY-18\Marketing\

'Check for existing directory
If Dir(strFolder_Path, vbDirectory) = "" Then
MsgBox ("Ok to create folder!"), vbOKCancel = vbOK
MkDir strFolder_Path
Else
MsgBox "The folder already exists.", vbOKOnly
End If
End Sub

 

[MarkK]

You have not described the problem with the code. Presumably it fails somehow? How it fails matters in troubleshooting it.

 

[MarkK]

Oh, my bad, there it is in red.

strWhere is a variable. It CONTAINS a value, but you are using its NAME. Consider this amendment . . .

strFolder_Path = "C:\" & strWhere & "\Marketing\"

Do you see how that differs, and why the result changes too?

 

[Punice]

TU MarkK. Now, this crops up: "Bad File Name or Number" at the red line:
Private Sub cboBizYear_Click()
Dim strFolder_Path As String
Dim BizYear As String
Dim strWhere As String

'Captures the user selected 'BizYear'
strWhere = "[BizYear] =" & Chr(34) & Me.[cboBizYear] & Chr(34)

'Creates path for folder & sub-folder to hold files
strFolder_Path = "C:\" & strWhere & "\Marketing\"

'Check for existing directory
If Dir(strFolder_Path, vbDirectory) = "" Then
MsgBox ("Ok to create folder!"), vbOKCancel = vbOK
MkDir strFolder_Path

Else
MsgBox "The folder already exists.", vbOKOnly
End If
End Sub

>>Also, where do you suggest I go to learn more about this sort of thing?

 

[MarkK]

Check the value of strFolder_Path when you pass it as a parameter to Dir(). This . . .

C:\[BizYear] ="12_R11_TY-18"\Marketing\

. . . is probably not a valid folder name.

 

[Punice]

MkDir still doesn't create this folder "12_R11_TY-18" & a sub-folder "Marketing".
I changed the cbox to a text box. Still get 'error 75'. I apologize for being so ignorant and not understand why it doesn't do what I wish it would. I've done a MkDir op previously in this same db, copied that code & modified it to 'fit' this application. That didn't work. That's why I'm back asking for help. Here is my simplified code:

Private Sub BizYear_Click()
Dim strFolder_Path As String
Dim BizYear As String
Dim strWhere As String

'Captures the user entered 'BizYear'
strWhere = Me.BizYear
Debug.Print strWhere YIELDS: "12_R11_TY-18", WHICH IS OK

'Creates path for folder & sub-folder to hold files
strFolder_Path = "C:\" & strWhere & "\Marketing\"
Debug.Print strFolder_Path YIELDS: "C:\12_R11_TY-18\Marketing\, also OK

'Check for existing directory
If Dir(strFolder_Path, vbDirectory) = "" Then
MsgBox ("Ok to create folder!"), vbOKCancel = vbOK
MkDir strFolder_Path
Else
MsgBox "The folder already exists.", vbOKOnly
End If
End Sub

 

[MarkK]

MkDir() may only make one folder at a time. You are asking it to make a folder AND a subfolder in one command. I bet you need to run MkDir() twice, once for the parent folder, once for the sub-folder.

 

[Punice]

Actually, you can make a directory and a sub-directory in Access 2007. The following code does it within the same db that won't do it, now. It creates a folder named 'R11Bidder14' and a sub-folder containing the customer's last name (ie., LName) obtained from a text box field on the same for as the make directory button.

Private Sub CreateSOW_Click()
Dim Folder_Path As String
Dim Folder_PathNew As String

'Create path for customer folder & files
strFolder_Path = "C:\R11Bidder14\" & (Me.[L_Name])
strFolder_PathNew = strFolder_Path & "\" & Me.[L_Name] & " SOW" & ".doc"
'Check for existing directory with the current form's customer name.
If Dir(strFolder_Path, vbDirectory) = "" Then
MsgBox ("Ok to creat folder!"), vbOKCancel = vbOK
MkDir strFolder_Path
'The name entered into the 'L_Name' window of the "Customers" form.
Else
MsgBox "The folder already exists.", vbOKOnly
End If

If Len(Dir(strFolder_PathNew)) = 0 Then
'Copy "SOW.doc" to this folder 'L_Name' and rename it to 'L_Name SOW.doc'.
FileCopy "C:\R11Bidder14\SOW.doc", strFolder_PathNew
Response = MsgBox(Me.[L_Name] & " SOW", vbOKOnly)
End If
'Opens file: "L_Name SOW.doc"
Application.FollowHyperlink strFolder_PathNew

End Sub

 

[jdraw]

I agree with Markk. Try creating each level separately.

see makedir()

Acc2010 won't let me do this in 1 step

Sub testmkdir()
   On Error GoTo testmkdir_Error

10    Dim x As String: x = "zzz\zzx"
20    MkDir "C:\users\Mellon\" & x      'existing folder

   On Error GoTo 0
   Exit Sub

testmkdir_Error:

    MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure testmkdir of Module ModuleTesting_CanKill"
End Sub

But does work in 2 steps.

 

[Punice]

OK, I took Markk's advice and created two directories, one at a time. Of course, it worked. Thanks to all for your help getting past this 'onion skin'. Now, I wonder what the next 'wall' will be. Never-the-less, I'm learning a lot trying on my own and seeking help from you experts when I bang my head against the 'wall' one time too many.

 

[Samantha]

Good Morning,

I am working on this same concept and am stuck. I am building a string from my underlying form and want to create a folder with it using a button. This post has been the most useful.

Private Sub cmdMakeFolder_Click()
Dim strFolderPath As String, strNewFolder As String, strCoName As String

If IsNull([tblLookupCompany.Nickname]) Then
    strCoName = [Company]
End If
    
strFolderPath = Left([JobNumber], 3)
strNewFolder = ([JobNumber] & " " & strCoName & " - " & [ServiceAddress] & ", " & [ProjectDescription])

MkDir ("O:\ECBSProposals\" & strFolderPath & strNewFolder)

End Sub

First - I know that I need to resolve checking to see if my first folder exists and I understand that concept and am working to incorporate that.

My issue lies with making the first variable change if that makes sense?
I want the folder to read O:\ECBSProposals\112\11201 Company - 111 address, Description
As the code runs it reads O:\ECBSProposals\11211201 Company - 111 address, Description
strFolderPath will change as time goes and I have played with constructing it, and I just cannot figure out that part.

Any help is greatly appreciated!

 

[Punice]

I'm no where any kind of programmer, but with a solution to my request for help, I used this code:

Private Sub MakeBidder_Click()
Dim appExcel As Excel.Application
Dim lngLastDataRow As Long
Dim Folder_Path As String
Dim strFolder_PathNew As String

'Create path for customer folder & files
strFolder_Path = "C:\10_R11_TY-16\Marketing\" & (Me.[L_Name])
strFolder_PathNew = strFolder_Path & "\" & Me.[L_Name] & " Roof" & ".xlsx"
'Check for existing directory with the current form's customer name.
If Dir(strFolder_Path, vbDirectory) = "" Then
MsgBox ("Ok to create folder!"), vbOKCancel = vbOK
MkDir strFolder_Path
'The name entered into the 'L_Name' window of the "Customers" form.
Else
MsgBox "The folder already exists.", vbOKOnly
Exit Sub
End If

If Len(Dir(strFolder_PathNew)) = 0 Then
'Copy "Bidder.xlsx" to this folder 'L_Name' and renames it to 'L_Name Roof.xlsx'.
FileCopy "C:\10_R11_TY-16\Bidder.xlsx", strFolder_PathNew
Response = MsgBox(Me.[L_Name] & " Roof", vbOKOnly)

Set appExcel = CreateObject("Excel.Application")
With appExcel
.Visible = True
.UserControl = True

'Open the renamed 'Bidder.xlsx' file in the associated folder & enters the data in it, as programmed.
With .Workbooks.Open(strFolder_PathNew)
lngLastDataRow = .Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
.Worksheets("Sheet1").Range("I2") = Me![F_Name] & " " & Me.[L_Name]
.Worksheets("Sheet1").Range("I3") = Me.[Address]
.Worksheets("Sheet1").Range("I4") = Me.[City] & ", " & Me.[State] & " " & Me![ZipCode]
.Worksheets("Sheet1").Range("I7") = Me.[Phone_No]
.Worksheets("Sheet1").Range("I8") = Me.[Cell_No]
.Worksheets("Sheet1").Range("I9") = Me.[Work_No]
.Worksheets("Sheet1").Range("N2") = Me.[Quote_Date]
.Worksheets("Sheet1").Range("N4") = Me.[Cust_No]
.Worksheets("Sheet1").Range("N5") = "Q" & Me.[Cust_No]
End With
End With

appExcel.WindowState = xlMaximized
Set appExcel = Nothing

Else
MsgBox "The file has already exists. Use 'Edit Bidder' to make changes.", vbOKOnly
End If

Exit_cmbMkDir_Click:
Exit Sub

Err_cmbMkDir_Click:
MsgBox Err.Description
Resume Exit_cmbMkDir_Click
End Sub

 

[Gasman]

PMFJI, but could I ask, what happens if you answer Cancel to "Ok to create folder!"

 

[Punice]

If you pick "OK", in my sub-routine, you will create a directory named "L_Name" that is the name on my "Customer's" table. Next, an Excel file
(Bidder.xlsx) with Customer's name, address & telephone number. etc
automatically copied into the corresponding cells per the code in the rest of the sub-routine.

I know that my code isn't what you wanted, specifically. I suggest that you eliminate what you don't need to reach your objective or better, yet, use the other responses that I see regarding my and your request for help.

Good Luck.

 

[Samantha]

strFolder_PathNew = strFolder_Path & "\" & Me.[L_Name] & " Roof" & ".xlsx"

Thank you Punice!

"\" - this is what I was missing from the line. Here is the code I used in case it helps any one else.

Private Sub cmdMakeFolder_Click()
Dim strFolderPath As String, strNewFolder As String, CoName As String

If IsNull([tblLookupCompany.Nickname]) Then
    CoName = [Company]
End If

'Assign strings a value
strFolderPath = ("O:\ECBSProposals\" & Left([JobNumber], 3))
strNewFolder = ("O:\ECBSProposals\" & Left([JobNumber], 3) & "\" & [JobNumber] & " " & CoName & " - " & [ServiceAddress] & ", " & [ProjectDescription])

'Check for existing directory and make file(s)

If Dir(strFolderPath, vbDirectory) = "" Then
'MsgBox "Ok to create folder " & strFolderPath & "?", vbOKCancel = vbOK
MkDir strFolderPath
End If

If Dir(strNewFolder, vbDirectory) = "" Then
MkDir ("O:\ECBSProposals\" & Left([JobNumber], 3) & "\" & [JobNumber] & " " & CoName & " - " & [ServiceAddress] & ", " & [ProjectDescription])
MsgBox "The folder " & strNewFolder & " has been created.", vbOKOnly
Else
MsgBox "The folder " & strNewFolder & " already exists.", vbOKOnly
End If
    
End Sub

Now that I have this functioning it would be great to make the hyperlink field in my table fill in the new hyperlink automatically after the code is ran. I am open to suggestions?

 

[Punice]

I am glad that I was able to help you. I'm not able to help you with your next adventure in Access. I am less knowledgeable than you. Maybe post your requirement in a fresh thread to get the attention of the "knowledgeable Ones",
the MVPers. Good Luck: