SaveAs using two cells for filename

I'm trying to save a file with two cells of data to create the filename. Here 
is the code:
Dim fname
With ActiveWorkbook
fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls"
..SaveAs fname
End With

I'm getting stopped on the fname line. I don't think I have the combination 
right. Can you help?
0
David4882 (672)
6/3/2005 11:00:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
245 Views

Similar Articles

[PageSpeed] 47

Dim fname as String
With ActiveWorkbook.worksheets(1)
   fname = .Range("B4").Value & .Range("E6") & ".xls"
   .SaveAs fname
End With

You missed the .worksheets(1) in front of E6.

Any chance that one of those cells contains a date--and that the value has
slashes in it (12/31/2005)?

If yes, this might help later...

Dim fname as String
With ActiveWorkbook.worksheets(1)
   fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls"
   .SaveAs fname
End With

If not, just ignore it.

David wrote:
> 
> I'm trying to save a file with two cells of data to create the filename. Here
> is the code:
> Dim fname
> With ActiveWorkbook
> fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls"
> .SaveAs fname
> End With
> 
> I'm getting stopped on the fname line. I don't think I have the combination
> right. Can you help?

-- 

Dave Peterson
0
ec357201 (5290)
6/3/2005 11:08:51 PM
I've almost got it...it saved the file in the right format, but saved it deep 
in my settings. I want to save it to a particular path, which was earlier in 
the code, but it is not working now...I always want to save in the directory 
below. By the way, you were right, the E6 was a DATE. Can I put spaces in the 
date format like mm dd yyyy? Here is what I have:

Dim fname
With ActiveWorkbook.Worksheets(1)
fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls"
ChDir "C:\Franchise_GPC\Ben Info\Big Picture 2005\"
..SaveAs fname

"Dave Peterson" wrote:

> Dim fname as String
> With ActiveWorkbook.worksheets(1)
>    fname = .Range("B4").Value & .Range("E6") & ".xls"
>    .SaveAs fname
> End With
> 
> You missed the .worksheets(1) in front of E6.
> 
> Any chance that one of those cells contains a date--and that the value has
> slashes in it (12/31/2005)?
> 
> If yes, this might help later...
> 
> Dim fname as String
> With ActiveWorkbook.worksheets(1)
>    fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls"
>    .SaveAs fname
> End With
> 
> If not, just ignore it.
> 
> David wrote:
> > 
> > I'm trying to save a file with two cells of data to create the filename. Here
> > is the code:
> > Dim fname
> > With ActiveWorkbook
> > fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls"
> > .SaveAs fname
> > End With
> > 
> > I'm getting stopped on the fname line. I don't think I have the combination
> > right. Can you help?
> 
> -- 
> 
> Dave Peterson
> 
0
David4882 (672)
6/3/2005 11:48:01 PM
I got it....here's what I ended up with...

Dim fname As String
With ActiveWorkbook.Worksheets(1)
fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls"
..SaveAs "C:\Franchise_GPC\Ben Info\Big Picture 2005\" & fname
End With

Thanks again for the help!!

"Dave Peterson" wrote:

> Dim fname as String
> With ActiveWorkbook.worksheets(1)
>    fname = .Range("B4").Value & .Range("E6") & ".xls"
>    .SaveAs fname
> End With
> 
> You missed the .worksheets(1) in front of E6.
> 
> Any chance that one of those cells contains a date--and that the value has
> slashes in it (12/31/2005)?
> 
> If yes, this might help later...
> 
> Dim fname as String
> With ActiveWorkbook.worksheets(1)
>    fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls"
>    .SaveAs fname
> End With
> 
> If not, just ignore it.
> 
> David wrote:
> > 
> > I'm trying to save a file with two cells of data to create the filename. Here
> > is the code:
> > Dim fname
> > With ActiveWorkbook
> > fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls"
> > .SaveAs fname
> > End With
> > 
> > I'm getting stopped on the fname line. I don't think I have the combination
> > right. Can you help?
> 
> -- 
> 
> Dave Peterson
> 
0
David4882 (672)
6/4/2005 12:03:01 AM
You didn't use "mm dd yyyy" as your format <bg>.

I don't like spaces in my filename, and it might make it easier to read if you
use underscores:

fname = .Range("B4").Value & "_" & _
            Format(.Range("E6").Value, "yyyy_mm_dd") & ".xls"

But this is just a personal preference.


