Macro to "save as"

Hi,

Am using excel 2003 and am wanting to create a macro, attached to a button, 
that will save as a particular cell #, say A2, and then print.  Have the 
print down pat, but can't figure out the save as part.......
-- 
Thanks for the help
0
JS1 (36)
8/27/2009 11:22:01 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
582 Views

Similar Articles

[PageSpeed] 22

What is in cell A2? Try things like these, depending on what you have and what you want to do....

ThisWorkbook.SaveAs Range("A2").Value & ".xls"
ThisWorkbook.SaveCopyAs Range("A2").Value & ".xls"
ThisWorkbook.SaveAs "C:\Folder Path\" & Range("A2").Value & ".xls",  AddToMru:=True
ThisWorkbook.SaveAs "C:\Folder Path\FileBaseName " & Format(Range("A2").Value, "yy-mm-dd") & ".xls"

HTH,
Bernie
MS Excel MVP


"JS" <JS@discussions.microsoft.com> wrote in message 
news:64BE309A-6B6E-4FEA-B810-727AC9971F19@microsoft.com...
> Hi,
>
> Am using excel 2003 and am wanting to create a macro, attached to a button,
> that will save as a particular cell #, say A2, and then print.  Have the
> print down pat, but can't figure out the save as part.......
> -- 
> Thanks for the help 


0
Bernie
8/27/2009 1:04:58 PM
Hi Bernie,

Thanks for your response.  I enter a number into cell A2, and want excel to 
save the worksheet as that number, and leave the original as the template.
-- 
Thanks again for the help


"Bernie Deitrick" wrote:

> What is in cell A2? Try things like these, depending on what you have and what you want to do....
> 
> ThisWorkbook.SaveAs Range("A2").Value & ".xls"
> ThisWorkbook.SaveCopyAs Range("A2").Value & ".xls"
> ThisWorkbook.SaveAs "C:\Folder Path\" & Range("A2").Value & ".xls",  AddToMru:=True
> ThisWorkbook.SaveAs "C:\Folder Path\FileBaseName " & Format(Range("A2").Value, "yy-mm-dd") & ".xls"
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "JS" <JS@discussions.microsoft.com> wrote in message 
> news:64BE309A-6B6E-4FEA-B810-727AC9971F19@microsoft.com...
> > Hi,
> >
> > Am using excel 2003 and am wanting to create a macro, attached to a button,
> > that will save as a particular cell #, say A2, and then print.  Have the
> > print down pat, but can't figure out the save as part.......
> > -- 
> > Thanks for the help 
> 
> 
> 
0
JS1 (36)
8/28/2009 9:35:01 AM
JS,

Then try using this - it will save in the same folder as the original template:

ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & Range("A2").Value & ".xls"

HTH,
Bernie
MS Excel MVP


"JS" <JS@discussions.microsoft.com> wrote in message 
news:E003FF78-AB8F-49A7-82AC-F7811542518A@microsoft.com...
> Hi Bernie,
>
> Thanks for your response.  I enter a number into cell A2, and want excel to
> save the worksheet as that number, and leave the original as the template.
> -- 
> Thanks again for the help
>
>
> "Bernie Deitrick" wrote:
>
>> What is in cell A2? Try things like these, depending on what you have and what you want to do....
>>
>> ThisWorkbook.SaveAs Range("A2").Value & ".xls"
>> ThisWorkbook.SaveCopyAs Range("A2").Value & ".xls"
>> ThisWorkbook.SaveAs "C:\Folder Path\" & Range("A2").Value & ".xls",  AddToMru:=True
>> ThisWorkbook.SaveAs "C:\Folder Path\FileBaseName " & Format(Range("A2").Value, "yy-mm-dd") & 
>> ".xls"
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "JS" <JS@discussions.microsoft.com> wrote in message
>> news:64BE309A-6B6E-4FEA-B810-727AC9971F19@microsoft.com...
>> > Hi,
>> >
>> > Am using excel 2003 and am wanting to create a macro, attached to a button,
>> > that will save as a particular cell #, say A2, and then print.  Have the
>> > print down pat, but can't figure out the save as part.......
>> > -- 
>> > Thanks for the help
>>
>>
>> 


0
Bernie
8/28/2009 2:45:05 PM
Hey Bernie,

Thanks for all of your help!  Works perfectly.
-- 
Cheers

"Bernie Deitrick" wrote:

