Saving in Excel

G

Guest

Is there a way that when saving a excel file that i can get a name from a
cell or maybe two?

so if i had an invoice excel sheet that when you go to save it would take
the customers name and the invoice number and make that the file name.
 
G

Guest

try this

Sub SaveWorkbookAs()
Dim CustomerName As String
Dim InvoiceNumber As String
'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value
Select Case MsgBox("Would you like to save this invoice", vbYesNo)

Case Is = vbYes
Application.ActiveWorkbook.SaveAs CustomerName & _
"_" & InvoiceNumber

Case Is = vbNo
Exit Sub
End Select
End Sub
 
G

Guest

Mike,

thanks for the help, the code works great.
can i get you to help me on one more thing?
Can i make it so that it will save in the dir that i want? it can be the
same one every time on a network drive.
 
G

Guest

try this

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox "The filename you have entered is not a valid
filename." _
& vbCrLf & "Filenames may not have any of these
characters in them:" _
& vbCrLf & " \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", vbOKOnly,
"Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function
 
G

Guest

I am trying to accomplish the same thing. When I run this I get an error
message. can you verify that the code is error free? Thanks

Steve


Mike said:
try this

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox "The filename you have entered is not a valid
filename." _
& vbCrLf & "Filenames may not have any of these
characters in them:" _
& vbCrLf & " \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", vbOKOnly,
"Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function


todd said:
Mike,

thanks for the help, the code works great.
can i get you to help me on one more thing?
Can i make it so that it will save in the dir that i want? it can be the
same one every time on a network drive.
 
G

Guest

This is the section I am getting a syntax error

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder ")"


Thanks

Mike said:
try this

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox "The filename you have entered is not a valid
filename." _
& vbCrLf & "Filenames may not have any of these
characters in them:" _
& vbCrLf & " \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", vbOKOnly,
"Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function


todd said:
Mike,

thanks for the help, the code works great.
can i get you to help me on one more thing?
Can i make it so that it will save in the dir that i want? it can be the
same one every time on a network drive.
 
G

Guest

What error are getting and could you post your code to look at

S Willingham said:
I am trying to accomplish the same thing. When I run this I get an error
message. can you verify that the code is error free? Thanks

Steve


Mike said:
try this

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox "The filename you have entered is not a valid
filename." _
& vbCrLf & "Filenames may not have any of these
characters in them:" _
& vbCrLf & " \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", vbOKOnly,
"Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function


todd said:
Mike,

thanks for the help, the code works great.
can i get you to help me on one more thing?
Can i make it so that it will save in the dir that i want? it can be the
same one every time on a network drive.

:

try this

Sub SaveWorkbookAs()
Dim CustomerName As String
Dim InvoiceNumber As String
'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value
Select Case MsgBox("Would you like to save this invoice", vbYesNo)

Case Is = vbYes
Application.ActiveWorkbook.SaveAs CustomerName & _
"_" & InvoiceNumber

Case Is = vbNo
Exit Sub
End Select
End Sub

:

Is there a way that when saving a excel file that i can get a name from a
cell or maybe two?

so if i had an invoice excel sheet that when you go to save it would take
the customers name and the invoice number and make that the file name.
 
G

Guest

mayby this will work


Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox _
("A file named: '" & anyFilename & " already exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To Folder ")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox _
"The filename you have entered is not a valid filename." _
& vbCrLf & _
"Filenames may not have any of these characters in them:" _
& vbCrLf & _
" \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", _
vbOKOnly, "Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function

S Willingham said:
I am trying to accomplish the same thing. When I run this I get an error
message. can you verify that the code is error free? Thanks

Steve


Mike said:
try this

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox "The filename you have entered is not a valid
filename." _
& vbCrLf & "Filenames may not have any of these
characters in them:" _
& vbCrLf & " \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", vbOKOnly,
"Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function


todd said:
Mike,

thanks for the help, the code works great.
can i get you to help me on one more thing?
Can i make it so that it will save in the dir that i want? it can be the
same one every time on a network drive.

:

try this

Sub SaveWorkbookAs()
Dim CustomerName As String
Dim InvoiceNumber As String
'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value
Select Case MsgBox("Would you like to save this invoice", vbYesNo)

Case Is = vbYes
Application.ActiveWorkbook.SaveAs CustomerName & _
"_" & InvoiceNumber

Case Is = vbNo
Exit Sub
End Select
End Sub

:

