Auto refreshing data via VBA

[Stonewall Rubberbow]

I have employees who use spreadsheets in different workbooks to indicate
their area's issues and I use a spreadsheet to combine the important pieces
into my own workbook. I want to have my workbook automatically update every
3-5 minutes without having to hit a refresh button.
Ultimately I'd like to have their workbooks do the same thing, so that I can
have cells that link from them to me and from me to them, so they can see
changes I'd like for them to make.
Can I use VBA to write a procedure to refresh the links? If it's possible,
can you write out the whole code I should use?; I'm not familiar enough with
VBA to do anything more than copy/paste and clap my hands with glee.

 

[JLatham]

You need a Workbook_Open event handler - that starts running when you open a
workbook if it exists. It would look something like this:
Private Sub Workbook_Open()
'runs for the whole time the workbook is open
Dim AllDone As Boolean ' initially and always FALSe
Dim LastUpdate As Long ' holds Timer value
Dim UpdateInterval As Long ' holds seconds between updates

'change 5 to whatever number of minutes you want
UpdateInterval = 5 * 60 ' number of seconds between updates
LastUpdate = Timer ' get current timer value
Do Until (AllDone)
DoEvents ' let other work get done
If Timer > LastUpdate + UpdateInterval Then
UpdateFromJack
UpdateFromJill
LastUpdate = Timer ' reset
End If
Loop
End Sub

To get that into the proper place in the workbook(s), right click on the
little Excel icon immediately to the left of the word File in the menu
toolbar, not the Excel icon at the far upper left corner of the Excel window.
Choose [View Code] from the list that appears and paste that code into that
area.

Because the boolean variable AllDone never gets set to True, the loop will
run forever as long as the workbook is open, and will restart each time it is
reopened. the DoEvents statement is critical - without it, you'd just go
into this loop and stay there and nothing else will get done at all.

Now, you're going to have to do something yourself about the
UpdateFromJack
and
UpdateFromJill entries
in that code.

Those are made-up names for public Subs that would be elsewhere in your
workbook, perhaps the result of recording a macro that performs the specific
updates from the other people. Each person's workbook would have slightly
different routines because they're each updating from different workbooks.

Now, I cannot help you with the code that would be inside of those routines
since I don't know what process you're using to get the updates. You can
begin by recording a macro while going through the processes - that's going
to most likely just give you a hard-coded, inflexible solution that will need
customization and tweaking to make robust enough to deal with the changing
layout and content of the workbooks involved. But it's a start.

In the meantime - don't even put in the Workbook_Open() event I've shown
above - don't do that until you have the update macros built and ready to be
used, and then substitute their names for the sample names I put into it.

I hope this helps you in getting started in creating a viable solution.