Solved: Make folder path and file name with VBA code
I work at an institution and has for several years used a VBA code to ensure that residents' accounts were stored in the correct folder, and with the right name.
The code checks if the folder path exists using a function (CheckMakePath). If not, the function will create it.
Here is the code of the function:
[vba]Function CheckMakePath(ByVal vPath As String) As String
Dim PathSep As Long, oPS As Long
If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
PathSep = InStr(3, vPath, "\")
If PathSep = 0 Then Exit Function
Do
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\")
If PathSep = 0 Then Exit Do
If Len(Dir(Left(vPath, PathSep), vbDirectory)) = 0 Then Exit Do
Loop
Do Until PathSep = 0
MkDir Left(vPath, PathSep)
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\")
Loop
CheckMakePath = vPath
End Function
[/vba] Folder and file name is given partly by text and partly by reference to cells in the worksheet.
Cell I4 contains a letter for the house - Cell E2 contains the resident's full name - Cell A4 contains a start date of the accounts.
The accounts are stored in a folder that will be called S:\Institution\A huset\Beboere\Beboer navn\Regnskab\2013 with the filename Regnskab 03-2013 Beboer navn.xls
This is the code I have used and it works (the emphasized is the folder path, what's in normal font is the file name).
With Sheets("Kassebog")
ActiveWorkbook.SaveAs CheckMakePath("S:\Institution\" & Sheets("Kassebog").Range("I4").Text & "-huset" & "\" & "Beboere" _
& "\" & Sheets("Kassebog").Range("E2").Text & "\" & "Regnskab" & "\" & Format(Sheets("Kassebog").Range("A4"), "yyyy")) _
& "Regnskab " & Format(Sheets("Kassebog").Range("A4"), "mm-yyyy") & " " & Sheets("Kassebog").Range("E2").Text & ".xls"
End With
Now I shall export the accounting file to another institution. They want a different folder structure to residents' accounts, and suddenly the code is not working as expected!
They want the accounts saved in a folder that for example can be called S: \Institution\Hus B\2013\Beboer navn\ with the file
name Regnskab 03-2013 Beboer navn.Xls
Instead, the folder path become S:\Institution\Hus B\2013 and filename become Beboer navnRegnskab 03-2013 Beboer navn.Xls
[vba] With Sheets("Kassebog")
ActiveWorkbook.SaveAs CheckMakePath("S:Institution\Beboerøkonomi\" & "Hus " & Sheets("Kassebog").Range("I4").Text & "\" _
& Format(Sheets("Kassebog").Range("A4"), "yyyy")) & "\"& Sheets("Kassebog").Range("E2").Text _
& "Regnskab " & Format(Sheets("Kassebog").Range("A4"), "mm-yyyy") & " " & Sheets("Kassebog").Range("E2").Text & ".xls"
End With[/vba]
Well, I thought, so I must of course add backslash like this & "\".
The same folder path as before are created and an error procedure informs me that the file has not been saved!
[vba]With Sheets("Kassebog")
ActiveWorkbook.SaveAs CheckMakePath("S:\Institution\Beboerøkonomi\" & "Hus " & Sheets("Kassebog").Range("I4").Text & "\" _
& Format(Sheets("Kassebog").Range("A4"), "yyyy")) & "\"& Sheets("Kassebog").Range("E2").Text & "\" _
& "Regnskab " & Format(Sheets("Kassebog").Range("A4"), "mm-yyyy") & " " & Sheets("Kassebog").Range("E2").Text & ".xls"
End With[/vba]
It may be simple, but I can not really capture what just happened here.
Is there anyone who can help?
