You are here:

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


I am looking to create a macro and assign it to a button that when clicked will save the file and name the file based on the content in name range “project_name”.  I think I have a unique situation so I am still searching for an answer.  Below is a little more detail on my situation.
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.

hello how are you, look with the following code create folders, check if a folder exists otherwise create it, then re-check that folder if the folder exists if not create one with the previous month to the current month, go to the folder created or existing folders month and creates three different one for each surcursal of the company, you can help what you're doing. Visit there you will find a number of examples that can serve you.

Private Sub BuscayCreaCarpeta()
Dim mes1 As String
Dim mes, año As Integer

'Estabezco si la carpeta existe

año = Year(Date)
mes = Month(Date)

'Determino el nombre de la subcarpeta que va dentro de la carpeta año, hay que tener en cuenta que los informes se
'sacan al mes siguiente, en caso de ser diciembre debería tenerse en cuenta que se saca en enero del año que sigue
'por ende el directorio donde se guardan los datos es el del año anterior que ya está en teoría creado.

'Se pone como nombre de mes el anterior al mes actual, ya que los informes se sacan el mes siguiente
Select Case mes
Case 1
mes1 = "Dic"
Case 2
mes1 = "Ene"
Case 3
mes1 = "Feb"
Case 4
mes1 = "Mar"
Case 5
mes1 = "Abr"
Case 6
mes1 = "May"
Case 7
mes1 = "Jun"
Case 8
mes1 = "Jul"
Case 9
mes1 = "Ago"
Case 10
mes1 = "Sep"
Case 11
mes1 = "Oct"
Case 12
mes1 = "Nov"
End Select

'Se establece que si el mes es diciembre el año donde se guarden los archivos es el actual menos 1 osea el anterior
If mes1 = "Dic" Then
año = año - 1
End If

'Verifica que la carpeta con el nombre del año se encuentre caso contrario la crea
path = "Z:\INFORMES\" & año
If Dir(path, vbDirectory) = "" Then
MkDir path
End If

'Verifica que la carpeta con el nombre del mes se encuentre caso contrario la crea
path1 = "Z:\INFORMES\" & año & "\" & mes1
If Dir(path1, vbDirectory) = "" Then
MkDir path1
End If

'Verifica que la carpeta con el nombre de la sucursal esta dentro mes del informe
path2 = "Z:\INFORMES\" & año & "\" & mes1 & "\Consolidado"
If Dir(path2, vbDirectory) = "" Then
MkDir path2
End If

path3 = "Z:\INFORMES\" & año & "\" & mes1 & "\CC"
If Dir(path3, vbDirectory) = "" Then
MkDir path3
End If

path4 = "Z:\INFORMES\" & año & "\" & mes1 & "\Suc1"
If Dir(path4, vbDirectory) = "" Then
MkDir path4
End If
End Sub
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


All Answers

Answers by Expert:

Ask Experts




Expert programming macros or visual basic VBA that can help automate your Excel spreadsheets using VBA programming or macros to excel, forms and other procedures related to Macros. I also have an excellent command of Excel in almost its entirety, formulas, graphs, dynamic tables. In o examples can download free excel macros programmed in VBA, macros can be downloaded and adapted to the project that you're doing to automate excel


For several years, use Microsoft Excel on a daily basis, more specifically in my work, which is why over several years I have compiled a wealth of knowledge about their operation, adding to the basic operation of coding excel VBA macros to automate repetitive tasks . I serve as an expert in excel also in site, which I enjoy a great reputation. In o examples can download free excel macros programmed in VBA, macros can be downloaded and adapted to the project that you're doing to automate excel


In public o continuously macro examples that readers can download for free and adapt to the needs of each project in excel that has the web page visitor

My profession's is a Chartered Accountant with experience in Excel, especially in VBA or macro programming, but also in the formulas and everything related to excel, I graduated several years ago at the National University of Cordoba, now I practice my profession in which a basic tool is excel.

Awards and Honors

Past/Present Clients

©2016 All rights reserved.