question about a code to "save as"

i have a command button on a spread sheet that, when clicked, will sav
the spread sheet in a folder and name it, depending what is in certai
cells. my problem is where i want it saved.

right now, i have all spread sheets being saved into one certai
folder, but i would like the spread sheet to be saved into a folde
that is specified in cell d2.

this is the code i have now.

Sub Save_As_FileName()
FName1 = Range("d2").Value
FName2 = Range("d3").Value
FName3 = Range("d5").Value
FName4 = Range("d6").Value
Fname5 = Range("d7").Value
pth = "f:\bids\"
ActiveWorkbook.SaveAs Filename:=pth & FName2 & "  " & FName3 & "  " 
FName4 & "  " & Fname5 & "  " & FName1 & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
End Sub


I have tried changing the pth="f:\bids\" to many different things an
can't seem to figure it out.

any help? Thanks

--
Message posted from http://www.ExcelForum.com

0
6/23/2004 9:06:12 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
240 Views

Similar Articles

[PageSpeed] 15

Sometimes Excel does not seem to like doing too much work in one go. Tr
this :-


Code
-------------------
  MyFileName = FName2 & " " & FName3 & " " & FName4 & " " & Fname5 & " " & FName1 & ".xls"
  ActiveWorkbook.SaveAs FileName:=MyFileName
 
-------------------


This will also enable you to check in the debugger that the file nam
you are supplying is valid ;

--
Message posted from http://www.ExcelForum.com

0
6/23/2004 9:17:22 AM
i see how that would create the file name, but i am not understandin
how that will save the workbook into a file designated by the text tha
is typed into cell d2.

i would still need the      pth="f:\bids\"   ?

in f:\bids i have 4 folders...  bdg, ear, gjg and jpr (sales re
intials)
in cell d2 one out of the four is typed.

i would like the workbook saved into one of those folders when th
command button is clicked

--
Message posted from http://www.ExcelForum.com

0
6/23/2004 9:56:27 AM
Sorry ... add the path at front as you do already :-

MyFileName =pth & FName2 .. etc

--
Message posted from http://www.ExcelForum.com

0
6/23/2004 6:17:55 PM
I've never seen excel fail in code like yours.  But I have failed to ensure that
there was good stuff in those cells.

I'd use BrianB's technique and then add a line like:

	msgbox myfilename

right before the saveas command.  Then I could see if I'm getting the ranges
from the correct worksheet.

I think I'd be a little more careful here:

with worksheets("sheet1")
  FName1 = .Range("d2").Value
  FName2 = .Range("d3").Value
  FName3 = .Range("d5").Value
  FName4 = .Range("d6").Value
  Fname5 = .Range("d7").Value
end with