David wrote:
> 
> I got it....here's what I ended up with...
> 
> Dim fname As String
> With ActiveWorkbook.Worksheets(1)
> fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls"
> .SaveAs "C:\Franchise_GPC\Ben Info\Big Picture 2005\" & fname
> End With
> 
> Thanks again for the help!!
> 
> "Dave Peterson" wrote:
> 
> > Dim fname as String
> > With ActiveWorkbook.worksheets(1)
> >    fname = .Range("B4").Value & .Range("E6") & ".xls"
> >    .SaveAs fname
> > End With
> >
> > You missed the .worksheets(1) in front of E6.
> >
> > Any chance that one of those cells contains a date--and that the value has
> > slashes in it (12/31/2005)?
> >
> > If yes, this might help later...
> >
> > Dim fname as String
> > With ActiveWorkbook.worksheets(1)
> >    fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls"
> >    .SaveAs fname
> > End With
> >
> > If not, just ignore it.
> >
> > David wrote:
> > >
> > > I'm trying to save a file with two cells of data to create the filename. Here
> > > is the code:
> > > Dim fname
> > > With ActiveWorkbook
> > > fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls"
> > > .SaveAs fname
> > > End With
> > >
> > > I'm getting stopped on the fname line. I don't think I have the combination
> > > right. Can you help?
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
ec357201 (5290)
6/4/2005 3:01:08 AM
Thank you again....!!

"Dave Peterson" wrote:

> You didn't use "mm dd yyyy" as your format <bg>.
> 
> I don't like spaces in my filename, and it might make it easier to read if you
> use underscores:
> 
> fname = .Range("B4").Value & "_" & _
>             Format(.Range("E6").Value, "yyyy_mm_dd") & ".xls"
> 
> But this is just a personal preference.
> 
> 
> David wrote:
> > 
> > I got it....here's what I ended up with...
> > 
> > Dim fname As String
> > With ActiveWorkbook.Worksheets(1)
> > fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls"
> > .SaveAs "C:\Franchise_GPC\Ben Info\Big Picture 2005\" & fname
> > End With
> > 
> > Thanks again for the help!!
> > 
> > "Dave Peterson" wrote:
> > 
> > > Dim fname as String
> > > With ActiveWorkbook.worksheets(1)
> > >    fname = .Range("B4").Value & .Range("E6") & ".xls"
> > >    .SaveAs fname
> > > End With
> > >
> > > You missed the .worksheets(1) in front of E6.
> > >
> > > Any chance that one of those cells contains a date--and that the value has
> > > slashes in it (12/31/2005)?
> > >
> > > If yes, this might help later...
> > >
> > > Dim fname as String
> > > With ActiveWorkbook.worksheets(1)
> > >    fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls"
> > >    .SaveAs fname
> > > End With
> > >
> > > If not, just ignore it.
> > >
> > > David wrote:
> > > >
> > > > I'm trying to save a file with two cells of data to create the filename. Here
> > > > is the code:
> > > > Dim fname
> > > > With ActiveWorkbook
> > > > fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls"
> > > > .SaveAs fname
> > > > End With
> > > >
> > > > I'm getting stopped on the fname line. I don't think I have the combination
> > > > right. Can you help?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
David4882 (672)
6/4/2005 10:46:01 AM
Reply:

Similar Artilces:

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

Using XP Briefcase with money file
I put a copy of my money file into the laptop briefcase and I am able to keep them in sych for a while. For some reason I get locked out, the computer knows the file has been updated but it will not let the desk top file update the laptop file, I get the following message; "The source file can not be opened" "Check to see if the other program has the file opened, and close the file before updating the briefcase." Other files update just fine. When I get this message, I can't even drag a new copy of the Money file to my briefcase, I have to rename the file an...

Exchange Features Tab in ADUC when using Windows 2000 SP3
Hi, I would like to run Active Directory Users and Computers with Exchange 2003 Admin tools I'm limited to Windows 2000 Prof/SP3 Everything works,except from the Exchange Features tab. When selecting this tab, ADUC hangs. Any ideas. (I have to stay on SP3!) br TN Terminal Services is always my first choice. On Wed, 13 Apr 2005 23:35:22 +0200, "newsMS" <t_n@trashcan.dk> wrote: >Hi, > >I would like to run Active Directory Users and Computers with Exchange 2003 >Admin tools >I'm limited to Windows 2000 Prof/SP3 > >Everything works,except...

Newbie Cell Reference Question...
Hello, I apologize if this question has been answered before, but I'm no quite sure what to search for as I'm not very good with excel. I'm making a spreadsheet where one sheet references another. I'll tr to explain this as best as possible.... I'm creating a spreadsheet for a fantasy basketball league. I have "Data" sheet that contains data for all players. I have another sheet "Teams" that has all the players on each team. Column B contains th players name, and column C contains a number that corresponds to th row this player is on in the dat...

Using a variable for a chart limit
Since I got instant help on my last query, is there any way to use a cell reference as an axis minimum or maximum in format axis? It seems impossible, but there is a lot of experience out there. Thanks in advance. -- Vince F Hi, See Jon's information http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html Cheers Andy Vince F wrote: > Since I got instant help on my last query, is there any way to use a cell > reference as an axis minimum or maximum in format axis? It seems impossible, > but there is a lot of experience out there. Thanks in advance. -- Andy Pop...

