Add vlookup data links top external closed workbook

[heathb]

Hi all,

Please see below.
I am adding a formula to a spreadsheet, that looks up a section in a closed workbook. This will always be the prior working day
- So if today is the 23rd, then the link should connect to the 20th - as 23rd is Monday, prior date is Friday 20th)
The user has to update the link manually, as the file date is not changing. - i.e. Bank Rec 16 Sept

Please Login or Register  to view this content.

Below, is my attempt to automate this

Create link to the files folder

Please Login or Register  to view this content.

Adding code for link
When the VBA adds the following, it asks you to make the link (see attached screen shot - naming convention is changed from Sept to Sep)

Please Login or Register  to view this content.

So, How do I make [Bank Rec 16 Sep.xlsm] become [Bank rec Tdate.xlsm] and not try to auto update the link during the macro process?


Many many thanks

[Greg M]

Hi there,

It's difficult for me to test this fully without access to your workbook and file structure, but see if the following code gets you moving in the right direction:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[heathb]

Hi Greg M

Sorry for he delay, yes this works with a few tweaks

Thanks for your help

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M