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 www.programarexcel.blogspot.com 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
mes1 = "Dic"
mes1 = "Ene"
mes1 = "Feb"
mes1 = "Mar"
mes1 = "Abr"
mes1 = "May"
mes1 = "Jun"
mes1 = "Jul"
mes1 = "Ago"
mes1 = "Sep"
mes1 = "Oct"
mes1 = "Nov"
'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
'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
'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
'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
path3 = "Z:\INFORMES\" & año & "\" & mes1 & "\CC"
If Dir(path3, vbDirectory) = "" Then
path4 = "Z:\INFORMES\" & año & "\" & mes1 & "\Suc1"
If Dir(path4, vbDirectory) = "" Then