MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Moe on March 10, 2001 2:49 PM

I am currently using Microsoft Excel 97 SR-2 and running
Windows 98. Is there a way that I can create an Excel
macro that will get a value from cell D3 and use that value
as a new filename? Possibly in a VB module?


Posted by Dave Hawley on March 10, 2001 3:07 PM

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

Posted by Moe on March 10, 2001 3:34 PM

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


Posted by Dave Hawley on March 10, 2001 4:15 PM

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

Posted by Moe on March 10, 2001 5:46 PM

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

Posted by Dave Hawley on March 10, 2001 5:58 PM

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

Posted by Moe on March 10, 2001 6:36 PM

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