Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ms excel vba automatically update external link to file in different folders based on current date 2

Status
Not open for further replies.
BxWill

BxWill

MIS
Mar 30, 2009
367
0
16
US
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;

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.

 
Sort by date Sort by votes
Hi BxWill,
I would create a function which generates the path to the current file from the current date.
Here is an example:
Code: 
[COLOR=#804040][b]function[/b][/color] LPad [COLOR=#804040][b]([/b][/color][COLOR=#008080]str[/color][COLOR=#804040][b],[/b][/color] pad[COLOR=#804040][b],[/b][/color] length[COLOR=#804040][b])[/b][/color]
  LPad [COLOR=#804040][b]=[/b][/color] [COLOR=#2e8b57][b]String[/b][/color][COLOR=#804040][b]([/b][/color]length [COLOR=#804040][b]-[/b][/color] [COLOR=#804040][b]Len[/b][/color][COLOR=#804040][b]([/b][/color][COLOR=#008080]str[/color][COLOR=#804040][b]),[/b][/color] pad[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#008080]str[/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]function[/b][/color]

[COLOR=#804040][b]function[/b][/color] get_path_from_date[COLOR=#804040][b]()[/b][/color]
  current_date [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]Now[/color]
  current_year [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]Year[/color][COLOR=#804040][b]([/b][/color]current_date[COLOR=#804040][b])[/b][/color]
  current_month [COLOR=#804040][b]=[/b][/color] LPad[COLOR=#804040][b]([/b][/color][COLOR=#008080]Month[/color][COLOR=#804040][b]([/b][/color]current_date[COLOR=#804040][b]),[/b][/color] [COLOR=#ff00ff]"0"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]2[/color][COLOR=#804040][b])[/b][/color]
  current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]MonthName[/color][COLOR=#804040][b]([/b][/color]current_month[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]False[/color][COLOR=#804040][b])[/b][/color]
  current_day [COLOR=#804040][b]=[/b][/color] LPad[COLOR=#804040][b]([/b][/color][COLOR=#008080]Day[/color][COLOR=#804040][b]([/b][/color]current_date[COLOR=#804040][b]),[/b][/color] [COLOR=#ff00ff]"0"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]2[/color][COLOR=#804040][b])[/b][/color]
  get_path_from_date [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"R:\Rate Sheets\"[/color] [COLOR=#804040][b]& _[/b][/color]
     current_year [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]"\"[/color] [COLOR=#804040][b]&[/b][/color] current_month_name [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]"\"[/color] [COLOR=#804040][b]& _[/b][/color]
     [COLOR=#ff00ff]"Rates_"[/color] [COLOR=#804040][b]&[/b][/color] current_year [COLOR=#804040][b]&[/b][/color] current_month [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]".xlsx"[/color] 
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]function[/b][/color]

[COLOR=#804040][b]function[/b][/color] get_path_from_date_2[COLOR=#804040][b]()[/b][/color]
  current_date [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]Now[/color]
  current_year [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]Year[/color][COLOR=#804040][b]([/b][/color]current_date[COLOR=#804040][b])[/b][/color]
  current_month [COLOR=#804040][b]=[/b][/color] LPad[COLOR=#804040][b]([/b][/color][COLOR=#008080]month[/color][COLOR=#804040][b]([/b][/color]current_date[COLOR=#804040][b]),[/b][/color] [COLOR=#ff00ff]"0"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]2[/color][COLOR=#804040][b])[/b][/color]
  [COLOR=#804040][b]select[/b][/color] [COLOR=#804040][b]case[/b][/color] [COLOR=#008080]month[/color][COLOR=#804040][b]([/b][/color]current_date[COLOR=#804040][b])[/b][/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]1[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"January"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]2[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"February"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]3[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"March"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]4[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"April"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]5[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"May"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]6[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"June"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]7[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"July"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]8[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"August"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]9[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"September"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]10[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"October"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]11[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"November"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#ff00ff]12[/color]
      current_month_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"December"[/color]
    [COLOR=#804040][b]case[/b][/color] [COLOR=#804040][b]else[/b][/color]
      wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"Error Bad Value !"[/color]
  [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]select[/b][/color]
  current_day [COLOR=#804040][b]=[/b][/color] LPad[COLOR=#804040][b]([/b][/color][COLOR=#008080]Day[/color][COLOR=#804040][b]([/b][/color]current_date[COLOR=#804040][b]),[/b][/color] [COLOR=#ff00ff]"0"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]2[/color][COLOR=#804040][b])[/b][/color]
  get_path_from_date_2 [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"R:\Rate Sheets\"[/color] [COLOR=#804040][b]& _[/b][/color]
     current_year [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]"\"[/color] [COLOR=#804040][b]&[/b][/color] current_month_name [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]"\"[/color] [COLOR=#804040][b]& _[/b][/color]
     [COLOR=#ff00ff]"Rates_"[/color] [COLOR=#804040][b]&[/b][/color] current_year [COLOR=#804040][b]&[/b][/color] current_month [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]".xlsx"[/color] 
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]function[/b][/color]

my_path [COLOR=#804040][b]=[/b][/color] get_path_from_date[COLOR=#804040][b]()[/b][/color]
Wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"path to the current file = """[/color] [COLOR=#804040][b]&[/b][/color] my_path [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]""""[/color]

my_path [COLOR=#804040][b]=[/b][/color] get_path_from_date_2[COLOR=#804040][b]()[/b][/color]
Wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"path to the current file = """[/color] [COLOR=#804040][b]&[/b][/color] my_path [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]""""[/color]

Output:
Code: 
c:\mikrom>cscript /NoLogo bxwill.vbs
path to the current file = "R:\Rate Sheets\2019\apríl\Rates_201904.xlsx"
path to the current file = "R:\Rate Sheets\2019\April\Rates_201904.xlsx"

I created the function get_path_from_date_2(), because my locale setting in windows is not English and so MonthName() returns the name in my locale settings.
 
Upvote 0 Downvote
Consider formatting Date():
[tt]
Debug.Print [green]Month(Date)[/green]
Debug.Print [blue]MonthName(Month(Date))[/blue]
[/tt]
So your little macro would be:

Code: 
ActiveWorkbook.ChangeLink "R:\Rate Sheets\2019\" & [blue]MonthName(Month(Date) - 1)[/blue] & "\Rates_2019" & [green]Month(Date) -1[/green] & ".xlsx", _ 
 "R:\Rate Sheets\2019\" & [blue]MonthName(Month(Date))[/blue] & "\Rates_2019" & [green]Month(Date)[/green] & ".xlsx

You can automate this even more.
If you would have a current Month's name in a cell, you can check it against [tt]MonthName(Month(Date))[/tt], and you can do the same with Year. And run this check when you open your Workbook.

Just a suggestion...


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
BTW,

Your Pseudocode;

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

could be this:

Code: 
Dim strMyPath As String

Dim intMyYear As Integer
Dim strMyMoName As String
Dim strFileDate As String

intMyYear = Year(Date)
strMyMoName = MonthName(Month(Date))
strFileDate = Format(Date, "YYYYMM")

strMyPath = "R:\Rate Sheets\" & intMyYear & "\" & strMyMoName & "\Rates_" & strFileDate & ".xlsx"

Debug.Print "use path: " & strMyPath

so you should see:
[tt]use path: R:\Rate Sheets\2019\April\Rates_201904.xlsx[/tt]


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
163
mikrom
mikrom
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
631
dhookom
dhookom
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
130
combo
combo
xwb
  • Locked
  • Helpful tip
Performing VBA operations in other languages
Replies
1
Views
110
strongm
strongm
Andrzejek
  • Question
Outlook VBA - Trust access
Replies
3
Views
226
hermanlaksko
hermanlaksko

Part and Inventory Search

Sponsor

Back
Top