Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs Macro - Build filename from cell contents HOW?
I would really appreciate anyone who could show me macro code to accomplish
this. I need to save workbooks with a filename generated from the values in multiple cells. I need to fully automate this process up to the point where i can browse to a save location, then hit save. The criteria is as follows.. - MUST specify (2) cells to gather data from to join to make 1 filename ie. A1,A2 (saveas filename should look like "data1 - data2.xls" ) - MUST also specify worksheet in the workbook to grab the data from ie. 'SHEET2' - MUST pop-up the 'browse' dialog, so i can select save location. TIA! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs Macro - Build filename from cell contents HOW?
Dim res as Variant, sName as String
with Worksheets("Sheet2") sName = .Range("A1").Text & " - " & .Range("A2").Text End With res = Application.GetSaveAsfilename( InitialFilename:=sName & ".xls") If instr(1,res,sname,vbTextcompare) = 0 then msgbox "Please don't change the file name - action is halted" exit sub end if if res = False then exit sub Activeworkbook.SaveAs res -- Regards, Tom Ogilvy "Ch33baman2" wrote: I would really appreciate anyone who could show me macro code to accomplish this. I need to save workbooks with a filename generated from the values in multiple cells. I need to fully automate this process up to the point where i can browse to a save location, then hit save. The criteria is as follows.. - MUST specify (2) cells to gather data from to join to make 1 filename ie. A1,A2 (saveas filename should look like "data1 - data2.xls" ) - MUST also specify worksheet in the workbook to grab the data from ie. 'SHEET2' - MUST pop-up the 'browse' dialog, so i can select save location. TIA! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs Macro - Build filename from cell contents HOW?
Tom, thanks very much for your solution!
I have modified the code to REMOVE the .XLS portion from the "Suggested Filename", but the "save as type" defaults to "all files (*.*)" So if i occasionaly choose to change the filname, it saves it with NO extension. I need it to default to (*.XLS) (I also removed the "please dont change the filemane" option as i want to be able to modify the filename from time to time) Thanks again "Tom Ogilvy" wrote: Dim res as Variant, sName as String with Worksheets("Sheet2") sName = .Range("A1").Text & " - " & .Range("A2").Text End With res = Application.GetSaveAsfilename( InitialFilename:=sName & ".xls") If instr(1,res,sname,vbTextcompare) = 0 then msgbox "Please don't change the file name - action is halted" exit sub end if if res = False then exit sub Activeworkbook.SaveAs res -- Regards, Tom Ogilvy "Ch33baman2" wrote: I would really appreciate anyone who could show me macro code to accomplish this. I need to save workbooks with a filename generated from the values in multiple cells. I need to fully automate this process up to the point where i can browse to a save location, then hit save. The criteria is as follows.. - MUST specify (2) cells to gather data from to join to make 1 filename ie. A1,A2 (saveas filename should look like "data1 - data2.xls" ) - MUST also specify worksheet in the workbook to grab the data from ie. 'SHEET2' - MUST pop-up the 'browse' dialog, so i can select save location. TIA! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs Macro - Build filename from cell contents HOW?
Also, is there any way to have the BROWSE dialog start up to a specific path?
ie UNC pathname or a different drive & path? "Ch33baman2" wrote: I would really appreciate anyone who could show me macro code to accomplish this. I need to save workbooks with a filename generated from the values in multiple cells. I need to fully automate this process up to the point where i can browse to a save location, then hit save. The criteria is as follows.. - MUST specify (2) cells to gather data from to join to make 1 filename ie. A1,A2 (saveas filename should look like "data1 - data2.xls" ) - MUST also specify worksheet in the workbook to grab the data from ie. 'SHEET2' - MUST pop-up the 'browse' dialog, so i can select save location. TIA! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs Macro - Build filename from cell contents HOW?
Look at help on GetSaveAsFilename for the filefilter argument to control the
saveas type. modify Activeworkbook.SaveAs res to Activeworkbook.SaveAs res, xlWorkbookNormal -- Regards, Tom Ogilvy "Ch33baman2" wrote: Tom, thanks very much for your solution! I have modified the code to REMOVE the .XLS portion from the "Suggested Filename", but the "save as type" defaults to "all files (*.*)" So if i occasionaly choose to change the filname, it saves it with NO extension. I need it to default to (*.XLS) (I also removed the "please dont change the filemane" option as i want to be able to modify the filename from time to time) Thanks again "Tom Ogilvy" wrote: Dim res as Variant, sName as String with Worksheets("Sheet2") sName = .Range("A1").Text & " - " & .Range("A2").Text End With res = Application.GetSaveAsfilename( InitialFilename:=sName & ".xls") If instr(1,res,sname,vbTextcompare) = 0 then msgbox "Please don't change the file name - action is halted" exit sub end if if res = False then exit sub Activeworkbook.SaveAs res -- Regards, Tom Ogilvy "Ch33baman2" wrote: I would really appreciate anyone who could show me macro code to accomplish this. I need to save workbooks with a filename generated from the values in multiple cells. I need to fully automate this process up to the point where i can browse to a save location, then hit save. The criteria is as follows.. - MUST specify (2) cells to gather data from to join to make 1 filename ie. A1,A2 (saveas filename should look like "data1 - data2.xls" ) - MUST also specify worksheet in the workbook to grab the data from ie. 'SHEET2' - MUST pop-up the 'browse' dialog, so i can select save location. TIA! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use cell contents to build sheet reference | Excel Discussion (Misc queries) | |||
SaveAs Cell.value=filename | Excel Programming | |||
Macro to Save As with filename based on cell contents. | Excel Programming | |||
Set Saveas Filename from Cell Contents Using VBA | Excel Programming | |||
Macro to open SaveAs... and change filename to cell value | Excel Programming |