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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
A bit too general perhaps. A general answer ...

Code:
ActiveWorkBook.Name=TextBox1.Value & ".xls"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,761
Messages
6,138,452
Members
450,139
Latest member
Sushmita Ramesh Kulkarni

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
Back
Top