> JS,
> 
> Then try using this - it will save in the same folder as the original template:
> 
> ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & Range("A2").Value & ".xls"
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "JS" <JS@discussions.microsoft.com> wrote in message 
> news:E003FF78-AB8F-49A7-82AC-F7811542518A@microsoft.com...
> > Hi Bernie,
> >
> > Thanks for your response.  I enter a number into cell A2, and want excel to
> > save the worksheet as that number, and leave the original as the template.
> > -- 
> > Thanks again for the help
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> What is in cell A2? Try things like these, depending on what you have and what you want to do....
> >>
> >> ThisWorkbook.SaveAs Range("A2").Value & ".xls"
> >> ThisWorkbook.SaveCopyAs Range("A2").Value & ".xls"
> >> ThisWorkbook.SaveAs "C:\Folder Path\" & Range("A2").Value & ".xls",  AddToMru:=True
> >> ThisWorkbook.SaveAs "C:\Folder Path\FileBaseName " & Format(Range("A2").Value, "yy-mm-dd") & 
> >> ".xls"
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "JS" <JS@discussions.microsoft.com> wrote in message
> >> news:64BE309A-6B6E-4FEA-B810-727AC9971F19@microsoft.com...
> >> > Hi,
> >> >
> >> > Am using excel 2003 and am wanting to create a macro, attached to a button,
> >> > that will save as a particular cell #, say A2, and then print.  Have the
> >> > print down pat, but can't figure out the save as part.......
> >> > -- 
> >> > Thanks for the help
> >>
> >>
> >> 
> 
> 
> 
0
JS1 (36)
8/29/2009 11:27:01 AM
Reply:

Similar Artilces:

Save As problem
I'm completely new to MFC.. so please excuse my ignorance.. I used the wizard to make a simple app.. Toolbars, menus etc. So i've got a few classes, the ones im interested in are CMapEditorApp and CMapEditorDoc. In CMapEditor.cpp I created all the D3D stuff, which renders my level, and allows it to be edited. It all works fine. The problem comes when I want to do a Save as - I have to use CMapEditorDoc to get the filename returned by the Save As dialogue.. But I can't think of a way of passing this name back to CMapEditorApp for it to use the save function.. I can't just p...

Save as
I generated a file in Excel 1997 then worked on it on another computer that Excel 2007. Now when I go to access the file in the 1997 version the formulaes in the cells don't incorporate any changes I make. I am presuming your workbook is on a server. Are you saving the file as an Excel 2007 workbook or as an Excel 97-2003 workbook? You can have both in the same folder - for example: MyWorkBook.xls (Excel 97-2003) and MyWorkBook.xlsx (Excel 2007). Notice the different extensions. If you open your 1997 workbook, make changes and save it as a 2007 workbook, you won't have th...

Publisher 2003 -Save As.. Defalt to 2000
Hi, I hope someone can help. I need to get publisher 2003 to always save in publisher 2000 format. The reason for this is that we have loads of computers running publisher. Most of them run publisher 2000 and a few new ones run publisher 2003. All of the users need to be able to access each others documents but when a 2003 user creates/opens a document and saves it again, 2000 users can no longer open them. The only solution is to get the user to save as.. and select publisher 2000. This is great if the users remembered to do it! Any suggestions would be very much appreciated. I know th...

excel 2002 to 2003- macro doesnt save as xls
PC was rebuilt at work, so new harddrive and new software. This saved as .xls in EXCEL 2002, BUT NOT IN 2003!!!??? ChDir "H:\MY DOCUMENTS ON H DRIVE" ActiveWorkbook.SaveAs Filename:= _ "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets (1).Range("k3").Value & "", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Bob - What's in Worksheets(1).Range("k3")? The command you show saves in Excel workbook format. Are you sure ...

Save as msg file with proper file (date) attributes
Hi there, I would like to save outlook mail items as msg files. If I do so using drag & drop to the explorer the date&time attributes of the created msg file will have the value of the current date & time (creation date&time of the file). I would like to have them set with the values from within the mail item. ==> Date sent or received. How can this be accomplished? Are there any tools wich can alter the file atrributes in this way automatically? Thank you for your help Lex ...

How can I resize/restore the File > Save As window?
Somehow I have managed to expand the File | Save As window such that it now fills the whole screen. This only affects Excel (2007). There is no minimise button, and I cannot see the bottom RH corner to grab it and resize the window and I cannot get hold of any edge of the window to move it. How can I resize/restore the File > Save As window? TIA. Chris. Try double-click on the Title Bar Gord Dibben MS Excel MVP On Fri, 4 Dec 2009 16:18:15 -0000, "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote: >Somehow I have managed t...

Outlook Save as a Browser???
I've been toying with using MS Outlook as a browser instead of MS Explorer. I have a situation where the user, uses MS Outlook extensively but only searches the internet occassionally. After showing her how to use MS Outlook as a web browser she asked if it's safe. So.... My question is how safe is MS Outlook compared to Explorer with respect to security? Outlook is a PIM, not a browser. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head...

The SAVE AS button does not show save to G: drive
the SAVE AS does not recognize another drive I assume you clicked on the "Save In" dialog box dropdown and could not see any other drive. This would not be an Excel issue IMO. Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 14:49:03 -0800, Marilyn S <Marilyn S@discussions.microsoft.com> wrote: >the SAVE AS does not recognize another drive I said SAVE AS not "IN" Excel 2007 does not show a SAVE INbutton > > On Tue, 15 Jan 2008 14:49:03 -0800, Marilyn S <Marilyn > S@discussions.microsoft.com> wrote: > > >the SAVE AS does not recognize...

save as isnt there neither is the save button pressable
hi my speradsheets will not let me save as or click the save button only way i can save is by ctrl +s i reallly need my save as to come back!!! SORT ME OUT Your best bet is to reset the buttons that seem to have got los somehow. Go to TOOLS, CUSTOMISE and select the tab COMMANDS. Go to FILE and i the right hand pane select the button you want and DRAG it across ont your toolbar and drop it. You can do this with any of the other button you like the look of. When you're through, close the dialog box an everything should be beck to normal Jo -- Jon Quixle ------------------------------...

excel save as *.prn
I work with Windows 2000 and office 2000. When I want to save as, I don't have the choice *.prn in file type. File>Print>Print to File. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "lmartinbdg" <anonymous@discussions.microsoft.com> wrote in message news:0a7f01c39717$bdc879a0$a301280a@phx.gbl... > I work with Windows 2000 and office 2000. > When I want to save as, I don't have the choice *.prn in > file type. I don't think that this'll do what the OP wants. This includes all the formatting codes that would get sent to ...

save as report button
Greetings, If Access 2000 there was a button I could add to the form toobar that would save the form as a report. I can't find it in Access 2007. Please help! You would have to build the report the use the button to open the report and then print it. Otherwise, you are just printing the FORM not the REPORT. -- Milton Purdy ACCESS State of Arkansas "Joseph W Card" wrote: > Greetings, > > If Access 2000 there was a button I could add to the form toobar that > would save the form as a report. I can't find it in Access 2007. Please he...

Can't "Save As" a new filename
Hi. I'm using MSOffice97 on a WindowsXP computer. I can't create a new workbook by using the File/SaveAs menu command. When I try, the error message says [paraphrased]: idmxl8.xla could not be found. Check the spelling & the path, etc. True -- Explorer can't find that file name either on this computer or on the old one, where "SaveAs" still works. I've tried uninstalling and re-installing both Excel and all of Office several times, to no avail. After using Excel for so many years, I use it for practically everything, and the only work-around I've ...

