×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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.

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

Hi BxWill,
I would create a function which generates the path to the current file from the current date.
Here is an example:

CODE

function LPad (str, pad, length)
  LPad = String(length - Len(str), pad) & str
end function

function get_path_from_date()
  current_date = Now
  current_year = Year(current_date)
  current_month = LPad(Month(current_date), "0", 2)
  current_month_name = MonthName(current_month, False)
  current_day = LPad(Day(current_date), "0", 2)
  get_path_from_date = "R:\Rate Sheets\" & _
     current_year & "\" & current_month_name & "\" & _
     "Rates_" & current_year & current_month & ".xlsx" 
end function

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

my_path = get_path_from_date()
Wscript.echo "path to the current file = """ & my_path & """"

my_path = get_path_from_date_2()
Wscript.echo "path to the current file = """ & my_path & """" 

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.

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

Consider formatting Date():

Debug.Print Month(Date)
Debug.Print MonthName(Month(Date))

So your little macro would be:

CODE

ActiveWorkbook.ChangeLink "R:\Rate Sheets\2019\" & MonthName(Month(Date) - 1) & "\Rates_2019" & Month(Date) -1 & ".xlsx", _ 
 "R:\Rate Sheets\2019\" & MonthName(Month(Date)) & "\Rates_2019" & Month(Date) & ".xlsx 

You can automate this even more.
If you would have a current Month's name in a cell, you can check it against MonthName(Month(Date)), 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.

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

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:
use path: R:\Rate Sheets\2019\April\Rates_201904.xlsx


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close