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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

D
  • Solved
Merge files and keep source formatting.
Replies
9
Views
312
Alex Blakenburg
A
T
  • Question Question
VBA to update Powerpoint Text from Excel Data in Cells
Replies
9
Views
763
tbakbradley
T
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
594
mumps
mumps
S
  • Question Question
Gathering Data From Multiple Files in Same Folder in Particular Range
3 4 5
Replies
41
Views
1K
kvsrinivasamurthy
K
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
414
mlan1604
M

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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