MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro with save as that does not change workbook name

Posted by David Pakalnis on September 17, 2001 12:17 PM

Ok, I am trying to save one work sheet as a text file.
I have that part down, but when I do the SaveAs method, it changes the name of the current workbook.
I have tried changing the name of the work book back, but it is read only.


Posted by Russell Hauf on September 17, 2001 12:21 PM

To not change the name of the workbook, you can use the Save method (instead of SaveAs). However, if you open a workbook read-only, there is no way to save the workbook with the same name - you would need to open the workbook with read/write access.

Hope this helps,


Posted by David Pakalnis on September 17, 2001 1:07 PM

I am sorry I was not clear...
I am using SaveAs since I am just trying to save a SINGLE worksheet, and in a different format.
(I am exporting one worksheet in a work book to a text file).

I do not want to have the workbook name change to the name I just exported the worksheet as.


Posted by Russell Hauf on September 17, 2001 2:18 PM

Still not sure exactly what you are trying to do. Are you trying to save one sheet as a new workbook with only one sheet? What do you mean by "a different format"? Are you trying to save in something other than Excel format?

If you're just trying to make a new workbook with one sheet, use the Workbooks.Add method, then copy over your sheet, then delete the other sheets (the workbook you added probably started wtih 3 or more sheets in it). If you need code to copy the sheet over, I suggest opening 2 workbooks, then record a macro where you copy a sheet from one workbook to the other (Edit-Move or Copy Sheet...etc.).


Posted by Juan Pablo on September 17, 2001 2:43 PM

The code should go something like this... are you doing something like it ?

Application.DisplayAlerts = False ActiveWorkbook.SaveAs FileName:="Yournewfile.txt", Fileformat:=xlText
Application.DisplayAlerts = True

I think that this way it should work. You have to be in the sheet you want to copy...