Is there a way that when saving a excel file that i can get a name from a
cell or maybe two?

so if i had an invoice excel sheet that when you go to save it would take
the customers name and the invoice number and make that the file name.
 
G

Guest

Now I am getting an error on this line

If Dir(SaveToPath & anyFilename) = "" Then


Thanks
S Willingham said:
This is the section I am getting a syntax error

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder ")"


Thanks

Mike said:
try this

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox "The filename you have entered is not a valid
filename." _
& vbCrLf & "Filenames may not have any of these
characters in them:" _
& vbCrLf & " \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", vbOKOnly,
"Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function


todd said:
Mike,

thanks for the help, the code works great.
can i get you to help me on one more thing?
Can i make it so that it will save in the dir that i want? it can be the
same one every time on a network drive.

:

try this

Sub SaveWorkbookAs()
Dim CustomerName As String
Dim InvoiceNumber As String
'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value
Select Case MsgBox("Would you like to save this invoice", vbYesNo)

Case Is = vbYes
Application.ActiveWorkbook.SaveAs CustomerName & _
"_" & InvoiceNumber

Case Is = vbNo
Exit Sub
End Select
End Sub

:

Is there a way that when saving a excel file that i can get a name from a
cell or maybe two?

so if i had an invoice excel sheet that when you go to save it would take
the customers name and the invoice number and make that the file name.
 
G

Guest

Post the whole code that you are using

S Willingham said:
Now I am getting an error on this line

If Dir(SaveToPath & anyFilename) = "" Then


Thanks
S Willingham said:
This is the section I am getting a syntax error

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder ")"


Thanks

Mike said:
try this

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\Sever01\users\Invoices\SavedReports\"

