Using cell data as file name
 

I've created a template for financial analysis and approval for my
sales force. One of the fields is a Quote Reference in which the user
can input any data that they want. The objective being that they begin
the process with a financial analysis, deliver the analysis for
approval, and provide a quote using the same data and Quote Reference.

I'd like to be able to use the data in the Quote Reference field as the
filename and create a button that will automatically use that data to
name the file when clicked.

I'm a complete noob to VBA, but otherwise quite experienced with most
other aspects of Excel. Is there a way to make this happen (easily for
a noob)?

Using cell data as file name
 

This is just demo code:

Sub button_maker()
ActiveSheet.Buttons.Add(249.75, 204, 66.75, 53.25).Select
Selection.OnAction = "saveit"
End Sub



Sub saveit()
Dim dirname As String, fname As String
dirname = "C:\Documents and Settings\Owner\Desktop"
ChDir dirname
fname = Cells(2, 2).Value
ActiveWorkbook.SaveAs Filename:=dirname & fname
End Sub



For the demo, I assume that the filename is stored in cell B2. Run
button_marker to create the button and assign the button macro. When the
button is pressed, the saveit macro, assuming Quote Reference is cell B2,
saves the sheet using the contents of B2 as the filename.
--
Gary's Student
gsnu200701


"huntnpeck" wrote:

I've created a template for financial analysis and approval for my
sales force. One of the fields is a Quote Reference in which the user
can input any data that they want. The objective being that they begin
the process with a financial analysis, deliver the analysis for
approval, and provide a quote using the same data and Quote Reference.

I'd like to be able to use the data in the Quote Reference field as the
filename and create a button that will automatically use that data to
name the file when clicked.

I'm a complete noob to VBA, but otherwise quite experienced with most
other aspects of Excel. Is there a way to make this happen (easily for
a noob)?

Using cell data as file name
 

Thanks for this. Though I've still not got it working, (I gather my
unfamilarity with VBA is showing)

I have added a button and named it, saveit (it was 'CommandButton1',
though I'm not certain that the button text has anything whatsoever to
do with the subroutine "saveit"

The actual cell that the data resides in in my sheet is F3, so I
changed "Cells(2,2).Value" to "Cells(6,3).Value"

I also changed the path in "dirname= "C:\Documents and
Settings\Owner\Desktop" to the actual path I want the file to save to.

What does the subroutine "button_maker()" do? and what do the numbers
in the parenthesies mean?, i.e., (249.75, 204, 66.75, 53.25)

Thanks,

Huntnpeck

Gary''s Student wrote:
This is just demo code:

Sub button_maker()
ActiveSheet.Buttons.Add(249.75, 204, 66.75, 53.25).Select
Selection.OnAction = "saveit"
End Sub



Sub saveit()
Dim dirname As String, fname As String
dirname = "C:\Documents and Settings\Owner\Desktop"
ChDir dirname
fname = Cells(2, 2).Value
ActiveWorkbook.SaveAs Filename:=dirname & fname
End Sub



For the demo, I assume that the filename is stored in cell B2. Run
button_marker to create the button and assign the button macro. When the
button is pressed, the saveit macro, assuming Quote Reference is cell B2,
saves the sheet using the contents of B2 as the filename.
--
Gary's Student
gsnu200701


"huntnpeck" wrote:

I've created a template for financial analysis and approval for my
sales force. One of the fields is a Quote Reference in which the user
can input any data that they want. The objective being that they begin
the process with a financial analysis, deliver the analysis for
approval, and provide a quote using the same data and Quote Reference.

I'd like to be able to use the data in the Quote Reference field as the
filename and create a button that will automatically use that data to
name the file when clicked.

I'm a complete noob to VBA, but otherwise quite experienced with most
other aspects of Excel. Is there a way to make this happen (easily for
a noob)?