correction: Get a value from cell D3 and use that value as a new filename

Hi Moe

There sure is! try this

Sub GetFileName()
Dim FileName As String
FileName = Sheets("Sheet1").Range("D3") & ".xls"
MsgBox FileName
End Sub

Dave

OzGrid Business Applications

Re: Get a value from cell D3 and use that value as a new filename

Oh, it works! Thank you Dave!! But how do I close the file now?
and save the file now? I put the code in a module under Book1, and
when I click on the button to run, it gives me the data in cell D3.xls, but I don't
understand how this is naming the file. When I try to close the
file I get the prompt to save and name it. What am I not understanding?
Moe

Re: Get a value from cell D3 and use that value as a new filename

The Msgbox function was for an example only. To save and close the Workbook as this name use:

Sub GetFileName()
Dim FileName As String
FileName = Sheets("Sheet1").Range("D3") & ".xls"
ThisWorkbook.Close SaveChanges:=True, FileName:=FileName
End Sub

Dave

OzGrid Business Applications

Run time error

Hi Dave,
The code now results in two snags. The first says that
"A file named "287.xls" already exisits, do you want to
replace it? If I say yes, a blank excel window appears (no
workbook at all). If I say no, I get a run-time error
stopping at the "close of object '_Workbook' failed.

Here is what I am really trying to do:
Cell D1 : 5/23/2000
Cell D2 : =TODAY()
Cell D3 : =DAYS360(D1,D2)

Cell D3 gives me the number of days that have passed between
today and 5/23/00, and updates daily. I want to put a shortcut
on my desktop to the file and have the name of the file be
the contents of cell D3. It's a round-about way of seeing the
contents of D3 without opening Excel if I can.
Any ideas? Thanks for all the help!
Moe

Re: Run time error

Moe, the blank screen is no doubt because there are no other workbooks open.
The Run time error is because when you say "No" to replace the existing file is cannot complete the procedure.
I'm assuming now that you want to close ALL open workbooks when you click your button and save over the top of the existing file.

Sub GetFileName()
Dim FileName As String
FileName = Sheets("Sheet1").Range("D3") & ".xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs FileName
Application.Quit
End Sub

Dave

OzGrid Business Applications

super!

Dave it looks like you did it! I dragged the file to the desktop and
tomorrow will be the true test! Thanks so much! You really 'excelled'
at helping a beginner VBer!

Moe