Hi All & happy New Year! (I'm using Excel 2003)

I made a spreadsheet that looks like a form and basically is a specialized
calculator for the Mortgage industry. I want to protect it from noobs, so I
locked most cells except for input cells, and made it a read only file before
I sent it to them.

Is there a way I can make it even more user friendly by automating the save
process. Specifically make it possible to click a box that will look for a
"Borrower" folder in their MyDocuments and save the spreadsheet with the
borrowers last name (that is in a cell that they fill out) appended to the
original file name? Can it also make the folder in their MyDocuments folder
the first time used? (i.e. if it isn't already there). Is this possible or am
I on the yellow brick road here? lol

Thanks in advance for all replies!

How about a version with a minor difference.

Instead of appending the borrower's name to the filename, just store the file in
the borrower's folder and use the date and time for the filename (or
borrower's_name _date_time.xls, in case you have to put the files in a common
folder someday.

If you like that idea:

Option Explicit
Sub testme()

Dim myDocumentsPath As String
Dim wsh As Object
Dim myFileName As Variant
Dim BorrowsCell As Range
Dim testStr As String

'change the worksheet name and address here
Set BorrowsCell = ThisWorkbook.Worksheets("sheet1").Range("a1")

myDocumentsPath = CreateObject("WScript.Shell") _
.SpecialFolders.Item("mydocuments")

If Trim(BorrowsCell.Value) = "" Then
MsgBox "Please enter the borrow's name"
Exit Sub
End If

'just try to create the folder.
'Ignore the error if it already exists.
On Error Resume Next
MkDir myDocumentsPath & "\" & BorrowsCell.Value
On Error GoTo 0

'the folder should exist now, but check
testStr = ""
On Error Resume Next
testStr = Dir(myDocumentsPath & "\" & BorrowsCell.Value & "\nul")
On Error GoTo 0

If testStr = "" Then
MsgBox "Invalid name in the borrows name cell: " _
& BorrowsCell.Address(0, 0) & vbLf & BorrowsCell.Value
Exit Sub
End If

myFileName = myDocumentsPath & "\" & BorrowsCell.Value _
& "\" & BorrowsCell.Value _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error - FILE NOT SAVED" _
& vbLf & Err.Number & " - " & Err.Description
Err.Clear
Else
MsgBox "File saved to:" & vbLf & myFileName
End If
On Error GoTo 0

End Sub

John Galt wrote:

Hi All & happy New Year! (I'm using Excel 2003)

I made a spreadsheet that looks like a form and basically is a specialized
calculator for the Mortgage industry. I want to protect it from noobs, so I
locked most cells except for input cells, and made it a read only file before
I sent it to them.

Is there a way I can make it even more user friendly by automating the save
process. Specifically make it possible to click a box that will look for a
"Borrower" folder in their MyDocuments and save the spreadsheet with the
borrowers last name (that is in a cell that they fill out) appended to the
original file name? Can it also make the folder in their MyDocuments folder
the first time used? (i.e. if it isn't already there). Is this possible or am
I on the yellow brick road here? lol

Thanks in advance for all replies!

--

Dave Peterson

Thank you Dave,
Sorry for delay in response. I'm sure this answers my question. This is a
bit above my current skills, but evidently this means there is at least one
way to accomplish my goal. I appreciate your time taken to think that out for
me. I will print that and see if I can find a book or articles that will help
me to learn enough to pull it off! :)

Warm Regards, Scott

"Dave Peterson" wrote:

How about a version with a minor difference.

Instead of appending the borrower's name to the filename, just store the file in
the borrower's folder and use the date and time for the filename (or
borrower's_name _date_time.xls, in case you have to put the files in a common
folder someday.

If you like that idea:

Option Explicit
Sub testme()

Dim myDocumentsPath As String
Dim wsh As Object
Dim myFileName As Variant
Dim BorrowsCell As Range
Dim testStr As String

'change the worksheet name and address here
Set BorrowsCell = ThisWorkbook.Worksheets("sheet1").Range("a1")

myDocumentsPath = CreateObject("WScript.Shell") _
.SpecialFolders.Item("mydocuments")

If Trim(BorrowsCell.Value) = "" Then
MsgBox "Please enter the borrow's name"
Exit Sub
End If

'just try to create the folder.
'Ignore the error if it already exists.
On Error Resume Next
MkDir myDocumentsPath & "\" & BorrowsCell.Value
On Error GoTo 0

'the folder should exist now, but check
testStr = ""
On Error Resume Next
testStr = Dir(myDocumentsPath & "\" & BorrowsCell.Value & "\nul")
On Error GoTo 0

If testStr = "" Then
MsgBox "Invalid name in the borrows name cell: " _
& BorrowsCell.Address(0, 0) & vbLf & BorrowsCell.Value
Exit Sub
End If

myFileName = myDocumentsPath & "\" & BorrowsCell.Value _
& "\" & BorrowsCell.Value _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error - FILE NOT SAVED" _
& vbLf & Err.Number & " - " & Err.Description
Err.Clear
Else
MsgBox "File saved to:" & vbLf & myFileName
End If
On Error GoTo 0

End Sub

John Galt wrote:

Hi All & happy New Year! (I'm using Excel 2003)

I made a spreadsheet that looks like a form and basically is a specialized
calculator for the Mortgage industry. I want to protect it from noobs, so I
locked most cells except for input cells, and made it a read only file before
I sent it to them.

Is there a way I can make it even more user friendly by automating the save
process. Specifically make it possible to click a box that will look for a
"Borrower" folder in their MyDocuments and save the spreadsheet with the
borrowers last name (that is in a cell that they fill out) appended to the
original file name? Can it also make the folder in their MyDocuments folder
the first time used? (i.e. if it isn't already there). Is this possible or am
I on the yellow brick road here? lol

Thanks in advance for all replies!

--

Dave Peterson