(My guess is you're getting the values from the wrong worksheet.)

"Zygoid <" wrote:
> 
> i have a command button on a spread sheet that, when clicked, will save
> the spread sheet in a folder and name it, depending what is in certain
> cells. my problem is where i want it saved.
> 
> right now, i have all spread sheets being saved into one certain
> folder, but i would like the spread sheet to be saved into a folder
> that is specified in cell d2.
> 
> this is the code i have now.
> 
> Sub Save_As_FileName()
> FName1 = Range("d2").Value
> FName2 = Range("d3").Value
> FName3 = Range("d5").Value
> FName4 = Range("d6").Value
> Fname5 = Range("d7").Value
> pth = "f:\bids\"
> ActiveWorkbook.SaveAs Filename:=pth & FName2 & "  " & FName3 & "  " &
> FName4 & "  " & Fname5 & "  " & FName1 & ".xls", _
> FileFormat:=xlNormal, CreateBackup:=False
> End Sub
> 
> I have tried changing the pth="f:\bids\" to many different things and
> can't seem to figure it out.
> 
> any help? Thanks!
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/23/2004 11:23:56 PM
Reply:

Similar Artilces:

'Save As' in Excel
Greetings, I would like to create an Excel file from within another file, I want it to act like this. I want to open a specific file (we will call it 'myfile') and from within 'myfile' I want to save the current file as another called 'mynewdate' when the 'Save As' is selceted from the File menu. I want the new file to use the current date as a default file name in the format of '/mmddyyyy'. So here is the scenario;open myfile, save as (diaglog opens) mmddyyyy is already in filr name box. How can I do this? Thanks -- Please reply to newsgroup so...

When "Save As" is selected
Okay....if a user selects "Save As" from the menu, I would like to display a message that states "Save As is disabled!" I pretty much want to prevent the "Save As" menu option from being selected. Is this gonna be possible??? thank you everyone Robert, Try this, Alt+F11 to open VB editor and double click 'ThisWorkbook' and paste the code in on the right Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then MsgBox "Save As isn't allowed" Cancel = True End If End Sub Mike ...

MFC bug? Save As dialog crashes in old style
Hi! I have to use an old style Save As dialog under XP with the VC++7.0 compiler but the dialog throw an access violation if the filter (Save file as type) is opened. The same code works well with VC++6.0. If the OFN_EXPLORER bit is omited (m_ofn.Flags &= ~OFN_EXPLORER), the dialog always freezes. However, everything is OK if this line is in comment. The HOOK procedure doesn't solve the problem, either. It seems to be an MFC bug. Thanks for the support in advance. Best regards, Ferenc Gratzer Ps.: The main part of the code: IMPLEMENT_DYNAMIC(CDsbFileDialog, CFileDialog) UINT...

Excel crashing on save as
I have an excel file with a lot of sheets in it and it is 5.44 mb I have been working on this project for a long time and after making changes I have been doing save as. Now, I am able to do a save, but not a save as, it says excel has generated errors and it is generating and error log. Is there a limit to spreadsheet size, number of sheets or something? Is there a way to repair the file? Sometimes opening and saving in a different (newer?) version may help. Sometimes not. If that didn't help (or if it's just one workbook), maybe opening the workbook in OpenOffice and sa...

Save As option
I'm working in Word 2003. When I open some files and try to save as a different name, the "SAve As" option is not available available. Only the Save option. How do I make save as appear under File ? Try this: Right click on the "file...edit...." toolbar| Click Customize at bottom | Go to tab "Commands" | scroll down Commands and find "Save As" | Drag the button up to File and put it where you want. "KP" wrote: > I'm working in Word 2003. When I open some files and try to save as a > different name, the "...

Excel "save as" function.
Microsoft excel XP will not let me save a document using the "Save as". How do I get this fucntion working? What happens when you try to use the Save As? Error message? Alert message? Nothing? Any other functions that don't appear to be working? Specifically File>Open? Read this google search result and see if anything applies. http://snipurl.com/ep4l Gord Dibben Excel MVP On Thu, 5 May 2005 03:38:02 -0700, help me please! <help me please!@discussions.microsoft.com> wrote: >Microsoft excel XP will not let me save a document using the "Save as"...

Why doesn't Save as work over the network?
Since Service Pack 2 was installed on my XP machine I can no longer do a Save as function over my internal network on to my Windows 98 machine. Nothing else has changed. Why? hi, not sure but guessing the the built in firewall. what to do? not sure i don't have sp2 yet. >-----Original Message----- >Since Service Pack 2 was installed on my XP machine I can no longer do a Save >as function over my internal network on to my Windows 98 machine. Nothing >else has changed. >Why? >. > Thanks. That sort of confirmed what I thought but I can't find a way round i...

Excel 2003 Save as web
:confused: Background: 1- I created a workbook using the Excel 2000 (Office 2000 SP3 OS:Wi 2000). 2- The workbook contains several sheets which are used to displa numerical data and charts. 3- The data is been collected/imported from .DAT files using VB/Macr which also creates/add new sheets and charts. And finally saved a Web. 4- Every thing works fine when using Excel 2000. And the created we result are working beautifully. Problem: When jag try to use the same workbook in Excel 2003 (Office XP SP OS:XP) the problem appears. The Excel seems at save as web for som strange reason stop res...

