Help "saving as" please

[Peter]

Hi,

I have a user form that I have almost got sorted except for a few
things. One of these is saving the spreadsheet.

In cell C2 of the spreadsheet associated with my user form has a date
in it. Can anyone please advise if it is possible to use the contents
of cell C2 in the following line in place of the ?'s such that the
spreadsheet saved in the H drive would have the name, say, 23 Oct 04

ActiveWorkbook.SaveAs "H:\?????????", -4143

Similiarly, another line

ActiveWorkbook.SendMail "(e-mail address removed)", "?????????"

sends the spreadsheet as an enclosure. Is it possible to again use the
content of C2 as the title of the email, replacing the ?'s in the
above line?

Any suggestions would be much appreciated.


--
Cheers

Peter

Remove the INVALID to reply

 

[Bob Phillips]

Hi Peter,

Active.SaveAs "H:\?" & Format(Activesheet.Range("C2"), "dd mmm yy")

Check out Ron de Bruin's site at www.rondebruin.nl/sendmail.htm for sendmail
options.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[I.T.]

Following works as a straightforward way to save file as whatever nam
is in C2

Dim xlsfname As String
xlsfname = Worksheets("Data").Range("C2").Value
ActiveWorkbook.SaveAs FileName:="H:\" & xlsfname & ".xls"

Please note there is NO error correction in this.

Or alternatively, if you want to specify the drive and directory t
save the file to then the following works and defaults the filename a
the date in cell C2 (Format the cell according to how you would lik
the name of the file displayed)

Dim xlsfname, xlsfpath As String
Sheets("Data").Select
xlsfname = Worksheets("Data").Range("C2").Value
xlsfpath = Application.GetSaveAsFilename(xlsfname
FileFilter:="Excel Files _ (*.xls),*.xls", FilterIndex:=1
Title:="Select Save location")
If xlspath = "False" Then
End
End If
ActiveWorkbook.SaveAs FileName:=xlsfpath & ".xls"

The only error correction in this code is if the user hits "Cancel" o
the "Select Save Location" Dialog then the procedure ends