Sleeper: Macro to Open workbook and Update Links Automatically (Solved)

[Luttrrt]

Hi,

I recently got this code to work for me and thought it might be helpful to others.The macro basically opens up other workbooks, updates the links, saves and closes, moving on to the next workbook.You can do as many workbooks as you like.I've got around 15 and it just flies through them.

Sub GetFile() 
    Workbooks.Open "Put Workbook path here inc the .xls", UpdateLinks:=1 
    ActiveWorkbook.Close SaveChanges:=True 
    Workbooks.Open "Put Workbook path here inc the .xls", UpdateLinks:=1 
    ActiveWorkbook.Close SaveChanges:=True 
    Sheets("Name of sheet containing the macro").Select 
    Range("A5").Select 
    ActiveCell.Value = "Completed" 
    Range("A5").Select 
End Sub

Regards,
Rowland

[Cyberdude]

Am I correct in assumming that all you want to do is to update the links? No criticism ... I just want to understand.

OK, for your next trick tell me how to keep one of my workbooks from suffering from the dreaded "Locked for Edit" condition. I thought I had it solved by running a macro to do my links (i.e., get the values) on opening, then deleting the links after I have the values. But suddenly this one workbook started getting the "L for E" condition apparently after I save it. Phffft!

Oh, and welcome to the forum!

[mdmackillop]

Hi Rowland,
This is the sort of thing we ask our members to submit as a KB Item. There is a post here which explains things. How To Submit An Entry To The KB
Regards
MD

[Luttrrt]

Cyberdude, are these regular links that you want to keep or just once offs.I basically use this code where I have once central spreadsheet where i put all my data and then have other workbooks which link into that.I use the macor to update these without having to go into every workbook individually.

That L&E happens to me sometimes.Think its a fault in Excel that just still registrers that you're in the file when you're not.If you reboot your PC, it takes you out of it.

You could incorporate a Paste Special - Values into the code to get rid of the links.