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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,377
Messages
5,571,775
Members
412,419
Latest member
acemali
Top