VBA routine to save file based on sheet name

VBA routine to save file based on sheet name

Didn't find your answer?

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.

avatar
By listerramjet
03rd Jan 2007 13:03

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.

Thanks (0)
avatar
By velohead
05th Jan 2007 09:16

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 !!

Thanks (0)
avatar
By listerramjet
17th Jan 2007 12:34

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.

Thanks (0)
avatar
By johncollis
17th Jan 2007 11:32

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

Thanks (0)