Save to relative (variable) file path

aldenes

New Member
Joined
Mar 2, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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

New Member
Joined
Mar 2, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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"
VBA Code:
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Range("A1").Value & "\" & Format(Date, "mm_dd_yyyy") & ".xlsx"
To make it more flexible with the same results:
VBA Code:
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

New Member
Joined
Mar 2, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Awesome; I will try that and report; thank you so much!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You're welcome,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top