Variable File Path Link

Question

WORKOUT USING MACROS...

Every month a report is generated, named the exact same thing, "Report", and stored in a designated file structure. The file structure we are using is thus: C/Fiscal Year/Month/Report.xlsx.  So, there is a folder for every month, and within every folder is an Excel report named, "Report".

 I am trying to set up a workbook that will pull data from the most current "Report.xlsx". I know how to create the static link, but that link will only look at the report filed for the static fiscal year and month defined in the file path of the link.

Is there a way to structure an in-cell link so that the file path contains a formula calculating the fiscal year from "today()", and the month from "today()"?  I don't need help with the fiscal year and month formulae, but I don't know how to enter the link formula to concatenate the formulae into the file path.

Answer 1

Hi,

To fetch data from another workbook where any component of the path is variable would involve the usage of the INDIRECT() function.  The problem with the INDIRECT() function is that it will not work if the source workbook is closed.  As one opens the source workbook, the value in the destination workbook will work fine.  Since it is not practical to have the source workbook open at all times that the destination workbook is also open, the INDIRECT() function solution will not be a viable one.

Here's my take:

1. Type the path (C/Fiscal Year/Month/Report.xlsx) of the source workbook in a certain cell of the destination workbook

2. In the destination workbook, use the INDIRECT.EXT() function to fetch data from a closed workbook.  This function will work even if the source workbook is closed.  The INDIRECT.EXT() function is not a built in function - it part of the Morefunc add-in (third party add-in) which you can download from this link of my website.
3. As you change the path in step 1 above, the formula will fetch data from the workbook at that location even if the source workbook is closed.

Hope this helps.

Answer 2

I don't need help with the fiscal year and month formulae, but I don't know how to enter the link formula to concatenate the formulae into the file path.

You say you don't need help with the Fiscal Year and Month formulae but the Fiscal year varies with the region/country that you are in so please tell us what formulae you are using for the Fiscal year and also the Month so we can use them to create the concatenated result. Need know the format of the month which could be numeric, 3 alpha abbreviation or full month words.  

Basically the concatenation of variables is like the following.

="C:" &  FiscalYearFormula & "" & MonthFormula & "" & "Report.xlsx"