I am creating a bit of code that captures the excel sheet name, and saves the file based upon the sheet name.
Getting the sheet name works, and saving the file (without shetname) works.
Question - how do I get the sheetname into the filename ?
I need this to be all within the script, and dont want to use any excel cells as an easier option.
Thanks.
test code as below.....
Sub save_002()
x = ActiveSheet.Name
ChDir _
"C:\Documents and Settings\Raymond Downham\My Documents\My Files\Test Area\Commission Test"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Raymond Downham\My Documents\My Files\Test Area\Commission Test\test_004.xls"
End Sub
ie - Basically if the sheet is called ABC , then I wish the file to be called ABC.xls
Thanks,
Raymond Downham
Replies (4)
Please login or register to join the discussion.
try this
Sub save_002()
MName = ActiveSheet.Name & ".xls"
MDir = ActiveWorkbook.Path
ActiveWorkbook.SaveAs Filename:=MDir & "\" & MName
End Sub
this assumes that you want to save it in the same folder as the avtive workbook, and indeed that the activeworkbook has been saved. If not you could always substitute MDir with the text of the required directory.
Yes.....
.......works very well, thanks.
Hope you have your email alert on, as there will be a second question whenI get time to look at this .
Ah, the joys of month end !!
Hi John
try this.
Sub save_002()
MName = Worksheets("sheet1").Range("b1") & ".xls"
MDir = ActiveWorkbook.Path
ActiveWorkbook.SaveAs Filename:=MDir & "\" & MName
End Sub
assumes that the required name (excluding the file extension) is in sheet1 at range b1. You can substitute range names if required.
VBA help required....
I saw this and found it useful for an automation project I have been working on.
If I wanted to save as using text in a cell as a filename, how should I go about it?
Thanks,
John