Excel Macro that Auto Updates Links/Refreshes

[SeanCook]

So i am trying to make a macro on a workbook(WB1). It has cells that link to other Workbooks (WB2-WB10). These (WB2-WB10) have two sheets that are linked to an access point which contains "1.xls" and "2.xls" (sheet1 linked to 1.xls, and sheet2 to 2.xls). I have tried a macro that will open the file, update links then save and close. Also tried the same but refreshall instead of update links, then again with a marco that does both, and yet everytime it runs, it goes through all the workbooks one by one, opens them, calculates, saves and closes, and yet when i go back to check, nothing is updated.

I have checked the access data and it is updated, and if i go into each workbook and manually click refresh it does indeed refresh. I am at a loss and dont know what i am doing wrong.

here is the code.


Sub GetFile()
Workbooks.Open "blahblah.xlsx", UpdateLinks:=1
ActiveWorkbook.RefreshAll
ActiveWorkbook.Close SaveChanges:=True

Any help would be greatly appreciated.

Thank You,

Sean

 

[BiocideJ]

Perhaps try the following...

    ActiveWorkbook.UpdateLink Name:= "blahblah.xlsx", Type:= xlExcelLinks

If all you want is to refresh the links, that is what I use in some of my workbooks.
Not sure of compatability with versions prior to 2007, but it definitely works in Excel 2007.

 

[dalbright]

What would the code be to use this against multiple sheets on a network file? I am having major issues with links not working properly and this is a pretty hot item.

We have a master file that has multiple sheets and in the multiple sheets there are links to other spreadsheets within the same network directory.
ex: Master has Sheet1, Sheet2, Sheet1 and Sheet2 have links to Child1, Child2 and Child3. All Sheets are protected so that data entry can only occur in specific cells.

When the Master file is opened an error is being returned that the "workbook contains one or more links that cannot be updated". The drive is not mapped and the link is \\serverName\ShareName\Folder\Folder\fileName.xlsx

I really need help with this so any feedback will be sincerely appreciated!!!

 

[BiocideJ]

This is a different issue entirely from the Original Post, but fortunately I saw it come through...

Links that cannot be updated can be due to various problems:

• the filename changed
• the file location changed
• the link has a typo
• etc.

The easiest way I know of to correct these problems is to open the main file (that is saying it cannot update links) In Excel 2007 you would click the Data tab and select Edit Links.
This will bring up a list of all references to other workbooks. From what you are saying, one or more of those links will have an ERROR in the Status column.

The trick now is to find the cell that uses that reference and then to determine why it isn't linking correctly (see the list above).
To find the cells that reference the broken link, use Excel's FIND feature (CTRL+F).
e.g. the broken link is [MyOtherExcelFile.xls] you could search for MyOtherExcelFile.xls. This will take you to the cell with the broken reference and you will then need to then figure out what to do.

• rename the other file
• move the file back to where it was
• correct the typo in the cell reference

Hopefully this helps.

 

[BiocideJ]

Another possible cause of the broken link (for a file on a network drive) is that the computer does not have access to the shared resource.

 

[dalbright]

Hmmm..I'm at a loss, the file name has not changed and is there are no misspellings. These are for salary reviews and the child files are supposed to feed the master files, the issue is that when the manager opens the master file which has the links to the child files, the data is not updating. I just left the office of the person that created these and when she opens the files, she is not getting the message and the updates from the child files are showing. I had her close without saving, I came back to my desk, opened the same file, I get the error message and the updates are NOT showing.

Any other thoughts?

 

[dalbright]

If I unprotect the Source sheets, it looks like it will let me edit the links. I'm going to try a couple of other things that I think might work. Maybe I need to write a macro to unprotect the source sheets, update the links then reprotect the source sheets?

 

[BiocideJ]

If I unprotect the Source sheets, it looks like it will let me edit the links. I'm going to try a couple of other things that I think might work. Maybe I need to write a macro to unprotect the source sheets, update the links then reprotect the source sheets?

I am stumped. I don't see why unprotecting the source would not be necessary on the other person's computer.
If you create a solution that works, please post what you find.

The references don't use INDIRECT do they? That is the only formula that I know sometimes causes issues with links.

 

[dalbright]

I'm not sure what you mean with INDIRECT? the link is this:

\\SRINT2\Reviews\All\FolderName\[excelfile.xlsx].SheetName.!CellReference

One thing I will note is we never had a problem with these working until we started migrating users to Excel 2007....

 

[BiocideJ]

When you go to the Data tab and select Edit Links, what (if any) error message does it say in the Status column?