naming a workbook

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I would like to use a macro with a userform to name a workbook. is there a code for this? I appriciate any help I could get.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
A bit too general perhaps. A general answer ...

Code:
ActiveWorkBook.Name=TextBox1.Value & ".xls"
 

LRRICHEY

New Member
Joined
Nov 4, 2005
Messages
2
BrianB said:
A bit too general perhaps. A general answer ...

Code:
ActiveWorkBook.Name=TextBox1.Value & ".xls"

Brian
I just tested this and I get the message

Cant assign to a read only property!

What I need to beable to do is have the macro start the save as process and add something to the name automaticly.
for example if a click on the save as button it will get the save as window to open and the file name will be for example "Book1.xls" but what I need it to say is "RaysBook1.xls" or some other phrase.
If i use this
fName = ActiveWorkbook.Name
fName = RIC & "_" & fName
ActiveWorkbook.SaveAs (fName)
The last line ActiveWorkbook.SaveAs will automaticly save the book but the save as applette will not come up.
I would like the applet to come up so I can verify that the name is what I want it to be
Thanks
Ray
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You need to change the Drive and Folder Path in the code below!
Also, change "\AndWhatEver" to what ever you want added to the default Workbook name, Note: The "\" before the "AndWhatEver" must stay!

Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If

End Sub


If you have the initial Workbook opened as a Read-Only copy then use this version of the code:


Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$
Dim fs As Variant, f As Variant

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fs.GetFileName(ActiveWorkbook.FullName))

'Re-Set ReadOnly! Note: "1" is the Read Only code!
If f.Attributes And 1 Then f.Attributes = f.Attributes - 1

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If

End Sub
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127

ADVERTISEMENT

ActiveWorkBook.Name=TextBox1.Value & ".xls"

Oops. Sorry - end of a long day :oops:

Should be

Code:
ActiveWorkbook.SaveAs Filename:=Textbox1.Value & ".xls"
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
BrianB, that worked great. I attached the code to the text box. now I am looking to attach a code to a command button in the same user box to close the user box. any ideas???
thanks for your help
royboy531
 

LRRICHEY

New Member
Joined
Nov 4, 2005
Messages
2
This worked great!! :biggrin:
Thanks
Joe Was said:
You need to change the Drive and Folder Path in the code below!
Also, change "\AndWhatEver" to what ever you want added to the default Workbook name, Note: The "\" before the "AndWhatEver" must stay!

Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If

End Sub


If you have the initial Workbook opened as a Read-Only copy then use this version of the code:


Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$
Dim fs As Variant, f As Variant

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fs.GetFileName(ActiveWorkbook.FullName))

'Re-Set ReadOnly! Note: "1" is the Read Only code!
If f.Attributes And 1 Then f.Attributes = f.Attributes - 1

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If

End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,805
Members
430,322
Latest member
excelnoobnoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top