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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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

W
  • Question Question
Help updating VBA code to use wildcard in the file name
Replies
1
Views
79
AlphaFrog
AlphaFrog
A
  • Question Question
How sort files contains specific word based on modified dates
Replies
0
Views
102
abdelfattah
A
D
  • Solved
How to include date range in my PDF export name
Replies
4
Views
91
druck21
D
D
  • Solved
Merge files and keep source formatting.
Replies
9
Views
350
Alex Blakenburg
A
T
  • Question Question
VBA to update Powerpoint Text from Excel Data in Cells
Replies
9
Views
1K
tbakbradley
T

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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