Help setting a File Name

hi experts

I'm having trouble with the following:

Cell J1 of Sheet1 contains a date, say Dec. 15, 2009.   Sheet1 has a 'Save' 
button called cmbSave.  When I click cbmSave, I'd like the SaveAs... dialog 
to be shown with the  suggested File Name showing as    
'POS_Summary_091215.xls' .  In other words, I'd like the suggested file name 
to start with 'POS_Summary_', and to end with the J1 date in yymmdd format.  
Can this be done?

thank you in advance
Cinnie
-- 
cinnie
0
Utf
12/3/2009 7:27:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
717 Views

Similar Articles

[PageSpeed] 21

Sub newdate()
Dim d As Date, s As String
d = Range("J1").Value
s = Format(d, "yymmdd")
ActiveWorkbook.SaveAs Filename:="POS_Summary_" & s & ".xls"
End Sub

-- 
Gary''s Student - gsnu200909
0
Utf
12/3/2009 7:44:01 PM
thanks Gary's Student

This indeed saves the file as requested, but is there any way to get the 
Windows 'Save As'  dialog box to open with the File Name box showing the name 
that your code fragment produces?  This way the user can change the folder if 
desired. 

Many thanks for any thoughts
-- 
cinnie


"Gary''s Student" wrote:

> Sub newdate()
> Dim d As Date, s As String
> d = Range("J1").Value
> s = Format(d, "yymmdd")
> ActiveWorkbook.SaveAs Filename:="POS_Summary_" & s & ".xls"
> End Sub
> 
> -- 
> Gary''s Student - gsnu200909
0
Utf
12/4/2009 2:40:06 AM
Give this code a try inside your macro...

'  Put this line with your other variable declarations
Dim FileNameWithPath As String, Filters As String, Index As Long
.......
.......
'  Put these lines in your code wherever needed
FileNameWithPath = "c:\Temp\NewTextFilename.txt"
Filters = "All Files (*.*),*.*,Text Files (*.txt),*.txt"
Index = 2
Application.GetSaveAsFilename InitialFilename:=FileNameWithPath, _
                              FileFilter:=Filters, FilterIndex:=Index

Assign the filename along with its full initial path (which must exist on 
the drive) to the FileNameWithPath variable (note that the specified path, 
if it exists, will be what the folder tree opens to); assign a comma 
delimited string (with **no** spaces around the commas) to the Filters 
variable where the list is in pairs... the first item is the text that is 
displayed in the "SaveAs File Type" drop down and the second item paired 
with it restricts the display to the specified file extension, repeat this 
pattern for the second an subsequent file specifications that you want to 
predefine for the user; and assign a number corresponding the filter that 
you want to be defaulted (so in my example, the 2 means use the second 
filter pair as the default, which is the Text Files one).

-- 
Rick (MVP - Excel)


"cinnie" <cinnie@discussions.microsoft.com> wrote in message 
news:5CAB7045-E4AA-43F2-95F4-AF25801C705D@microsoft.com...
> thanks Gary's Student
>
> This indeed saves the file as requested, but is there any way to get the
> Windows 'Save As'  dialog box to open with the File Name box showing the 
> name
> that your code fragment produces?  This way the user can change the folder 
> if
> desired.
>
> Many thanks for any thoughts
> -- 
> cinnie
>
>
> "Gary''s Student" wrote:
>
>> Sub newdate()
>> Dim d As Date, s As String
>> d = Range("J1").Value
>> s = Format(d, "yymmdd")
>> ActiveWorkbook.SaveAs Filename:="POS_Summary_" & s & ".xls"
>> End Sub
>>
>> -- 
>> Gary''s Student - gsnu200909 

0
Rick
12/4/2009 8:58:33 AM
Here is a more practical (and usable) example...

'  Put this line with your other variable declarations
Dim FileNameWithPath As String, Filters As String
Dim SaveTo As String, Index As Long
.......
.......
'  Put these lines in your code wherever needed
FileNameWithPath = "c:\Temp\RicksSaveAsTest.xls"
Filters = "All Files (*.*),*.*,Excel Files (*.xls),*.xls"
Index = 2
SaveTo = Application.GetSaveAsFilename(InitialFilename:=FileNameWithPath, _
                                FileFilter:=Filters, FilterIndex:=Index)
ThisWorkbook.SaveAs SaveTo

The GetSaveAsFilename dialog doesn't actually save anything, it simply gets 
the filename and its path from the user, hence the last line is needed to 
perform the actual save operation.

