Solved: Saving File - Automated File Naming

[thomas.szwed]

Hi there can anyone tell me how to do the following .

User clicks custom 'Save' Button

This brings up the Save As screen and the file name is already populated as

Cell D3, Contract, Cell G9.xls

So basically it looks to two cells for text aswell as having the text 'Contract' in the middle....?

Thanks for any responses....

[Bob Phillips]

[vba]

Dim filename As Variant

filename = Range("C3").Value & " Contract " & Range("G9").Value
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
[/vba]

[thomas.szwed]

Thanks XLD, this is great. Could i ask for two further solutions.

1. How can we edit the code so a comma appears between all three bits of text i.e CellC3,Cotnract,CellG9. Currently there are no commas.

2. This is much trickier. Cell C3 contains a name in full i.e. John Smith.

I need my naming convention to go like this.

Surname, Forename, 'Contract', CellG9.xls

Is there anyway in the code to pull out that fullname from Cell C3, split it up and then put in order surname, forename.......??

I know this seems tricky, wondering if it can be automated?

I dont have much room to play with on the sheet aswell, as I am using it as a userform and altering columns etc would not work.....

Thanks in advance...

[Bob Phillips]

Is the name in C3 always Forename Surname, never say Thomas A Szwed, or Mr T Szwed?

[thomas.szwed]

Is the name in C3 always Forename Surname, never say Thomas A Szwed, or Mr T Szwed?

The name is Cell C3 will ALWAYS be in the format Forename Surname

So for example. Thomas Szwed

[thomas.szwed]

PS I found a thread on how to seperate cells without using Data > Text function, but it doesnt seem to work.....?

http://www.excelforum.com/showthread.php?t=466827

[Bob Phillips]

[vba]

Dim filename As Variant

filename = Range("C3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & Left(filename, InStr(filename, " ") - 1) & ", Contract, " & Range("G9").Value
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
[/vba]

[thomas.szwed]

Hi XLD, this populates the name field in Save As, fine, but when you click Save the box disappears and it doesnt save....? Any clues why?

[Bob Phillips]

Because GetSaveAsfilename doesn't save anything, it just returns the full path of the file to be saved. You have to save that returned filename.

[thomas.szwed]

Thanks for your response, but this has left me confused? What are you saying my next action should be? I am pressing Save, but nothing is happening and the 'Save As' box just closes???

Appreciate your comments.....Thanks

[Bob Phillips]

[VBA]

Dim filename As Variant

filename = Range("C3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & Left(filename, InStr(filename, " ") - 1) & ", Contract, " & Range("G9").Value
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then

ActiveWorkbook.SaveAs filename
End If
[/VBA]

[thomas.szwed]

Hi XLD, thanks for having a crack but unfortunately this doesnt work, i have attached my workbook so you can have a look rather than try to explain it. The button you need to press is Save to File and the code is found in Module 2.

Thanks in AdvaNCE

[Bob Phillips]

The clue was in the error message, you can't have / in a filename, because your file name field G9 was a date, and it picked up that value. Use text instead

[vba]

Dim filename As Variant

filename = Range("D3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & Left(filename, InStr(filename, " ") - 1) & ", Contract, " & Range("G9").Text
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then

ActiveWorkbook.SaveAs filename
End If
[/vba]

[thomas.szwed]

Genius, well done. I knew about that but assumed the problem was somewhere else.

Finally how do i get a space in between Surname/Forename as it is currently combined

currently = SzwedTom, Contract, 01 December 2008

preferably = Szwed Tom, Contract, 01 December 2008

Thanks and wont pester you again!

[Bob Phillips]

[vba]

Dim filename As Variant

filename = Range("D3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & " " & _
Left(filename, InStr(filename, " ") - 1) & _
", Contract, " & Range("G9").Text
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then

ActiveWorkbook.SaveAs filename
End If
[/vba]

[thomas.szwed]

Ahhh! One last thing i have now noticed! If the user presses the button when nothing is entered in the boxes then we get debug error "Invalid Procedure Call or Arguement".

How would i put in a Msg Box to prevent this sub being carried out if there were no values present in the two required boxes???

Many Thanks

[Bob Phillips]

[vba]

Dim filename As Variant

With ActiveSheet

If .Range("D3").Value = "" Or .Range("D3").Value = "" Then

MsgBox "Both C3 and G9 must have values", vbOKOnly + vbExclamation, "Save File"
Else

filename = Range("D3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & " " & _
Left(filename, InStr(filename, " ") - 1) & _
", Contract, " & Range("G9").Text
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then

ActiveWorkbook.SaveAs filename
End If
End If
End With
[/vba]