PDA

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



Luttrrt
07-20-2005, 04:41 AM
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
07-20-2005, 10:32 AM
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! :thumb

mdmackillop
07-20-2005, 12:37 PM
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 (http://www.vbaexpress.com/forum/showthread.php?goto=newpost&t=3397)
Regards
MD

(http://www.vbaexpress.com/forum/showthread.php?goto=newpost&t=3397)

Luttrrt
07-21-2005, 04:50 AM
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.