Saving a Workbook using info from a Cell???

[Scott]

I have searched this site and found some items that relate to my issue but
everything I have tried does not seem to work.



I am using code to open a new workbook where certain parts of the original
information are copied to. I am then trying to save this new Workbook under
the date that occurs in Range("E2") on a worksheet named Data Entries of the
original workbook entitled Data.xls to a certain file location. The code I
have so far is:

Workbooks.Add

ActiveWorkbook.SaveAs Filename:= _

"C:\Documents and Settings\schoes14\Desktop\Min Out Reports\Min Out
Report.xls", FileFormat:= _

xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _

, CreateBackup:=False

I would like the date to appear right before " Min Out Report.xls"



Any help would be greatly appreciated.

 

[excel-ant]

Hi Scott,

Firstly you need to assin the value of cell E2 to a variable, say
qdate

qdate = Range("E2").Value

Then construct the string by which you want to save the file as
follows into variable qfile:-

qfile = "C:\Documents and Settings\schoes14\Desktop\Min Out Reports\"
& qdate & " Min Out Report.xls"

Then save file as qfile.

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=qfile _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

Hope this helps, Any problems get in touch.

Ant
http://www.excel-ant.co.uk

 

[Scott]

I had searched the site before posting this but I must not have had the
correct words in the search. When I search for my post using part of my
Subject line I actually found the answer to this question.

Thanks.

 

[Gary''s Student]

Let's get the cell value and then add the new workbook:

Sub Macro1()
namevalue = Workbooks("Data.xls").Sheets("Data Entries").Range("E2").Text
MsgBox (namevalue)
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\test\Book" & namevalue & ".xls"
End Sub

NOTE the date in the cell was 4 December 2008
I used the Text property to get the date without slashes.

 

[Scott]

Thanks alot. Your response actually helped to shorten everything I was using
each time I wanted to save. The shorter the code the better I say.

 

[edtheant]

dim sFileNameDate as string

sFileNameDate = Workbooks("data.xls").Sheets("Data Entries").Range("E2").value

ActiveWorkbook.SaveAs Filename:= "C:\Documents and
Settings\schoes14\Desktop\Min Out Reports\" & sFileNameDate & "Min Out
Report.xls"



Something like that anyway...