help required with using cells to generate a filename
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 -- ---------------------------------------------- Posted with NewsLeecher v3.0 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
help required with using cells to generate a filename
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" |
help required with using cells to generate a filename
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 -- 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 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 -- ---------------------------------------------- Posted with NewsLeecher v3.0 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
help required with using cells to generate a filename
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_f ilename) 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 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 -- ---------------------------------------------- Posted with NewsLeecher v3.0 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
All times are GMT +1. The time now is 03:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com