perhol
05-06-2008, 05:03 AM
I am in the process of making sure that users save account-sheets in right folder and with a filename that identify the sheet correctly.
For that i have used code found in this thread
http://www.vbaexpress.com/forum/showthread.php?t=11572
and just slightly modifyed.
Here is the code in full:
Sub GemSom()
ActiveWorkbook.SaveAs CheckMakePath("G:\" & _
Sheets("Kasserapport").Range("H5").Text & "-huset" & "\" & "Beboere" & "\" & _
Sheets("Kasserapport").Range("D2").Text & "\" & "Regnskab" & "\" & _
Format(Sheets("Kasserapport").Range("A4"), "yyyy")) & _
"Regnskab " & Format(Sheets("Kasserapport").Range("A4"), "mm-yyyy") & _
" " & Sheets("Kasserapport").Range("D2").Text & ".xls"
End Sub
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, "\") 'Position af drev-seperatoren i stien
If PathSep = 0 Then Exit Function 'Ugyldig sti
Do
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\") 'Position af folder
If PathSep = 0 Then Exit Do
If Len(Dir(Left(vPath, PathSep), vbDirectory)) = 0 Then Exit Do 'check stien
Loop
Do Until PathSep = 0
MkDir Left(vPath, PathSep)
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\")
Loop
CheckMakePath = vPath
End Function
The code works as expected, except that i need to lookup part of the path and filename, and i need to make sure that at least 2 cells is populatet.
The account-sheet is for occupants in 3 houses Named A-huset, B-huset and C-huset, each with 8 occupants.
The name of the house must bee in the path (e.g. G:\A-huset\Beboere\Lena Frederiksen . . . . . )
The name is taken from a list in column I rows 5 to 28 and filled in cell D2. In column H is the A-, B- or C-part of the house name. That should be looked up and used in the Range("H5").Text & "-huset" instead of "H5" in the above code.
Also, how do i make sure that cells D2 and A4 is populated before save?
For that i have used code found in this thread
http://www.vbaexpress.com/forum/showthread.php?t=11572
and just slightly modifyed.
Here is the code in full:
Sub GemSom()
ActiveWorkbook.SaveAs CheckMakePath("G:\" & _
Sheets("Kasserapport").Range("H5").Text & "-huset" & "\" & "Beboere" & "\" & _
Sheets("Kasserapport").Range("D2").Text & "\" & "Regnskab" & "\" & _
Format(Sheets("Kasserapport").Range("A4"), "yyyy")) & _
"Regnskab " & Format(Sheets("Kasserapport").Range("A4"), "mm-yyyy") & _
" " & Sheets("Kasserapport").Range("D2").Text & ".xls"
End Sub
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, "\") 'Position af drev-seperatoren i stien
If PathSep = 0 Then Exit Function 'Ugyldig sti
Do
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\") 'Position af folder
If PathSep = 0 Then Exit Do
If Len(Dir(Left(vPath, PathSep), vbDirectory)) = 0 Then Exit Do 'check stien
Loop
Do Until PathSep = 0
MkDir Left(vPath, PathSep)
oPS = PathSep
PathSep = InStr(oPS + 1, vPath, "\")
Loop
CheckMakePath = vPath
End Function
The code works as expected, except that i need to lookup part of the path and filename, and i need to make sure that at least 2 cells is populatet.
The account-sheet is for occupants in 3 houses Named A-huset, B-huset and C-huset, each with 8 occupants.
The name of the house must bee in the path (e.g. G:\A-huset\Beboere\Lena Frederiksen . . . . . )
The name is taken from a list in column I rows 5 to 28 and filled in cell D2. In column H is the A-, B- or C-part of the house name. That should be looked up and used in the Range("H5").Text & "-huset" instead of "H5" in the above code.
Also, how do i make sure that cells D2 and A4 is populated before save?