create a macro to automaticaly name a file.

[Guest]

I need to have control over file naming in a worksheet would like to be able
to do this by assiging a macro to a button.

 

[Harald Staff]

Write a macro containing something like
ActiveWorkbook.SaveAs "C:\Temp\MyFile.xls"
and assign it to your button.

HTH. Best wishes Harald

 

[Guest]

Harald: Is it possible to pull the information out of a cell entered in the
worksheet?
Thanks
BH

 

[Tom Ogilvy]

Write a macro containing something like
where cell B9 contains the string

C:\Temp\MyFile.xls

 

[Guest]

Tom:
Were do I inserte the file path.

where cell B9 contains the string

C:\Temp\MyFile.xls

 

[Tom Ogilvy]

if B9 contains the string C:\Temp\MyFile.xls

then C:\Temp\ is the file path.

The activeworkbook is saved to the C:\Temp directory with a filename of
MyFile.xls

 

[Guest]

Tom;
I am looking to have the file name be what ever I enter into cell B9.
Thanks

 

[Tom Ogilvy]

sPath = "C:\Temp\"
ActiveWorkbook.SaveAs sPath & Activesheet.Range("B9").Value

where cell B9 contains the name you want.

------------------

if you want the path in B8 and the name in B9

sPath = Range("B8").Value
if Right(spath,1) <> "\" then sPath = sPath & "\"
ActiveWorkbook.SaveAs sPath & Activesheet.Range("B9").Value

------------------


If you want to save in the same location as the activeworkbook currently is
located

sPath = ActiveWorkbook.Path
if Right(spath,1) <> "\" then sPath = sPath & "\"
ActiveWorkbook.SaveAs sPath & Activesheet.Range("B9").Value

 

[Guest]

Tom:

sPath = "C:\Temp\"
ActiveWorkbook.SaveAs sPath & Activesheet.Range("B9").Value

where cell B9 contains the name you want.

------------------

if you want the path in B8 and the name in B9

sPath = Range("B8").Value
if Right(spath,1) <> "\" then sPath = sPath & "\"
ActiveWorkbook.SaveAs sPath & Activesheet.Range("B9").Value

------------------


If you want to save in the same location as the activeworkbook currently is
located

sPath = ActiveWorkbook.Path
if Right(spath,1) <> "\" then sPath = sPath & "\"
ActiveWorkbook.SaveAs sPath & Activesheet.Range("B9").Value

 

[Guest]

All I am trying to do is place a command button on a form that will name the
file from a cell and save the file under that name , I have not had any luck
so far. I may not be seeting up the macro the right way.

 

[Dave Peterson]

You may want to post the code that's failing (don't attach the workbook).

 

[Guest]

ActiveWorkbook.SaveAs sPath & Activesheet.Range("B9").Value

 

[Dave Peterson]

What's in B9?

There are some characters that can't be used in file names (like *, /, \).

(Make sure you're looking at the correct sheet, too. Maybe the wrong sheet is
active.)

 

[Guest]

B9 will be a field that is entered by the user it will contain text,or
numerical values.

 

[Dave Peterson]

I meant what was in B9 when the code failed?

msgbox "***" & activesheet.range("b9").value & "***"

would show you the value surrounded by asterisks.

 

[Guest]

There was not anything in the b9 cell. I am new to the use of macros and need
a step by setp layout of what I need to do. I am tring to set up a templet
that I will have control over the file name by entering data into a cell. So
if I ener a name in cell b9 I when I click my command button I would like to
have the file saved as the name in cell b9.

 

[Dave Peterson]

You could check to see if there is anything in that cell before you try to save
the workbook:

if isempty(activesheet.range("b9")) then
msgbox "Please put something in B9!"
exit sub
end if

'rest of code here.

=====
but there's lots of things that you could put in B9 that would make the save
fail. Sometimes it's just better to try it and then check to see if it was
successful:

Option Explicit
Sub testme()
Dim sPath As String
sPath = "c:\my documents\excel\"

On Error Resume Next
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B9").Value
If Err.Number <> 0 Then
MsgBox "File not saved!" & vbLf & "Please check B9"
Err.Clear
End If

End Sub

There was not anything in the b9 cell. I am new to the use of macros and need
a step by setp layout of what I need to do. I am tring to set up a templet
that I will have control over the file name by entering data into a cell. So
if I ener a name in cell b9 I when I click my command button I would like to
have the file saved as the name in cell b9.

 

[Guest]

Thanks a million.

I meant what was in B9 when the code failed?

msgbox "***" & activesheet.range("b9").value & "***"

would show you the value surrounded by asterisks.