Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refreshing data via VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refreshing data via VBA
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. "Stonewall Rubberbow" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple MS Query with Data Parameters Not Auto-refreshing | Excel Discussion (Misc queries) | |||
Auto refreshing and printing a form with data from each row of a spreadsheet | Excel Discussion (Misc queries) | |||
Tool for auto loading/refreshing/updating/etc... | Excel Programming | |||
Auto Refreshing - values | Excel Programming | |||
Auto Refreshing: data retreival from webpage | Excel Programming |