Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use cell contents to build sheet reference Horatio J. Bilge, Jr. Excel Discussion (Misc queries) 6 September 28th 09 05:12 PM
SaveAs Cell.value=filename Helmut Excel Programming 4 November 23rd 06 04:26 PM
Macro to Save As with filename based on cell contents. [email protected] Excel Programming 4 June 22nd 06 11:02 AM
Set Saveas Filename from Cell Contents Using VBA Celtic_Avenger Excel Programming 1 September 5th 04 10:57 PM
Macro to open SaveAs... and change filename to cell value Andy Excel Programming 5 July 19th 04 12:23 PM


All times are GMT +1. The time now is 09:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"