help required with using cells to generate a filename

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jeffhughes@ntlworld.com - 20 Mar 2006 20:08 GMT

is there a way of clicking on a button that asks you where you want
the file saving, but enters in a filename derived from the contents
of two cells and todays date?

for example if cell A1 contained 'hello' and cell B12 contained
'world' the prompted filename would be 'hello - world - 200306.xls'.

Any help would be appreciated.

TIA

Reply to this Message

jennifer1970@hotmail.com - 20 Mar 2006 20:21 GMT

Try something like this.

Dim sCell1 As String
Dim sCell2 As String
Dim DateStamp As String

sCell1 = Sheet1.Cells(1, 1).Value
sCell2 = Sheet2.Cells(1, 2).Value
DateStamp = Format(Date, "YYYYMMDD")

Application.GetSaveAsFilename sCell1 & " - " & _
sCell2 & " - " & _
DateStamp & ".XLS"

Reply to this Message

Tom Ogilvy - 20 Mar 2006 20:45 GMT

look at the getsaveasfilename

it has an InitialFilename argument

With Activeworkbook.worksheets(1)
fname = Application.GetSaveAsFilename InitialFilename:= _
"C:\My Folder\" & .Range("A1").Text & _
" - " & .Range("B12").text & " - " & _
format(Date,"yyyymm") & ".xls"
End with
Activeworkbook.SaveAs fname
But the user could always change it. If you just want to save the file with
the given name
with worksheets(1)
Activeworkbook.Saveas "C:\My Folder\" & .Range("A1").Text & _
" - " & .Range("B12").text & " - " & _
format(Date,"yyyymm") & ".xls"
End with

Signature

Regards,
Tom Ogilvy

"Bungle" wrote:

> is there a way of clicking on a button that asks you where you want
> the file saving, but enters in a filename derived from the contents
[quoted text clipped - 6 lines]
>
> TIA

Reply to this Message

Jay - 20 Mar 2006 21:50 GMT

You could also try this, Bungle, which is a variation of the previous two
suggestions. Copy it into your VBA editor work area; it's ready to run.
--Jay

Sub fileNameFromCellContentsAndDate()
With ActiveWorkbook.ActiveSheet
'First part of filename...
a = Range("A1").Value & " - " & Range("B1").Value & " - "

'Date part of filename
b = Format(Date, "ddmmyy")

'Filename extension...
c = ".xls"

'Use a variable name that is different from 'filename' because filename
is a VBA keyword
s_filename = a + b + c

fname = Application.GetSaveAsFilename(InitialFileName:=s_filename)
ActiveWorkbook.SaveAs Filename:=fname
End With
End Sub

"Bungle" wrote:

> is there a way of clicking on a button that asks you where you want
> the file saving, but enters in a filename derived from the contents
[quoted text clipped - 6 lines]
>
> TIA

Reply to this Message