anyFilename = CustomerName & _
"_" & InvoiceNumber & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox("A file named: '" & anyFilename & " already
exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To
Folder")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox "The filename you have entered is not a valid
filename." _
& vbCrLf & "Filenames may not have any of these
characters in them:" _
& vbCrLf & " \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", vbOKOnly,
"Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If



End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function


:

Mike,

thanks for the help, the code works great.
can i get you to help me on one more thing?
Can i make it so that it will save in the dir that i want? it can be the
same one every time on a network drive.

:

try this

Sub SaveWorkbookAs()
Dim CustomerName As String
Dim InvoiceNumber As String
'Change A1 to the cell your CustomerName is in
'Change B1 to the cell your InvoiceNumber is in
CustomerName = Range("A1").Value
InvoiceNumber = Range("B1").Value
Select Case MsgBox("Would you like to save this invoice", vbYesNo)

Case Is = vbYes
Application.ActiveWorkbook.SaveAs CustomerName & _
"_" & InvoiceNumber

Case Is = vbNo
Exit Sub
End Select
End Sub

:

Is there a way that when saving a excel file that i can get a name from a
cell or maybe two?

so if i had an invoice excel sheet that when you go to save it would take
the customers name and the invoice number and make that the file name.
 
G

Guest

This is the code I'm using

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your QuoteName is in
'Change B1 to the cell your CustomerName is in
QuoteName = Range("E8").Value
CustomerName = Range("B6").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\C:\Documents and Settings\FaroTemplate\Desktop\Quotes\"

anyFilename = QuoteName & _
"_" & CustomerName & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox _
("A file named: '" & anyFilename & " already exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To Folder ")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox _
"The filename you have entered is not a valid filename." _
& vbCrLf & _
"Filenames may not have any of these characters in them:" _
& vbCrLf & _
" \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", _
vbOKOnly, "Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If

End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function



I pasted yours and made on modification. I changed the Labels to QuoteName
and CustomerName. Other than that it is just as you wrote it.

Thanks in advance
 
G

Guest

I figured it out. I had a syntax error in my file path.

On a related note. Is there a way that it can save to a folder named as the
CustomerName.

In other words. I have a folder called Quotes. Within this I have sub
-folders named after each customer where there quotes are saved. Ideally I
would like to save each customers quote in the proper folder and in the event
the customer had no folder yet it would simply save to the main quote folder.

Is this possible.

Thanks

Steve

This is the code I'm using

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your QuoteName is in
'Change B1 to the cell your CustomerName is in
QuoteName = Range("E8").Value
CustomerName = Range("B6").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\C:\Documents and Settings\FaroTemplate\Desktop\Quotes\"

anyFilename = QuoteName & _
"_" & CustomerName & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox _
("A file named: '" & anyFilename & " already exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To Folder ")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox _
"The filename you have entered is not a valid filename." _
& vbCrLf & _
"Filenames may not have any of these characters in them:" _
& vbCrLf & _
" \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", _
vbOKOnly, "Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If

End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function



I pasted yours and made on modification. I changed the Labels to QuoteName
and CustomerName. Other than that it is just as you wrote it.

Thanks in advance
 
G

Guest

try this
Shouild work as long as the Folder is named the same as Customers Name
SaveToPath = "\\C:\Documents and Settings\FaroTemplate\Desktop\Quotes\" & _
CustomerName & "\"

S Willingham said:
I figured it out. I had a syntax error in my file path.

On a related note. Is there a way that it can save to a folder named as the
CustomerName.

In other words. I have a folder called Quotes. Within this I have sub
-folders named after each customer where there quotes are saved. Ideally I
would like to save each customers quote in the proper folder and in the event
the customer had no folder yet it would simply save to the main quote folder.

Is this possible.

Thanks

Steve

This is the code I'm using

Sub SaveWorkbookToFolder()
Dim CustomerName As String
Dim InvoiceNumber As String
Dim SaveToPath As String
Dim userInput As String
Dim anyFilename As String

'Change A1 to the cell your QuoteName is in
'Change B1 to the cell your CustomerName is in
QuoteName = Range("E8").Value
CustomerName = Range("B6").Value

'Change to the folder path need to be sure you have a \ at the end of path
SaveToPath = "\\C:\Documents and Settings\FaroTemplate\Desktop\Quotes\"

anyFilename = QuoteName & _
"_" & CustomerName & ".xls"

If Dir(SaveToPath & anyFilename) = "" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Else

Select Case MsgBox _
("A file named: '" & anyFilename & " already exists in " & SaveToPath _
& vbCrLf & "What would you like to do?" & vbCrLf _
& "Overwrite the existing file? [Yes]" & vbCrLf _
& "Save file with a different name? [No]" & vbCrLf _
& "Cancel - do not save this file at this time. [Cancel]", _
vbYesNoCancel + vbExclamation + vbDefaultButton2, "Save Invoice To Folder ")

Case Is = vbYes

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
Application.DisplayAlerts = True

Case Is = vbNo


userInput = "dummy entry to make it work"

GetFileNameFromUser:
Do While userInput <> ""
anyFilename = InputBox$("Enter a new filename to use:", _
"Commission Manager", CustomerName & _
"_" & InvoiceNumber)
If Right(UCase(Trim(anyFilename)), 4) <> ".XLS" Then
anyFilename = anyFilename & ".xls"
End If
If ValidateFilename(anyFilename) <> "" Then

MsgBox _
"The filename you have entered is not a valid filename." _
& vbCrLf & _
"Filenames may not have any of these characters in them:" _
& vbCrLf & _
" \ / : * ? < > | " & Chr$(34) & vbCrLf _
& "Please provide a valid filename.", _
vbOKOnly, "Invalid Filename"
GoTo GetFileNameFromUser
End If
If Trim(UCase(anyFilename)) = ".XLS" Then

If MsgBox("You have chosen to Cancel the file save." & _
"Did you really intend to Cancel this operation?", _
vbYesNo + vbInformation, "Confirm Cancel") <> vbYes Then
GoTo GetFileNameFromUser
Else

anyFilename = ":* QUIT *:"
userInput = ""
End If
End If
If userInput <> "" Then

userInput = Dir(SaveToPath & anyFilename)
End If
Loop


If anyFilename <> ":* QUIT *:" Then

ActiveWorkbook.SaveAs Filename:=SaveToPath & anyFilename
End If

Case Else


Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

End Select
End If

End Sub



Private Function ValidateFilename(anyFilename As String) As String

Dim InvalidCharacterList As String
InvalidCharacterList = "\/:*?<>|" & Chr$(34)
Dim LC As Integer

ValidateFilename = ""
If Len(Trim(anyFilename)) = 0 Then
ValidateFilename = "EMPTY"
Exit Function
End If
anyFilename = Trim(anyFilename)
For LC = 1 To Len(anyFilename)
If InStr(InvalidCharacterList, Mid(anyFilename, LC, 1)) Then
ValidateFilename = Mid(anyFilename, LC, 1)
Exit Function
End If
Next

End Function



I pasted yours and made on modification. I changed the Labels to QuoteName
and CustomerName. Other than that it is just as you wrote it.

Thanks in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top