A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

SaveAs Macro - Build filename from cell contents HOW?



 
 
Thread Tools Display Modes
  #1  
Old February 22nd 07, 03:04 PM posted to microsoft.public.excel.programming
Ch33baman2
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!
Ads
  #2  
Old February 22nd 07, 03:17 PM posted to microsoft.public.excel.programming
Tom Ogilvy
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  
Old February 22nd 07, 04:22 PM posted to microsoft.public.excel.programming
Ch33baman2
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  
Old February 22nd 07, 04:34 PM posted to microsoft.public.excel.programming
Ch33baman2
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  
Old February 22nd 07, 05:03 PM posted to microsoft.public.excel.programming
Tom Ogilvy
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!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03: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 04:31 PM.


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