Change source file based on current date and updating values

[Lv4games]

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

 

[Joe4]

Let's say that the date is input in cell J10.
Then you should be able to use this:

Sub Macro1()
'
' Macro1 Macro
'

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

End Sub

 

[Lv4games]

Thank you Joe4, I tried to run it but I am getting the below and highlight

 

[Joe4]

Was the code you posted in your original code working?
Did you remember to update the cell "J10" reference in my code to whatever cell your date in in?
Is the date entered on the sheet entered actually as a valid date, or is it actually a text entry?

 

[Lv4games]

Was the code you posted in your original code working?
Did you remember to update the cell "J10" reference in my code to whatever cell your date in in?
Is the date entered on the sheet entered actually as a valid date, or is it actually a text entry?

1. the original code works that I posted.
2. Yes I did updated the Cell j, on my end I have it to reference J40
3. its an actual date, I used =today() and in formatting it have under category "Date"

 

[Joe4]

If you temporarily return the build value to a MsgBox, does it look like it is the correct value?

Sub Macro1()
'
' Macro1 Macro
'

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

End Sub

 

[Lv4games]

If you temporarily return the build value to a MsgBox, does it look like it is the correct value?

Sub Macro1()
'
' Macro1 Macro
'

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

End Sub
[/COD
[/QUOTE]

Yes it is the correct value, it gives me the file path and its all correct. How to I actually get it to update what its showing me. Or was that just making sure it pulls correct value before we finish the code?

 

[Joe4]

Yes it is the correct value, it gives me the file path and its all correct. How to I actually get it to update what its showing me. Or was that just making sure it pulls correct value before we finish the code?

Yes, I am just trying to confirm that it is EXACTLY the same (any extra space, characters, format, etc could be problematic).

I haven't worked much with links, but I would it shouldn't matter if the value is hard-coded or the result of a formula.

Can you tell me if this version works?
Sub Macro1()
'
' Macro1 Macro
'

'
Dim fName as String
fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm"
ActiveWorkbook.UpdateLink Name:= fName, Type:=xlExcelLinks

End Sub

This time we are still using a variable, but we are entering the hard-coded value into that variable.
I am curious to see if that works.

 

[Lv4games]

Yes, I am just trying to confirm that it is EXACTLY the same (any extra space, characters, format, etc could be problematic).

I haven't worked much with links, but I would it shouldn't matter if the value is hard-coded or the result of a formula.

Can you tell me if this version works?
Sub Macro1()
'
' Macro1 Macro
'

'
Dim fName as String
fName = "https://assurantconnects.sharepoint.com/teamsGO/Staff/leader/Department Document Library/watchtower/IDPs/IDP Analysis - 05.18.23.xlsm"
ActiveWorkbook.UpdateLink Name:= fName, Type:=xlExcelLinks

End Sub

This time we are still using a variable, but we are entering the hard-coded value into that variable.
I am curious to see if that works.

Yes the version you provided above works fine and updated.

 

[Joe4]

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?

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.