Including cell name in file path, mixing hard lines and cells

[Jocelin]

hello friends,
I wish to make it so that my company's commands automatically get saved in the right folder. I made a template for these commands and I wished to be able to include the cell name in my file path like so: https://stackoverflow.com/questions/20978868/vba-macro-to-save-excel-file-using-path-from-cell

But in my case, i'd like to put the cell name in the path like so: " C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours"nameof the cell in A20"\concombre1\orange 2\bite mole"file name"

Please help, this is something my tutor would like for friday, thank you for your help!

 

[mumps]

Welcome to the Forum. Please note that Forum members generally agree that we will not do homework for you. Try doing some research online searching for something like "use cell value in path name". There is an abundance of data online.

 

[Jocelin]

Welcome to the Forum. Please note that Forum members generally agree that we will not do homework for you. Try doing some research online searching for something like "use cell value in path name". There is an abundance of data online.

Just to clarify that this is no homework and that I am not graded on it whatsoever, more of a real-life issue that the company needs. They are tired of having to go through tons of folders to save their excel files and asked me if it was possible to find a solution. I said "tutor" because I am doing an internship at the said company.

 

[mumps]

My apologies. I misunderstood. Perhaps you can try something like this replacing FileName with the actual name plus the extension:

SaveAs Filename:="C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours\" & Range("A20") & "\concombre1\orange 2\bite mole\ & FileName"

 

[Jocelin]

My apologies. I misunderstood. Perhaps you can try something like this replacing FileName with the actual name plus the extension:

SaveAs Filename:="C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours\" & Range("A20") & "\concombre1\orange 2\bite mole\ & FileName"

Thank you very much sir, your code worked well. I wanted to know if it was possible for excel to select A20 as a folder path but as well as ignore the letters that are after it.

Here is what I mean: in my case, the file is saved to: path = "C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours" & Range("A20") & "\concombre1\orange 2\bite mole"
Thanks to you, It now saves to the correct location, but the only problem is that the company has a bunch of folders that have the name "A20" followed by the name of the project.
It is possible to further explore this code in order to make excel ignore the fact that there are other letters AFTER the "" & Range("A20") &"

So, when saved: let's say range A20 is "14-2058" and the folder is named "14-2058 Metal Project" would it be possible for excel to ignore the "Metal Project" part of the folder name, so anything beyond the match.


Thank you very much for your help.

 

[Jocelin]

I tried something like this but it didn't work:


Private Sub SaveSpecific_Click()
Dim path As String
Dim filename1 As String


path = "C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours" & Range("A20") & ".*" & "\concombre1\orange 2\bite mole"
filename1 = Range("F7").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True






End Sub

 

[mumps]

I'm not sure that would be possible. Since there are more than one folder that have the same beginning value (Cell A20), the macro would not know which of the folders to save to. One way around this is to have another cell containing "Metal Project". For example, let's say cell A21 contains "Metal Project" then you could use the following line of code:

SaveAs Filename:="C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours\" & Range("A20") & " " & Range("A21") & "\concombre1\orange 2\bite mole\ & FileName"

 

[Fluff]

If you've only got one folder that starts with the A20 value, you could try

   Dim Pth1 As String, Pth As String
   Pth1 = Dir("C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours\" & Range("A20") & "*", vbDirectory)
   Pth = "C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours\" & Pth1 & "\concombre1\orange 2\bite mole\"
   ThisWorkbook.SaveAs Pth & "Test.xlsm"

 

[Jocelin]

@mumps I might have badly explained, all the folders start with a different beginning value, its just that only the number is in the file and not the full name for example: I am trying to save filename1 which has the number 15479 in cell A20 to
path = "C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours" & Range("A20") & "\concombre1\orange 2\bite mole"

except the folder's actual name is 15479 Metal project

or another instance could be, another file which has a different number, in A20 lets say 15789, that one would need to save to the folder "15789 Metal project 2"

 

[Jocelin]

I think Fluff is onto something, the only problem is i have no idea where to put that code to apply it to the command button i have linked to my previous code which is:
save to specific location code:


with button


Private Sub SaveSpecific_Click()
Dim path As String
Dim filename1 As String


path = "C:\Users\Vincent.BILLY-SURFACE\Desktop\affaires en cours" & Range("A20") & "\concombre1\orange 2\bite mole"
filename1 = Range("F7").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True






End Sub