Print quote + export to word + save as + create note w/attach
Hi all, I would like to know if anybody has already done something similar, even using some (cheap) commercial program. The main goal is to add a button on the quote form (as explained previously in this group) that should: - invoke Crystal Report in a "silent mode" passing some parameters (quote ID, folder name to say where to put the exported report, etc.) and telling CR to export the report in Ms Word format to the folder specified; - create a note with the Word file attached. I am sure the last step is very easy, but I cannot figure out about the first one. I think that edi...

Macro for Save As
I am looking for help with a macro that will run save as and place the file in the same path as the file being saved as and adding a back to the end of the file before .xls. Origional file B:\One\Two\Origional.xls Save as file B:\One\Two\Origionalback.xls Thank you In principal, thsi is waht you are looking for: ================== Sub SaveAsBAckUp() NFN = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "BACK" FULL = ActiveWorkbook.Path & "\" & NFN & ".XLS" ActiveWorkbook.SaveAs FULL End Sub =============...

Outlook Canedar... Save as a Web
Hello, I need Help exporting my Outlook Calendar to FrontPage. With the outlook that comes with Office 2000, it was easy. I simply export the month range by doing a "Save as a Web". This can then be brought into my FrontPage so it can be displayed as a web on my website. Current: Now I'm using OfficeXP. When I click "Save as a Web" and input my date range, the output seems to include an extra column on the right side, a split screen, that displays "Appointments and Event Details" . I don't want this split screen. I don't want the appointment deta...

Slow File|Save As
For no particular reason I've noticed that my Word 2003 and Excel 2003 (WinXP Home) now produce long (10 sec) delays when I use the File |Save As command. Suppose I'm trying to reach a nested folder. As I click each successive sub-folder, it seems that some will instantly produce the next nested level, while others only do so after a delay, during which nothing appears to be happening. If I drill down to a subfolder nested five or so levels deep, it almost seems as though every other sublevel behaves normally and the rest with the delay. Any possible explanations? -- PT ...

how to change Save As default directory?
Hi. Each time I want to save a new spreadsheet the default directory shown is always My Documents. What needs to be done in order to change it to Desktop? Thanks for all help. OS = Windows 2000 Professional Hi excel2000! Try: Tools > Options > General Change the default file location. But your desktop is sure going to get crowded over time. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. ...

Macro to "save as"
I want to make a macro that will "save as" my exel file using a cell content for the file name.(using copy-paste When i do this, it copies the cell content into the macro as text, and so when the cell content changes for the next "save as" the text in the macro does not. Happy Holidays Below is what is writen in the macro, as seen in the macro editor. The number in quotation marks, is the value that was in cell F3. If I change le last digit from a 5 to a 6 in F3 and run the macro again, it does not look at the new number in F3, and tries to re-save under the same name ...

Save as webpage
I have created a button to save my spreadsheet as a webpage here is the code when I hit the button: ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _ "C:\WINDOWS\Personal\Emailed Proposals\Page.htm", "FRONT", "", xlHtmlStatic, _ "Proposal for XL_16777", "").Publish (True) ChDir "C:\WINDOWS\Personal\Emailed Proposals" Range("K1").Select It works great!! The only problem is it saves as the same name everytime Page.htm. In the Proposal in cell (n2) it has the Proposal number. I would like to name it using this number. Is ...

Save as is disappeared
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, the option &quot; save as&quot; has disappeared in the menubar at archive. I see only save or close. And I miss the extra toolbar in the menu with symbols for save etc. This is the second time I know it is simple to reset but how?.. Can anyone tell me where I can find a good manual for office 2008 for Mac? Try going to edit > customize > menubars and toolbars. The hit reset. Note if you had any special menu setups you will have re-customize them after the reset. Joosje@officeforma...

