Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

File save

  Asked By: Kaua    Date: Dec 06    Category: MS Office    Views: 1060
  

I have a spreadsheet that has a cell that will have a number entered
into it (IE serial number). I want to place a "SAVE" button on the
sheet and have the number saved as the file name. Any assistance would
be appreciated.

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Bradley Evans     Answered On: Dec 06

You have several choices depending on whether you want to save  a copy
of the workbook with a new name while retaining the name of the
current workbook to continue working on, or whether you simply want to
save as and keep on working on the newly named workbook.

Assuming the serial number  is in cell  A1 of the sheet which will have
the save button  on it:
Create this macro in a code module:

Sub blah()
On Error Resume Next
myPath = "C:\Documents and Settings\aUser\My Documents\"
ThisWorkbook.SaveAs myPath & ActiveSheet.Range("A1") & ".xls"
End Sub

making sure the myfolder line contains a valid path.

From the Forms toolbar add a button to the sheet, assign blah() to it.
Edit the text on the button to 'Save'.

To save only a copy as the new name and remain working on the original
substitute SaveCopyAs for SaveAs above.

To prevent checks such as 'A file  named ~ already exists.. ..Do you
want to replace it?' appearing you can add the lines beginning
Application.DisplayAlerts and lose the On Error line viz.:

Sub blah()
Application.DisplayAlerts = False
myPath = "C:\Documents and Settings\aUser\My Documents\"
ThisWorkbook.SaveAs myPath & ActiveSheet.Range("A1") & ".xls"
Application.DisplayAlerts = True
End Sub

This will always overwrite any existing file of the same name.

These are raw solutions with little error checking, so only add the On
Error line after you're sure it works.

 
Answer #2    Answered By: Barak Levi     Answered On: Dec 06

Another question…
This file  is located on a network drive and will be accessed by
several people; I would like to save  it back to the same drive but
with the number  as the file name. The network path
is "\\Main_Plant\Engineering_Services\Engineering\ECR Forms" if this
helps...

I've tried to change the code a few different ways, but I couldn't
get it to save to the network drive. It will only save in "my
documents" on my local pc.

 
Answer #3    Answered By: Rosalie Holmes     Answered On: Dec 06

have you change

myPath = "C:\Documents and Settings\aUser\My Documents\"

into

myPath = "Z:\Main_Plant\Engineering_Services\Engineering\ECR"

Z can be modify but it must represent the networkdrive

 
Answer #4    Answered By: Zachary Larson     Answered On: Dec 06

I realized the reason the paths I was using did not work…it was
because I had dime'd the code (mypath) I got from Pascal as an
INTEGER instead of a STRING. It was saving the file  to my documents
and adding a zero to it no matter what I put in for a path.

 
Didn't find what you were looking for? Find more on File save Or get search suggestion and latest updates.




Tagged: