Change source file based on current date and updating values

L

Lv4games

New Member
Joined
Oct 7, 2016
Messages
17
Hello everyone,

I need some help with the below VBA code. What I am wanting to do based on the date input on a cell, I want it to update to new source file and update values. The files that we want to update the values name change only the date that is (in red font), the rest of the naming stays the same, example IDP Analysis - 05.18.23

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.UpdateLink Name:= _
"https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm" _
, Type:=xlExcelLinks
End Sub
 
Sort by date Sort by votes
Joe4 said:
That seems to suggest that maybe there is an issue with the data, and they are not, in fact the same.

If your try the following, does the message box return TRUE or FALSE?
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm"
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    MsgBox fName = fName2
    'ActiveWorkbook.UpdateLink Name:=fName, Type:=xlExcelLinks

End Sub

If it returns false, then there is probably an issue with the J40 cell reference, such as:
- it is looking at the wrong sheet
- the value in cell J40 is NOT entered as a valid date, but rather as text, and therefore the FORMAT function cannot be applied to it.
Click to expand...
It did indeed returned it false. Here is how I have the date reference j40, is there one you recommend?
1685456036589.png
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Joe4 said:
That seems to suggest that maybe there is an issue with the data, and they are not, in fact the same.

If your try the following, does the message box return TRUE or FALSE?
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm"
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    MsgBox fName = fName2
    'ActiveWorkbook.UpdateLink Name:=fName, Type:=xlExcelLinks

End Sub

If it returns false, then there is probably an issue with the J40 cell reference, such as:
- it is looking at the wrong sheet
- the value in cell J40 is NOT entered as a valid date, but rather as text, and therefore the FORMAT function cannot be applied to it.
Click to expand...
Yes it did return in as False, this is the formatting I have set for J40, is there one you recommend?
1685456147788.png
 
Upvote 0
If the value in J40 is today's date, then update the code to this and try again, and see if it returns TRUE or FALSE:
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm" 
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    MsgBox fName = fName2
    'ActiveWorkbook.UpdateLink Name:=fName, Type:=xlExcelLinks

End Sub
 
Upvote 0
Joe4 said:
If the value in J40 is today's date, then update the code to this and try again, and see if it returns TRUE or FALSE:
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm"
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    MsgBox fName = fName2
    'ActiveWorkbook.UpdateLink Name:=fName, Type:=xlExcelLinks

End Sub
Click to expand...
It returned TRUE
 
Upvote 0
OK, that confirms that we seem to be building the correct value.
So now let's test each one to see if it works.

First, see if this builds a workable link:
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm" 
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    ActiveWorkbook.UpdateLink Name:=fName, Type:=xlExcelLinks

End Sub

Then, test this one and see if it builds a workable link:
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm" 
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    ActiveWorkbook.UpdateLink Name:=fName2, Type:=xlExcelLinks

End Sub
(note the only difference between the two is the variable it is using in the Name argument).
 
Upvote 0
Joe4 said:
OK, that confirms that we seem to be building the correct value.
So now let's test each one to see if it works.

First, see if this builds a workable link:
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm"
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    ActiveWorkbook.UpdateLink Name:=fName, Type:=xlExcelLinks

End Sub

Then, test this one and see if it builds a workable link:
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim fName as String
    Dim fName2 as String
    fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm"
    fName2 = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - " & Format(Range("J40"),"mm.dd.yy") & ".xlsm"
    ActiveWorkbook.UpdateLink Name:=fName2, Type:=xlExcelLinks

End Sub
(note the only difference between the two is the variable it is using in the Name argument).
Click to expand...
1st code and second code I get
1685459078358.png
, for the first code if I change the date to 5.18.23 it automatically updates values, but as soon as I change the code to say 5.30.23, I get the "activeworkbook" error as soon on screen shot. 2nd. code gives the error "activeworkbook" regardless if I switch the code to 5.18.23 or leave it as is 5.30.23. Thank you so much for the help on this :)
 
Upvote 0
Lv4games said:
1st code and second code I get View attachment 92559, for the first code if I change the date to 5.18.23 it automatically updates values, but as soon as I change the code to say 5.30.23, I get the "activeworkbook" error as soon on screen shot. 2nd. code gives the error "activeworkbook" regardless if I switch the code to 5.18.23 or leave it as is 5.30.23. Thank you so much for the help on this :)
Click to expand...
That seems to suggest that:
"https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm"
is a valid link, but:
"https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm"
is not.

Are you sure that you have a file in there with the 05.30.23 date?
Everything you confirmed here seems to suggest that you don't.
 
Upvote 0
Joe4 said:
That seems to suggest that:
"https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm"
is a valid link, but:
"https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm"
is not.

Are you sure that you have a file in there with the 05.30.23 date?
Everything you confirmed here seems to suggest that you don't.
Click to expand...

Joe4 said:
That seems to suggest that:
"https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm"
is a valid link, but:
"https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.30.23.xlsm"
is not.

Are you sure that you have a file in there with the 05.30.23 date?
Everything you confirmed here seems to suggest that you don't.
Click to expand...
Yes I am sure, I even did a test, updated the links to file 05.30.23 and used your first Code and was able to updated the values for 05.30.23 when I ran the macro
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA Project (Automatic Update) and Macro reference with file name change
Replies
8
Views
325
JDSOuth49
J
J
  • Question
VBA Script to Update Files in a folder, updating Links
Replies
1
Views
130
Micron
M
A
  • Question
Breaking up CSV files based on date into individual txt files
Replies
2
Views
235
anuradhagrewal
A
B
  • Solved
Using VBA to update a file daily with data from another daily workbook
Replies
2
Views
648
BYahr
B
D
  • Solved
Open Certain Worksheet Based on Current Date
Replies
8
Views
304
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,169
Messages
6,123,412
Members
449,098
Latest member
ArturS75

Share this page

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