Solved: Create folders on network drive

[brandonb8]

Hello. I'm very new to macros and excel language in general. I've been trying to create a macro for a week now and am having no luck. It seems extremely complicated for me, but I initially wanted to refuse asking for help, instead trying to learn through research. I've found some good information, but am having trouble putting it all together. Here is my situation.

I need to create a macro for saving files to a mapped network drive. I work for the government inspecting asphalt and we need a way to save our core data. The drive we will use is J:\Asphalt Core Data. I made a macro that will save the file to a project subfolder and mix subfolder. For instance J:\Asphalt Core Data\J6I1541\SP125. The J6I1541 AND SP125 info are based on cell values where the inspector can put in the project and mix values. These cells are B6 AND B8. The real kicker is I want the macro to create these folders if they dont exist. From what I read I need to use MkDirMulti, but am having trouble with the language. I've got one macro to work called "transferJ". This macro will save the file, but if the folders dont exist I get an error message. Is there an easier way to do this. Any help would be appreciated.

[lucas]

I have changed the title of your thread to reflect the question. Helps with searches and will come closer to getting you the kind of help you need. Help!!!! doesn't tell us much.

[VBA] MkDir ActiveWorkbook.Path & MyDir1[/VBA]

[mvidas]

Brandon,

I made a function that checks if a directory exists, and if not then it creates it and returns the directory string as well. I'm not sure exactly how you're wanting to use it, but heres an example using your two cells:[vba]Sub brandonb8()
ActiveWorkbook.SaveAs CheckMakeUNCPath("J:\Asphalt Core Data\J6I1541\" & _
Range("B6").Text) & "filename.xls"
ActiveWorkbook.SaveAs CheckMakeUNCPath("J:\Asphalt Core Data\J6I1541\" & _
Range("B8").Text) & "filename.xls"
End Sub

