You are here:

Excel/Macro to Save File to Default Location based on Cell Content

Advertisement


Question
QUESTION: Hello,

I am looking to create a macro and assign it to a button so the user of the form can click the button and have the form save to the default location and have the file be named the value that is in cell A12 of Sheet 1.  We are using this form on a sharepoint site.  The excel template form file is at the parent site level and can be accessed at the different subsites.  So the location varies based on the subsite.  

I've found many marco codlings to save the file to a specific folder using the content in a cell but nothing that will use the default location when the save button is clicked.  Any help is appreciated.

Thank you,

Will

Based

ANSWER: Hi Will,

Please see if the below text serves your requirement:
Paste the following text in the Command Button code:

Private Sub CommandButton1_Click
Dim fpath as String
fpath = Worksheets("Sheet1").Cells(12,1).Value
ActiveWorkbook.SaveCopyAs ("C:\Temp\" & fpath & ".xls")
End Sub

In the above code, fpath represents the file name stored in cell A12 of Sheet 1 and "C:\Temp\" represents the default path you wanted to use. By contatenating these two values, you get the default path and file name you want.

Hope this helps.
Gulshan.


---------- FOLLOW-UP ----------

QUESTION: Thank you Gulshan.  If the default location is changing is there code that will allow this?  If I understand the above it requires me to have one location and hard code that into the Macro.  Depending at what level the form is opened at it will determine where it is saved.  I am looking to save file in the location that appears when the save button is clicked, but I want to name the file the same name as the content in Cell 12 sheet one.  Is this possible?

Thanks again for all your help.

Regards,

Will

ANSWER: Hi Will,

This can be handled too.

You can designate a cell - Say A13 for path. Everytime you load a form, you can update the value of this cell depending on the level of the form.
Then, while clicking the button, you can pick up the path from this cell.

Private Sub CommandButton1_Click
Dim fpath as String
Dim fdir as string
fpath = Worksheets("Sheet1").Cells(12,1).Value
fdir = Worksheets("Sheet1").Cells(13,1).Value
ActiveWorkbook.SaveCopyAs (fdir & fpath & ".xls")
End Sub

Hope this helps,
Gulshan.


---------- FOLLOW-UP ----------

QUESTION: Hi Gulshan,

Thanks again for the response.  I am still having issues with getting this code to work.  I apologize as I didn't provide much detail on what my situation is so I try to do so below to see if there is anything that you can suggest will work.

I have a Microsoft SharePoint site that we use to route forms of project requests for approval.  We have many different divisions that have a subsite within the SharePoint .  We‘ve created a standard routing form template that we saved in the Site Content types of the Parent Site.  Each subsite submits a project request using this standard form by filling out the different areas related to their project.

To submit a project request, the user enters their subsite of the SharePoint and clicks the “new document icon within a library.  This new document icon pulls the standard routing form I mention above.  The user completes the necessary data and clicks the save icon to save it to the subsite library (which they are in#.  When the save window opens, the default folder #library# of that subsite will appear where the form/file will be saved.  It allows the user to type in a name they want to save the file as in that default location.  

When a user from a different subsite wants to submit a project request, that user will enter his or her subsite and follow the steps mentioned above.  Each subsite accesses this standard form template, but when they save the form, the default path is to the library of their subsite.  With this, each path default location is different than the other subsites.  
What I am looking to do, is create a macro that will save the file in the subsite default location #remember this changes by subsite) and name the file based on the cell content within the form.  I’ve named this cell content with a name range of “projectname”.  I’ve come across code that will save the file to your desktop and name it based on the data in the name range specified, however I can’t seem to find a code that will use the default path of the SharePoint subsite as it differs with each subsite.  Hard coding a path won’t suffice, because each subsite has its own folder where the completed routing forms are saved.  

The best example I can give is if you were to open excel and save the file, it will typically default the path to the “my documents” allowing you to name the file.  This is essentially what I’m looking to do with the standard forms in our SharePoint site.  I am looking to keep the default path of each subsite and then name the file based on the name range I mention above.

I could just leave as is by allowing the user to specific a name when they click the save icon.  However, I am trying to eliminate the keying of data and create consistency with the file name and what is stated in the form as the project name.

Thanks again,

Will

Answer
Hi Will,

I'm not an expert on Sharepoint, but I have just one question. Is there any way for you to identify and store the subsite from where the user is accessing the file.
I'm not trying to define the default path here, I just need to know which subsite the user is accessing this form from. If that is possible, then we can move towards our solution.

Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.