save as Access 97 mdb..
I'm using Access 2002, and looking to save an mdb as 97. When I saved as, it said i needed to set references while IN access 97. Is that correct? I can't 'save as' unless I have access 97? Any workarounds to this? Thanks! Don > I'm using Access 2002, and looking to save an mdb as 97. When I saved as, it > said i needed to set references while IN access 97. Is that correct? Yes. References ALWAYS need to be checked when converting between versions of Access. For example, just look at the reference (2002) "Microsoft Access 10.0 Object Library", which in ...

"Save as" dialog hanging on certain network folder
I am trying to troubleshoot a very small business network. There is a folder shared from a Windows XP Pro machine, and when initiating a save as dialog to this location, many office applications will hang when it appears to be loading the contents of the folder. I have tested Excel and Word, versions 2000 and 2003. Navigating to the folder in explorer works just fine. I thought rebooting would help, but the issue appears to effect the machine even after a fresh boot. Some machines on our network behave correctly. Others will hang for a short while but eventually load the conte...

Additional file with no extension created during File Save As proc
Good morning all! I originally posted this query on the Developer board, as I thought it was a problem with my code, but now I'm not so sure. I have a workbook with a macro that runs a Save As within a loop, to create test data files. I was finding that for every new file I created, and additional file with a filename comprisiong eight alphanumeric characters and no file extenson was created in the same folder. Thinking that the workbook might be corrupted, I created a new workbook (with NO copying/pasting from the original - everything types in from scratch) , created new code m...

Save as a picture
I have a group of cells that contain names and numbers. Column A has the name and column be has there score Is there anyway of being able to select those cells only and save them as a picture. I have tried to copy them and paste them into the Paint program and save as a picture but it doesnt copy the exact formatting over. Is there another alternative?? Thanks Andrew Andy Pope has a free add-in that you can use to export images: http://www.andypope.info/vba/gex.htm Andrew wrote: > I have a group of cells that contain names and numbers. > > Column A has the name and...

how do i save as a dbf file from access 2007
I have a need to be able to edit and save as a dbf file from access 2007. any ideas?? ...

cells not right when save as html
When I save my workbook as an html page the formatting on my 45 degree cells at the top of the page are no longer at a 45 degree angle. Is it possible for this formatting? ...

Grid lines (save as web page)
I have just upgraded from Excel 97 to Excel 2003. Using Excel 97, when I saved a sheet in htm format, the htm file had grid lines. However, when I use Excel 2003 and "Save as Web Page, there are no grid lines in the htm file. How can I save a htm file with grid lines? Ralph Excel has gone through several evolutions with regard to gridlines. When Excel was at 97 'Save as HTML' menu option used to add 'borders' to the table it created. In later versions this was dropped as the 'gridlines' do not really 'exist' as a drawing object in Excel. To get t...

Pressing rhe Print button brings up a "Save As" dialog box.
I'm using Win'XP and Office 2003. Pressing the print button in Excel brings up a "Saved As" dialog box. Rebooting doesn't change a thing. Following through on the "Save As" dialog box doesn't lead anywhere. Been using Excel for months and never saw anything like it. What am I doing wrong? Please help. Don J -------------------------------------------------------------------------------------------------- You got a few replies when you first posted this. Don J wrote: > &g...

Repeatable Excel Crash
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I've got a workbook that has a sheet I'd like to save as a webpage for sharing a report. However, every time I attempt to save the sheet as a webpage Excel crashes, throws the crash reporting dialog, and then restarts. <br><br>The workbook is a &quot;.xlsx&quot; format file (the .xlsb format has proven to be so unstable as to be unusable with constant crashes.). There are 16 worksheets with a about 2-3 thousand raw data cells and another 2-3 thousand formula cells. The sheet in quest...