-
FileName Manipulation Problem
hi,
I have the following code which automatically saves my worksheet into an attachment in an email and autopopulating its filename from values on my worksheet.
[vba] 'Save the new workbook/Mail it/Delete it
With ActiveSheet
TempFilePath = Environ$("temp") & "\"
TempFileName = Range("C2").Value & ", CONTRACT, " & Format(Range("C22").Value, "dd.mm.yy")[/vba]
However, the first bit of the filename is from a cell C2 which contains a name e.g. John Smith. However when saving the filename, I want this name to be reversed so would be Smith, John.
I have some code to do this from another Sub:
[VBA]Sub SaveToFile()
Dim FileName As Variant
FileName = Range("E6").Value
FileName = Mid(FileName, InStr(FileName, " ") + 1) & " " & _
Left(FileName, InStr(FileName, " ") - 1) & _
", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy")
FileName = Application.GetSaveAsFilename(FileName, "Microsoft Excel Files (*.xls), *.xls")
If FileName <> False Then
ActiveWorkbook.SaveAs FileName
End If
End Sub
[/vba]
However I tried copying and pasting into my new sub but it doenst like it because it refernces FileName twice....
any thoughts on how to implement this???
THanks alot!
-
works ok here but FileName is the name Excel uses as a property - it doesn't seem to matter for me but it would eliminate doubt if you use say MyFileName or TheFileName instead. If hat fails, use different variable names either side of the assignment statement (the '=' sign).
ps
Just a suggestion, if there happens to be a name such as
John Harry Smith
it currently ends up as
Harry Smith John
A small change using instrrev instead of instr may be preferred:
Mid(FileName, InStrRev(FileName, " ") + 1) & " " & Left(FileName, InStrRev(FileName, " ") - 1)
which yields
Smith John Harry
Last edited by p45cal; 06-25-2009 at 03:54 AM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
So just change every instance of FileName to MyFileName?
-
As a first step it would be very easy to try it.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
Do i need to change this aswell?
[VBA]Dim FileName As Variant[/VBA]
-
That would seem to fit in with "So just change every instance of FileName to MyFileName"
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules