Have MS Excel 2016 workbook that has several links in worksheet "Analysis" that refers to the external file "Rates_201904" at path R:\Rate Sheets\2019\April\.
Every month, I manually update the link to the following month's Rate sheet. For example, I will now update to the external file "Rates_201905" that is located at R:\Rate Sheets\2019\May\.
(Note, there are over 20 other links to 20 other external MS Excel files. Therefore, every month, I manually have to update 20 times.)
My objective is to use vba to "automatically" change the source of the links each month conditional on the current date. Alternatively, I could assign the macro to a icon on the toolbar and click on it.
Currently experimenting with ActiveWorkbook.ChangeLink via MS Excel VBA.
Recording the macro, I receive the following;
Considering that the updates occur monthly, I envision that I can use the ActiveWorkbook.ChangeLink method to "automatically" Edit the links and change the source each month.
The structure of the Rate files are the same month to month. Only the data within the file changes.
My Pseudocode is;
If now() is > Beginning of April 2019 and < End of April 2019, use path "R:\Rate Sheets\2019\April\Rates_201904.xlsx"
If now() is > Beginning of May 2019 and < End of May 2019, use path "R:\Rate Sheets\2019\May\Rates_201905.xlsx"
If now() is > Beginning of June 2019 and < End of June 2019, use path "R:\Rate Sheets\2019\June\Rates_201906.xlsx"
and so on
Any insight as to how I can setup this up? How would you perform, if doing it differently?
Have searched the internet for quite some time and reviewed several books, but I have not found vba code that has a nested IF and uses date/time to conditionally edit external links.
Every month, I manually update the link to the following month's Rate sheet. For example, I will now update to the external file "Rates_201905" that is located at R:\Rate Sheets\2019\May\.
(Note, there are over 20 other links to 20 other external MS Excel files. Therefore, every month, I manually have to update 20 times.)
My objective is to use vba to "automatically" change the source of the links each month conditional on the current date. Alternatively, I could assign the macro to a icon on the toolbar and click on it.
Currently experimenting with ActiveWorkbook.ChangeLink via MS Excel VBA.
Recording the macro, I receive the following;
Code:
ActiveWorkbook.ChangeLink "R:\Rate Sheets\2019\April\Rates_201904.xlsx", _
"R:\Rate Sheets\2019\May\Rates_201905.xlsx
Considering that the updates occur monthly, I envision that I can use the ActiveWorkbook.ChangeLink method to "automatically" Edit the links and change the source each month.
The structure of the Rate files are the same month to month. Only the data within the file changes.
My Pseudocode is;
If now() is > Beginning of April 2019 and < End of April 2019, use path "R:\Rate Sheets\2019\April\Rates_201904.xlsx"
If now() is > Beginning of May 2019 and < End of May 2019, use path "R:\Rate Sheets\2019\May\Rates_201905.xlsx"
If now() is > Beginning of June 2019 and < End of June 2019, use path "R:\Rate Sheets\2019\June\Rates_201906.xlsx"
and so on
Any insight as to how I can setup this up? How would you perform, if doing it differently?
Have searched the internet for quite some time and reviewed several books, but I have not found vba code that has a nested IF and uses date/time to conditionally edit external links.