-- 
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:usGaj$LdKHA.5796@TK2MSFTNGP06.phx.gbl...
> Give this code a try inside your macro...
>
> '  Put this line with your other variable declarations
> Dim FileNameWithPath As String, Filters As String, Index As Long
> ......
> ......
> '  Put these lines in your code wherever needed
> FileNameWithPath = "c:\Temp\NewTextFilename.txt"
> Filters = "All Files (*.*),*.*,Text Files (*.txt),*.txt"
> Index = 2
> Application.GetSaveAsFilename InitialFilename:=FileNameWithPath, _
>                              FileFilter:=Filters, FilterIndex:=Index
>
> Assign the filename along with its full initial path (which must exist on 
> the drive) to the FileNameWithPath variable (note that the specified path, 
> if it exists, will be what the folder tree opens to); assign a comma 
> delimited string (with **no** spaces around the commas) to the Filters 
> variable where the list is in pairs... the first item is the text that is 
> displayed in the "SaveAs File Type" drop down and the second item paired 
> with it restricts the display to the specified file extension, repeat this 
> pattern for the second an subsequent file specifications that you want to 
> predefine for the user; and assign a number corresponding the filter that 
> you want to be defaulted (so in my example, the 2 means use the second 
> filter pair as the default, which is the Text Files one).
>
> -- 
> Rick (MVP - Excel)
>
>
> "cinnie" <cinnie@discussions.microsoft.com> wrote in message 
> news:5CAB7045-E4AA-43F2-95F4-AF25801C705D@microsoft.com...
>> thanks Gary's Student
>>
>> This indeed saves the file as requested, but is there any way to get the
>> Windows 'Save As'  dialog box to open with the File Name box showing the 
>> name
>> that your code fragment produces?  This way the user can change the 
>> folder if
>> desired.
>>
>> Many thanks for any thoughts
>> -- 
>> cinnie
>>
>>
>> "Gary''s Student" wrote:
>>
>>> Sub newdate()
>>> Dim d As Date, s As String
>>> d = Range("J1").Value
>>> s = Format(d, "yymmdd")
>>> ActiveWorkbook.SaveAs Filename:="POS_Summary_" & s & ".xls"
>>> End Sub
>>>
>>> -- 
>>> Gary''s Student - gsnu200909
> 

0
Rick
12/4/2009 9:13:40 AM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Any XML Naming Conventions?
Does the use of DTD, XML Schema and similar constructs adopt the use of C# naming conventions? If so how do I make the distinction of how to apply C# conventions with XML elements, attributes and so on? Any referrals to resources that discuss or document XML Naming Conventions? -- <%= Clinton Gallagher, "Twice the Results -- Half the Cost" Architectural & e-Business Consulting -- Software Development NET csgallagher@REMOVETHISTEXTmetromilwaukee.com URL http://www.metromilwaukee.com/clintongallagher/ clintonG wrote: > Does the use of DTD, X...

Exporting file names to an excel spreadsheet.
I have a folder containing many many many .pdf files. Is there a way to export the filenames of these .pdf files into an excel spreadsheet? If this is a dumb question, I apologize. I didnt have any luck searching for the answer here. -- Kepf ------------------------------------------------------------------------ Kepf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30611 View this thread: http://www.excelforum.com/showthread.php?threadid=547520 I use Jim Cone's fine Add-in called "ListFiles"for this sort of thing. It's available free (a...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Publisher file made in Publisher 2007 won't open in 2007
I produced an 8.5 x 11 brochure two days ago in Publisher 2007. I was able to save, resave and reopen the file many times during the day. When I tried to open the file today, I get the "Publisher cannot open the file" error message. I've tried to open the file on two different machines with Publisher 2007 and get the error message both time. One has XP Pro, the other has Visa Home Basic. It's a 1.6 meg file. I have a smaller publisher file of a bumper sticker (40 kb) which was made a couple of weeks ago which opens with no problem. This is the second time I ha...

Multiple PST Files #2
I am running Outlook 2003 and would like to have Outlook use two different .pst files (one for my work stuff and one for home) on the same computer. Is it possible to do this? If so, can I can I configure one of the pst files to get e-mail from one account, while the other pst file is configured to get mail from the other account? Thanks! -D Yes, you can create multiple PSTs. I keep mine stored in the same file location but with different names. Depending on which version of Outlook you run, you can creat a new pst from file/new/Outlook Data File (I run Outlook 2003 but had multiple P...

Money 2006 file repair application error
I cannot delete an old account because I get a "file may be courrupt" error so I am trying to repair my 2006 file. Following the deirections, when the progress bar almost completes, I get an application exception and send an error report to MS. My event log has 2 app errors 1) Event Type: Error Event Source: Application Error Event Category: None Event ID: 1001 Date: 4/12/2006 Time: 10:47:26 PM User: N/A Computer: xxx Description: Fault bucket 02266399. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 42 75 63 6b 65 ...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Application hanging on file load...
Okay, I'm having the strangest problem, and I'm hoping somebody here will be able to give me an idea of how to attack it... I have an MFC application that does some networked communication, multi-threading, fun stuff... I also load and save xml configuration files. Now, on most of the computers I've run my software on, I have absolutely no problems. On SOME boxes, however, whenever I use my menu to try to load or save a file... before the File Dialog box even comes up... the application hangs. All the boxes I've tried this on have been Windows 2000 boxes with Service Pack 4....