Function CheckMakeUNCPath(ByVal vPath As String) As String
Dim PathSep As Long, oPS As Long
If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
PathSep = InStr(3, vPath, "\") 'position of drive separator in path
If PathSep = 0 Then Exit Function 'invalid path
Do
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\") 'position of directory
If PathSep = 0 Then Exit Do
If Len(Dir(Left(vPath, PathSep), vbDirectory)) = 0 Then Exit Do 'check path
Loop
Do Until PathSep = 0
MkDir Left(vPath, PathSep)
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\")
Loop
CheckMakeUNCPath = vPath
End Function[/vba]Note that it will create all of the path that does not exist, like:[vba]Sub brandonb8_2()
MsgBox CheckMakeUNCPath("C:\Hi\Brandon\Welcome to\VBAExpress")
End Sub[/vba]Matt

[brandonb8]

Thanks man. I'm gonna try it to see if it works in my application. Thanks for taking the time to help.

Brandon

[brandonb8]

Brandon,

I made a function that checks if a directory exists, and if not then it creates it and returns the directory string as well. I'm not sure exactly how you're wanting to use it, but heres an example using your two cells:[vba]Sub brandonb8()
ActiveWorkbook.SaveAs CheckMakeUNCPath("J:\Asphalt Core Data\J6I1541\" & _
Range("B6").Text) & "filename.xls"
ActiveWorkbook.SaveAs CheckMakeUNCPath("J:\Asphalt Core Data\J6I1541\" & _
Range("B8").Text) & "filename.xls"
End Sub

Function CheckMakeUNCPath(ByVal vPath As String) As String
Dim PathSep As Long, oPS As Long
If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
PathSep = InStr(3, vPath, "\") 'position of drive separator in path
If PathSep = 0 Then Exit Function 'invalid path
Do
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\") 'position of directory
If PathSep = 0 Then Exit Do
If Len(Dir(Left(vPath, PathSep), vbDirectory)) = 0 Then Exit Do 'check path
Loop
Do Until PathSep = 0
MkDir Left(vPath, PathSep)
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\")
Loop
CheckMakeUNCPath = vPath
End Function[/vba]Note that it will create all of the path that does not exist, like:[vba]Sub brandonb8_2()
MsgBox CheckMakeUNCPath("C:\Hi\Brandon\Welcome to\VBAExpress")
End Sub[/vba]Matt

Matt,

Thanks again for the help. This sort of worked. It created the folder I needed based on "B6" but I need it to string the folder to another sub folder "B8".

For instance I want to have the file named as the contents of cell B4. It will be saved to the J:\Asphalt Core Data folder in a sub folder (name contained in B6. It will then create another subfolder B8. The file will be saved in B8 as the info in B4.

Ex. B4 = Barke
B6 = J6D0600
B8 = SP190 03-37

Ending path = J:\Asphalt Core Data\J6D0600\SP190 03-37
filename = Barke

I want to be able to change the folder name based on B6 and B8 and save as B4 so that if

B4 = Huskey
B6 = J6P1725
B8 = SP125 06-32

Ending path = J:\Asphalt Core Data\J6P1725\SP125 06-32
filename = Huskey

Am I making any sense. It is probably pretty easy to do, but I am extremely new at this. Thanks again for taking the time to help with my situation.

Brandon

[mvidas]

No problem, Brandon. I'm guessing you had it mostly right, just maybe the parenthesis or "\" mixed it up. If B6 has the subfolder of Asphalt Core Data and B8 has the subfolder of the B6 folder, you can use[vba] ActiveWorkbook.SaveAs CheckMakeUNCPath("J:\Asphalt Core Data\" & _
Range("B6").Text & "\" & Range("B8").Text) & Range("B6").Text & ".xls"[/vba]
I did originally write this for UNC paths, like \\server\folder1\folder2\etc\. If you're sure that everyone using this has the same J drive as you do, you can use J, but you may want to use the network path just in case. If your J drive is mapped on another computer as K, it will be saved to the wrong place (assuming that user has a J drive, if not then it will error out)

[mvidas]

As one last thing, if you're not sure what the UNC path is for your J drive, open up My Computer. You'll see something like
"foldername on 'ServerName' (J: )"
or
"foldername on 'ServerName\foldername\foldername\etc' (J: )"

Basically just put \\ then whatever you see between the ' and ' after "on", then \ again, then the foldername. So my first example would have a UNC path of
\\ServerName\foldername\

and the second would have a UNC path of
\\ServerName\foldername\foldername\etc\foldername\

[brandonb8]

Matt,

That worked perfectly. It created the subfolders just how I wanted it to. The only thing it didn't do was save it as the user entering the data based on the B4 cell. It saved it as filename J6D0600. I was wanting to save it as the user Barke contained in cell B4. You were right by the way I was missing a slash from fixing it myself. Thanks again and if you can create the save file as contents of B4 you will be my VBA hero. Thanks again.

Brandon

[mvidas]

That looks to be a typo on my part.. in the code I most recently gave you there are two references to Range("B6"). Change the second one (the one followed by & ".xls") to B4 and you'll be all set. Sorry about that
Matt

[brandonb8]

Worked perfectly. Thanks again. Admin please mark this solved.

Brandon

[mvidas]

Glad to help! And I was going to mark it solved for you, but as this is your first thread here I thought I'd show you how to do it yourself. At the top of the thread you'll see a box for "Thread Tools", and if you click that you'll see "Mark Thread Solved".
Let us know if you need anything else!
Matt

[brandonb8]

Matt or anyone else that can help,


My workbook has come along nicely and I just recently added a password to modify the sheet. Using the formulas from the previous posts, I created a

1) Save to Local button
- This saves to the computers hard drive in the order of
- D:/Asphalt Core Data/Job Number/ Mix Type/ User.xls

2) Transfer to J Drive
- this saves to the computers network drive
- J:/Asphalt Core Data/Job Number/ Mix Type/ User.xls

Everything worked well until i added the password to modify in the save as general options. I saved a blank and then handed it out to our employees. Now it will ask for the password when you first load it and it will allow you to add data. When I go to save it though, it will give me the 1004 error that the sheet is write reserved. Do I need to put a password prompt in my two macro buttons to prompt for password right before saving to get rid of this error. It is necessary to have it read only on the network drive so that others that have access to it cannot accidentally destroy the data. It is also necessary to be able to modify the data on the network drive so that is why I went the password route. Any help would be appreciated. I am stumped and stuck.

Thanks in advance.