• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: Update data link based on string of a file name

I

inddon

Member
Hello There,

I have the following files:

1. Master file. There are 2 files 'Master Book 670' & 'Master Book 671' (password: master)
2. Child file. There is 1 file 'Child book'

The 'Child Book' file is linked to Master Book file. Currently, the data link in 'Child Book' is done manually. I am looking for to have a VBA code which will do the update via VBA code.

Requirement:
Child Book
1. In the 'Child Book' file in column F2 is the file number of the 'Master Book' given (this is the suffix in the master file name)
2. A button 'Update Link' is there. When pressed it should check if the cell F2 has a value. If F2 has a value then it should check the master directory (directory link should be taken from the formula (the first data row i.e. E5)
3. It should search in that directory if there is any file with it's last 3 characters matches with the above cell F2.
4. If step 3 matches, then open that master file in read-only mode (password is always: master). If not display message ' File not found'
5. Update the links
6. Finally give a message success or failure

I have attached the files 'Master Book 670' & 'Master Book 671' and Child Book' for your reference.

Could you please advice, how this can be achieved via VBA?

Thanks a lot ad look forward to hear from you.

Regards
Don
 

Attachments

  • Master.zip
    19.5 KB · Views: 4
  • Child Book.xlsm
    19.8 KB · Views: 6
inddon said:
Hello There,

I have the following files:

1. Master file. There are 2 files 'Master Book 670' & 'Master Book 671' (password: master)
2. Child file. There is 1 file 'Child book'

The 'Child Book' file is linked to Master Book file. Currently, the data link in 'Child Book' is done manually. I am looking for to have a VBA code which will do the update via VBA code.

Requirement:
Child Book
1. In the 'Child Book' file in column F2 is the file number of the 'Master Book' given (this is the suffix in the master file name)
2. A button 'Update Link' is there. When pressed it should check if the cell F2 has a value. If F2 has a value then it should check the master directory (directory link should be taken from the formula (the first data row i.e. E5)
3. It should search in that directory if there is any file with it's last 3 characters matches with the above cell F2.
4. If step 3 matches, then open that master file in read-only mode (password is always: master). If not display message ' File not found'
5. Update the links
6. Finally give a message success or failure

I have attached the files 'Master Book 670' & 'Master Book 671' and Child Book' for your reference.

Could you please advice, how this can be achieved via VBA?

Thanks a lot ad look forward to hear from you.

Regards
Don
Click to expand...


Looking forward for your advice on my post...

Regards
Don
 
You must log in or register to reply here.
Back
Top