can't open files
I have Microsoft Office 2003 ~ all of a sudden when I go to my documents and try to open a file, I get this message "problem sending command to the program". Error 0646 and dt000 See the article "How to re-register Word when problems crop up opening files” at: http://www.word.mvps.org/FAQs/AppErrors/ReRegisterWord.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Lost in Vegas" <Lo...

cell looses name after sorting
Can someone help me with the following problem in Excel 2000: in a table I have attached serveral cells with unique cell names, the values in these cells are used in other sheets. the problem is that when I sort the table, the cell names stay in the original rowposition; they are not sorted! while their values are. So Cell names get different values, and other calculations on my other sheets get messed up! How can I make the cell names relative instead of absolute? thankx in advance, Jim --- Message posted from http://www.ExcelForum.com/ "jimfx >" <<jimfx.109zcv@exc...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Change License / Company name
Hi, we tried CRM 1.2 with the MSDN license key. Now my company bought a 25 user Proffesional Suite license and I want to change the key on the production server. Is there an easy way to do this?? The only solution I see, is via the Data Migration Framework. Erik. How about the re-deploy tool? "Erik" <none> wrote in message news:u0SXdbXlEHA.3452@TK2MSFTNGP15.phx.gbl... > Hi, > we tried CRM 1.2 with the MSDN license key. Now my company bought a 25 > user > Proffesional Suite license and I want to change the key on the production > server. Is there an easy wa...

Comparing first and last names in two lists
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

SCL Settings
Does Microsoft has a recommended configuration for the Intelligent Message Filter? My recommendation is to start by only blocking messages with the highest SCL (so gateway setting of 9). Then pick something conservative like SCL = 7 to move to junk folder. Observe the results yourself (make sure you install the form that exposes the SCL score of messages), and over time decrease the value to something that makes sense for you and your users. -GT "Mark Castelli" <mcastelli98@yahoo.com> wrote in message news:eebQAL0CGHA.312@TK2MSFTNGP09.phx.gbl... > Does Microsoft...

Clearing security settings
I am setting up a new server that will be home to GP version 8. I am migrating from 7.5 to 8. I want to clear out all users, advanced security, and standard security settings and recreate them on the new server. Anybody ever scripted this? I assume I want to truncate tables, but I don't know which ones, other than what is show in techkno Steps to remove Advanced Security (874401). TIA If you run a delete statement on the SY02000 table, it will remove all settings, then you can run a reconcile against security restrictions and user access. "Cindy Lange" wrote: > I...

Programmatic access settings disabled
Hi, How I can enable the access to change the settings for the programmatic access in Outlook 2007. I tried to change the setting with [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Outlook\Security] "ObjectModelGuard"=dword:00000002 without any success. The settings stays to "Always warn" and can't be changed. We are usiing Windows 7 with McAfee 8.7i. Windows doesn't recognize it in the Action Center. You'll have to update McAfee 8.7i to at least Patch 2 for it to integrate with the Windows 7 Action Center. I believe Patch 3 has been out a...

I can no longer convert my files to pdf
I have Publisher 2003 and yesterday, my pdf menu on the top left disappeared and I can no longer convert my Publisher files to pdf. I've even uninstalled/reinstalled the Adobe Acrobat 7 and have also updated to 7.8 but still no luck. The next step was to uninstall and reinstall Publisher and still no luck. Any one know what may be the problem? Thanks. How are you trying to convert your files? When happens when you try? -- JoAnn Paules MVP Microsoft [Publisher] "darrellm" <darrellm@discussions.microsoft.com> wrote in message news:A78B7B52-981D-4FB7-8625-EAD9...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Backup Excel file
I want to take backup of a file every time. I am using - save as - tools - general option - always create back up. The backup file is in the same location but I want to put it in a different folder. What should I do, kindly suggest. Thanks Jan Karel Pieterse's has an addin called AutoSafe (note spelling). http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it's done, it either deletes these backups (or puts them...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...