CFileDialog
Is is possible to have automatic "Confirm Save As" message boxes to open centered over the CFileDialog? You would have to subclass CFileDialog, and add an OnOK handler (virtual method, not the OnBnClickedOK handler). If the user fails to confirm, return immediately instead of calling the CFileDialog::OnOK handler. joe On Sun, 28 Mar 2010 12:38:21 -0700, ab` <ab@absent.com> wrote: >Is is possible to have automatic "Confirm Save As" message boxes to open >centered over the CFileDialog? Joseph M. Newcomer [MVP] email: newcomer@flounder.com ...

save as text question
I have a spreadsheet that is comprised of a single column but thousands of rows. The width of each cell is 80 characters. I am saveing the file as text (MS-DOS). The problem that I encounter occurs when the cell contains a comma. The resulting text file inserts double quotes at the beginning and end of the line that contained the comma. Why does this occur and how do I prevent this? If you want more control on how your text file is created, maybe you can use a macro: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/tex...

Help descriptions in Save, Save As windows
In Office 2004, I've noticed that the Save and Save As dialog windows=20 contain toward the bottom a box with help descriptions that change as = you=20 mouse over various options. Office X had this feature only in the = preferences=20 window. Is there any way to turn this feature off in Office 2004? In article <0dd001c46e9c$27f18630$a301280a@phx.gbl>, "B McNeill" <anonymous@discussions.microsoft.com> wrote: > In Office 2004, I've noticed that the Save and Save As dialog windows > contain toward the bottom a box with help descriptions that change as you ...

Save as picture
Sad to see "save as picture" no longer a right click option in Outlook 2007. Is there a good alternative? Copy and paste results in many more steps and large scrap files. Have you tried using File, Save Attachments? "TonyO" wrote: > Sad to see "save as picture" no longer a right click option in Outlook 2007. > Is there a good alternative? > > Copy and paste results in many more steps and large scrap files. > > > Hi Mary - we meet again! I tried File, Save Attachments. They were greyed out. I wonder if someone has written one o...

My Save As menu is not working
Save As under the file menu in Excel is "grayed out" so I cannot select it. How can I fix this problem Hi, right-click the menubar select Customise from list >> Toolbars click Standard click Reset Button ok - Mark >-----Original Message----- >Save As under the file menu in Excel is "grayed out" so I cannot select it. >How can I fix this problem >. > ...

Save as #6
For some reason I'm not able to save my XL Spreadsheets to Microsoft Excel Workbook (*.XLS) it is not offered in the pull down window as a Save As option. Therefore when I email a spreadsheet the person on the other end cannot open. My Operating system is WinME. Everything I try to save is XLA. I reloaded Professional and still could not see a XLS option. Also I bought this software separate. No other options at all under File>Save As>File Type? Try Tools>Options>Transition "Save Excel Files As" What are you set for there? Change to "Microsoft Excel ...

Excel "save as" problem
Please help! All of a sudden, anytime I do a "save as" in excel, it hangs for abou 3 minutes. It then slowly proceeds to let me path to the correc folder. Any ideas? Mike horethm@bellsouth.ne -- mghorethPosted from - http://www.officehelp.i Two things I'd check............see if Excel is doing a Recalc before Save, and see if you have the Autofilter open on a large database...... Vaya con Dios, Chuck, CABGx3 "mghoreth" wrote: > > Please help! > > All of a sudden, anytime I do a "save as" in excel, it hangs for about > 3 minutes. ...

Outlook 2000 "save as" .MSG is not saving in message format??
All: We're trying to save individual messages from Outlook 2000 SR1 to message format (standard Internet RFC822 format) files. For most of last week it was working properly. File -> Save As, change file type to .MSG, and save the message, and it'd be RFC822-format text. Now, Outlook saves the message as a Word document. WTF? How do I get it to go back to the correct behavior, namely saving the message as an RFC822-format text file instead of a Word document? I'm not aware of anything having been changed, and this is happening on many users' systems. I don't see any...

Save As from chart sheet
I am using Excel 2K/Windows 2K. I have imported a CSV file and created charts on chart sheets. The file is in the CSV format and a chart sheet is active, when I click on the Save As option on the file menu, nothing happens - no dialog box or anything. If I save the file as an Excel file and from a chart sheet select Save As, I get a prompt to save over the existing file. If I use Excel 2003, I do get the Save As dialog box from a chart sheet regardless if the file is CSV or XLS. Is this new to 2003 or should I also be getting the Save As dialog box in Excel 2000? -- maryj Because of ...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Save as Web Page..
Does anyone know how to not include the details on the side. I want to save it as just a monthly calendar in web format but even if I clear the check mark that says show detail it still saves it as a detailed appointment column on the right ...

save as version 2003 problem
I'm working in vba in Access to create and save an Excel file. All's good except that one of the workstations this is runnign on is using Office 2007. I'm developing in 2003 and all the other workstatiosn they have are using 2003. It's very important that the files be saved in 2003 format. When I do this, it runs fine and saves as 97/95 objExcelBk.SaveAs sTempPath & sExcelFileName, xlExcel9795 ','56 = xl 2000/2003 I read online in a forum post that "56" is the correct code for saving as 2003 but that's when the code is written in 20...

where to get "save as" button from excel 2007
as mentioned subject Click the Office icon (round thing in top left corner) Click Excel Options (bottom of dialog) Click Customize on left side of dialog Set "Choose commands from" to "all commands" at top of dialog Select the Save As icon and press the Add button in centre of screen OK out of the dialog Now QAT will have the Save As icon best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "crapit" <biggercrap@yahoo.com> wrote in message news:%23diLXs0rHHA.1296@TK2MSFTNGP06.phx.gbl... > as mentioned subject > ...