Help with Links

[seljo]

I am creating a spreadsheet to measure wait times. I want to have one that the sec. inputs data into and uses to monitor how long people wait but I also want to have a seperate spread sheet that our Managers can view to look at how long people have been waiting and weahter or not to go help. So here is the problem I am having I have a spread sheet set up with links to the master sheet, the one the sec. inputs data, but it only updates when it is opened. So what I want to do is update all the links within a workbook every minute or so, this is so that our Managers can get a realtime view of our waiting room. I am open for any suggestions here.

Thanks,

 

[hatman]

how about this... in a standard module:

Sub link_update()

    ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
    
    Application.OnTime Now + TimeValue("00:01:00"), "link_update"

End Sub

in the Open Event of the ThisWorkbook Class Module, add

 call link_update

It should update your links every minute until you close the book...

 

[bassls]

I get a runtime 1004 error. Should ThisWorkbook read as the actual name of the destination spreadsheet, ie destworkbook.xls? Should LinkSourses actually read as the name of the source spreadsheet, ie virtual source.xls?

 

[hatman]

This is a generic piece of code that should work with any workbook that has links to one or more other workbooks. However, the code needs to be placed in the workbook that contains the links. It should not cause an error even if the workbook has no links.

That being said, the code requires a workbook object... you could replace ThisWorkbook with any workbook object you like... Activeworkbook, Workbooks("anything"), etc.

As far as linksources, you don't really want to raplace this... the .Linksources is a collection of all of the links in the workbook, and allows you to update the links to all workbooks.

What version of Excel are you running? There are a variety of causes for runtime errors, which typ eis this one, and on which line does it occur?

 

[bassls]

there 3 seperate workbooks that are linked to one "Source" workbook. Does this code go in each of the 3 destinations or the one source? We are running Excel 2000.

Thanks

 

[hatman]

It needs to be inserted into every destination workbook... that is the location where the link update action needs to occur.

let me know how you make out... my original code should work without substantial modifications.

 

[bassls]

I appologise. I'm not a programmer. I need some more help with this. In VB editor, I right click on sheet 1 and chose VEIW CODE. I copy and paste the code that you gave me then closed VBE. A minute later, I see an error saying the macro, "link update" cannot be found. I am also confused as to what standard and class modules are.

 

[hatman]

Sounds like you placed all o fth ecode into the Class Module for the Sheet. Be sure you delete this code before proceeding.

It breaks down like this:
Each workbook has several class modules, one for each sheet, and one for the entire workbook. These class modules all you to add code that will be run when certain events happen. these events may be isolated only to a certain sheet, or may be captured from anywhere in the book. There ar eno standard modules in a workbook, unless you add one or more manually.

The code that I recommended comes in 2 parts. That first part needs to go in a standard module. When you right-click on th esheet tab and select View code, there should be a pane to the side of the window calle dthe Project Explorer. It has a tree-view of all open books (plus any addins that you have installed). If you see nothing that looks like this, hit Ctrl+R. In the project explorer, expand the selction for the appropriate workbook, then expand the selection for Microsoft Excel Objects for that workbook, and double click ThisWorkbook. Insert this code into the code pane at the right:

Private Sub Workbook_Open()
call link_update
End Sub

What this does is to run the Link_Update routine when the workbook is opened...

Now from the Top menu, select Insert->Module... a new blank standard module pane will appear toward the right of the VBA window. Insert the Link_Update routine as posted above... change the time interval as needed (I posted it with a 1 minute interval). Now press F5. to start the code running... it should update the links on the time interval you specifyed, until you save and close the workbook...

Hope this helps... let me know ho wyou make out.

 

[bassls]

Hat,

That seems to work correctly now. I am going to change the increment to 1 hour and let it run until tomorrow before adding it to the rest of the sheets. I very much appreciate your assistance and patience with me. This board is an invaluable asset for non-programmers like myself.

Thanks again,
BAS

 

[hatman]

No problem. Let me know how you make out otmorrow: I want to make sure this does what you want... if ther eis a problem, we'll work it out ...