Save to relative (variable) file path

[aldenes]

Hello all bright minded people, please allow me to ask a question:

I have a complicated excel project that uses Power Query to read data from different directories. To indicate the source I have a drop down menu (with all the sources) and a FilePath named in name manager. The project outputs results in the active sheet based on different vba routines.
Problem: I need to copy the results to a different file named as the current day that will reside in an "output" folder in the location of each source; I am trying to adapt the script below for doing that but have some syntax problems.

In other words, if I have 4 source directories A, B, C, D and when I select to import from A, I will need to save the file in A:\output; A, B, C, D contain the source files, locations that are variables (defined in FilePath)
What I am using below works but I need to somehow introduce Filepath\output and I don't know how. Your help is much appreciated.

Sub Storing()

Dim sFileName$
Application.ScreenUpdating = False

sFileName = ThisWorkbook.Path & "\" & Format(Date, "mm_dd_yyyy") & ".xlsx"

ActiveSheet.Copy

ActiveWorkbook.SaveAs sFileName
ActiveWorkbook.Close savechanges:=False
MsgBox "The Sheet name is saved as " & sFileName & "!"
Application.ScreenUpdating = True


End Sub

 

[JLGWhiz]

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Output\" & Format(Date, "mm_dd_yyyy") & ".xlsx"

Your nessage bix will need to be modified to reflect the same change.

 

[aldenes]

Thanks for your reply; it takes care of saving in the Output subfolder; however, I have multiple folders where data to be imported resides (ex exemplified above say folders A, B, C, D); I select whichever I want to work with from a drop down menu; the selection is passed as a named field "FilePath"; I need to save the results to the respective output subfolder in folders A, B, C, D; when I work with A, result is saved in A:\output, when I work with B, result is saved in B:\output and so on.
I do not know how to pass this name field or cell value where it resides to VBA in the syntax above
Thank you!

 

[JLGWhiz]

You need to clarify if you are using four different root directories or four different sub directories. For Example, if the Root directory is the same as that of the host workbook and you want to add in a subdirectory based on a dropdown value in cell A1 then"

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Range("A1").Value & "\" & Format(Date, "mm_dd_yyyy") & ".xlsx"

To make it more flexible with the same results:

Dim fPath As String, dr As String, dt As String 'declare varibles
fPath = ThisWorkbook.Path & "\"  'Initialize varible and add separator
dr = Range("A1").Value 'initialize dropdown variable value
dt = Format(Date, "mm_dd_yyyy")  'initialize date format variable
ActiveWorkbook.SaveAs fPath & dr & "\" & dt & ".xlsx"  'construct the statement for saveas

Now it saves to the directory value in cell A1 no matter what it happens to be.

Don't be confused by the use of variables. They are only tools to save space and simplify the typing of the code when what the variable represents might be used several times in the code, or when using the full blown syntax would create a very long statement.

 

[aldenes]

Awesome; I will try that and report; thank you so much!

 

[JLGWhiz]

You're welcome,
regards, JLG