How do I make door hangers using publisher?
Is this even possible? Mary Sauer has a website with a generous collection of goodies including door hangers. Take a look at just above George Washington's portrait (way down the page) at, http://msauer.mvps.org/publisher_projects2.htm -- Don - Publisher 2000� Vancouver, USA "medinapie" <medinapie@discussions.microsoft.com> wrote in message news:986B8199-C9B5-46CE-8F2F-C772BE5C21BC@microsoft.com... > Is this even possible? I have a sample on my web page. Scroll down -- on the left. http://msauer.mvps.org/graphics.htm Some cute ones here that you could mod...

using program to record audio comment and compress audio to MP3
I can use VBA to record an audio comment into a Word file using Selection.InlineShapes.AddOLEObject ClassType:="SoundRec", FileName:="", _ LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _ "C:\WINDOWS\system32\sndrec32.exe", IconIndex:=0, IconLabel:="Wave Sound" BUT but by default the file is stored as a PCM at 44kHz and 16 bit stereo whcih is about 200kB per second Sound recorder (which is used to do the recording) allows me to manually convert this to a MP3 and a much smaller file e.g. 3kB per second Is there any way I can aut...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

Cell background shading in Excel
Why is it that the background shading colors available under the Paintbucket Icon chjange from file to file? Also, how can I add some of the colors to the paintbucket that are availabe under the format/cells/pattern pulldown? Colors are a Workbook-level property, so you can change the color palette for any workbook. Choose Tools/Options/Colors... and modify the colors to your hearts' content. You can also import another workbook's color palette from that dialog. Note however, that there are only 56 bins in the color palette, so you can only use 56 colors at a time. The paint b...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...

Sum every other cell?
I own a used-book shop. I have an Excel SS to track how many books pe day I sell in each of 28 categories and the $$$ I take in for eac category. So there's two columns for each day: #books & $$$. therefore want to add every odd-numbered cell in a row to get the tota number of books for a category in a given period of days and ever even-numbered cell in the same row for the the total $$$. Is there simple formula or function for this -- bookmanj ----------------------------------------------------------------------- bookmanjb's Profile: http://www.excelforum.com/member.php?acti...

how do I find the equation of a line using a scatter plot?
I am taking a chem lab and right now we are studying rate laws using kenetics. we had to graph the results of our experiment and then find the equation of the line. such as y=mx+b but i cant fiugre out how to do it. or if i am supposed to put a trend line in the graph, but if that is what I am supposed to do how do I find the equation of it? First: to make trendline. Right click on the data in the chart; use Add trendline; specify Linear; on Options tab, check shoe equation Secondly:To get slope and intercept: Let's say you x-values are in A2:A11 and y-values in B2:B11 for slope us...

Using Rept function
I want to repeat one cells entry somewhere else in my (same) worksheet and have used the Rept function successfully. However, ifthe 1st cell (e.g. A3 is in date format like 12/12/2003, when I use the Rept A3,1 function in another cell (e.g.cell R3), it returns the date as a number (37967). What I want is for the date to be repeated exactly as is (12/12/2003 or whatever date is typed). Can anyone help please Don't use the REPT function. Just enter =A3 and apply the date format you want using Format, Cells if you do not automatically get it. -- Jim Rech Excel MVP You can't unless ...

MERGE CELLS
I have Name, PO Box, street address, city, state, zip across a row in 6 seperate cells/columns. I want to have this format in 1 cell: Name PO Box Street address City, State Zip STEVE wrote: > I have Name, PO Box, street address, city, state, zip across a row in 6 > seperate cells/columns. > > I want to have this format in 1 cell: > > Name > PO Box > Street address > City, State Zip > You do realize that this will screw up your ability to sort the data. A better solution would be to describe what you want to do with the data. You may not need it in the ...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...

Schema validation using Visual Studio .NET 2003
Hi, All Iam trying to do is validate a group of schemas which inturn references an external XSD schema(tried putting both in local webserver/as part of project),Iam getting errors This is what Iam doing 1. Open a blank Biztalk solution 2. Add all the schemas to validate 3. Validate Schema Am not sure what Iam doing wrong.Any help would be appreciated Thanks Raj ...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

looking for duplicates & unmatched between two columns
Hi - I'm looking for a function that will look at a cell (containing text) in one column and then look to a range of cells in another column and if a match is found to indicate "1" if not found to indate "0" ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** Hi have a look at http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon -- Regards Frank Kabel Frankfurt, Germany "Michael" <mweber@ulr-ins.com> schrieb im Newsbeitrag news:4072cbf6$0$202$75868355@news.frii.net...

How to use Or in txrbox
Hi Working in a relational db in Access 2003 I have a continuous form (MyForm). In Header section of the form I have an unbound txtbox control to enter words separated by Or. After entering the word(s) user click a button to trigger searching the word(s). My form is based on Allen's Browne search example. Thus searching controls are in Header and founded records must be displayed in form2 rather than in Details of the Myform. On Mon, 26 Apr 2010 13:20:02 -0700, sebastico <sebastico@discussions.microsoft.com> wrote: >Hi >Working in a relational db in Access 2003 I...