tomsweddy
06-25-2009, 03:23 AM
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.
'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")
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:
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
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!
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.
'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")
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:
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
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!