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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top