Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
I have 2 computers reading from drive X. Computer1 has File1 which is open and
constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
with file2 you can use the Application.OnTime method to call a sub that reads
the data. the sub would just need to set of the methd again when its done. in a standard module (ALT+F11 to open the VBA editor, then Insert/Module) paste this Option Explicit Global bKillTimer As Boolean Sub StartTime() Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End Sub Sub FetchData() ThisWorkbook.UpdateLink Name:="C:\testing\test.xls", Type:=xlExcelLinks 'start timer again If Not bKillTimer Then Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End If End Sub Sub stoptimer() bKillTimer = True End Sub "Doug" wrote: I have 2 computers reading from drive X. Computer1 has File1 which is open and constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
Looks great, I'll try it this afternoon. Thanks for the help!
Doug "Patrick Molloy" wrote: with file2 you can use the Application.OnTime method to call a sub that reads the data. the sub would just need to set of the methd again when its done. in a standard module (ALT+F11 to open the VBA editor, then Insert/Module) paste this Option Explicit Global bKillTimer As Boolean Sub StartTime() Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End Sub Sub FetchData() ThisWorkbook.UpdateLink Name:="C:\testing\test.xls", Type:=xlExcelLinks 'start timer again If Not bKillTimer Then Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End If End Sub Sub stoptimer() bKillTimer = True End Sub "Doug" wrote: I have 2 computers reading from drive X. Computer1 has File1 which is open and constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
I tried your code. I substituted your filename "C:\testing\test.xls" for my
file name. I get this error message... Method 'Update Link' of object '_Workbook' failed. any ideas? Doug "Doug" wrote: Looks great, I'll try it this afternoon. Thanks for the help! Doug "Patrick Molloy" wrote: with file2 you can use the Application.OnTime method to call a sub that reads the data. the sub would just need to set of the methd again when its done. in a standard module (ALT+F11 to open the VBA editor, then Insert/Module) paste this Option Explicit Global bKillTimer As Boolean Sub StartTime() Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End Sub Sub FetchData() ThisWorkbook.UpdateLink Name:="C:\testing\test.xls", Type:=xlExcelLinks 'start timer again If Not bKillTimer Then Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End If End Sub Sub stoptimer() bKillTimer = True End Sub "Doug" wrote: I have 2 computers reading from drive X. Computer1 has File1 which is open and constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
check that your file2 is linked....so from the menu, EDIt / LINKS
"Doug" wrote: I tried your code. I substituted your filename "C:\testing\test.xls" for my file name. I get this error message... Method 'Update Link' of object '_Workbook' failed. any ideas? Doug "Doug" wrote: Looks great, I'll try it this afternoon. Thanks for the help! Doug "Patrick Molloy" wrote: with file2 you can use the Application.OnTime method to call a sub that reads the data. the sub would just need to set of the methd again when its done. in a standard module (ALT+F11 to open the VBA editor, then Insert/Module) paste this Option Explicit Global bKillTimer As Boolean Sub StartTime() Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End Sub Sub FetchData() ThisWorkbook.UpdateLink Name:="C:\testing\test.xls", Type:=xlExcelLinks 'start timer again If Not bKillTimer Then Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End If End Sub Sub stoptimer() bKillTimer = True End Sub "Doug" wrote: I have 2 computers reading from drive X. Computer1 has File1 which is open and constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
My files are linked. I'm assuming file name in the code is the source file.
This is the file name I used. Name:="C:\Documents and Settings\Doug Loewen\My Documents\Doug\CKUA\PledgeTrackerDemo.xls" Could there be a problem with the file name, or is there something wrong wilth using ThisWorkbook.UpdateLink? Doug "Patrick Molloy" wrote: check that your file2 is linked....so from the menu, EDIt / LINKS "Doug" wrote: I tried your code. I substituted your filename "C:\testing\test.xls" for my file name. I get this error message... Method 'Update Link' of object '_Workbook' failed. any ideas? Doug "Doug" wrote: Looks great, I'll try it this afternoon. Thanks for the help! Doug "Patrick Molloy" wrote: with file2 you can use the Application.OnTime method to call a sub that reads the data. the sub would just need to set of the methd again when its done. in a standard module (ALT+F11 to open the VBA editor, then Insert/Module) paste this Option Explicit Global bKillTimer As Boolean Sub StartTime() Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End Sub Sub FetchData() ThisWorkbook.UpdateLink Name:="C:\testing\test.xls", Type:=xlExcelLinks 'start timer again If Not bKillTimer Then Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End If End Sub Sub stoptimer() bKillTimer = True End Sub "Doug" wrote: I have 2 computers reading from drive X. Computer1 has File1 which is open and constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
If any cells pull data from another file, then a link will have been created.
You'll see this in two ways: (1) a linked cell will show the linked workbook in the formula and (2) under the Edit menu, the sub-menu Links will be enabled ...click on that and the link files window will open. You'll see your file there. The code essentially clicks the update link buttons - ie refreshes the data. "Doug" wrote: My files are linked. I'm assuming file name in the code is the source file. This is the file name I used. Name:="C:\Documents and Settings\Doug Loewen\My Documents\Doug\CKUA\PledgeTrackerDemo.xls" Could there be a problem with the file name, or is there something wrong wilth using ThisWorkbook.UpdateLink? Doug "Patrick Molloy" wrote: check that your file2 is linked....so from the menu, EDIt / LINKS "Doug" wrote: I tried your code. I substituted your filename "C:\testing\test.xls" for my file name. I get this error message... Method 'Update Link' of object '_Workbook' failed. any ideas? Doug "Doug" wrote: Looks great, I'll try it this afternoon. Thanks for the help! Doug "Patrick Molloy" wrote: with file2 you can use the Application.OnTime method to call a sub that reads the data. the sub would just need to set of the methd again when its done. in a standard module (ALT+F11 to open the VBA editor, then Insert/Module) paste this Option Explicit Global bKillTimer As Boolean Sub StartTime() Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End Sub Sub FetchData() ThisWorkbook.UpdateLink Name:="C:\testing\test.xls", Type:=xlExcelLinks 'start timer again If Not bKillTimer Then Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End If End Sub Sub stoptimer() bKillTimer = True End Sub "Doug" wrote: I have 2 computers reading from drive X. Computer1 has File1 which is open and constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - I'm getting desperate
I got it to work. I changed "ThisWorkbook" to "ActiveWorkbook" and it ran
without the error message. Thank you again for all your help and patience. Doug "Patrick Molloy" wrote: If any cells pull data from another file, then a link will have been created. You'll see this in two ways: (1) a linked cell will show the linked workbook in the formula and (2) under the Edit menu, the sub-menu Links will be enabled ...click on that and the link files window will open. You'll see your file there. The code essentially clicks the update link buttons - ie refreshes the data. "Doug" wrote: My files are linked. I'm assuming file name in the code is the source file. This is the file name I used. Name:="C:\Documents and Settings\Doug Loewen\My Documents\Doug\CKUA\PledgeTrackerDemo.xls" Could there be a problem with the file name, or is there something wrong wilth using ThisWorkbook.UpdateLink? Doug "Patrick Molloy" wrote: check that your file2 is linked....so from the menu, EDIt / LINKS "Doug" wrote: I tried your code. I substituted your filename "C:\testing\test.xls" for my file name. I get this error message... Method 'Update Link' of object '_Workbook' failed. any ideas? Doug "Doug" wrote: Looks great, I'll try it this afternoon. Thanks for the help! Doug "Patrick Molloy" wrote: with file2 you can use the Application.OnTime method to call a sub that reads the data. the sub would just need to set of the methd again when its done. in a standard module (ALT+F11 to open the VBA editor, then Insert/Module) paste this Option Explicit Global bKillTimer As Boolean Sub StartTime() Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End Sub Sub FetchData() ThisWorkbook.UpdateLink Name:="C:\testing\test.xls", Type:=xlExcelLinks 'start timer again If Not bKillTimer Then Application.OnTime Now + TimeValue("00:01:00"), "Fetchdata" End If End Sub Sub stoptimer() bKillTimer = True End Sub "Doug" wrote: I have 2 computers reading from drive X. Computer1 has File1 which is open and constantly being updated throughout the day (18 hours per day). Computer2 has File2 which is to read "Totals" data from File1. I am using formulii in File2 to get this info. Sample " =X:\Folder\[File1.xls]Totals'!$D$2. " Both files are open all day. What I would like to do is have File2 on Computer2 automatically update throughout the day, without the user having to do anything. (They are just observing the total changes). My first preference would be to have File2 automatically update every 2 or 3 minutes as long as the file is open. Is there some code I can use to do this? If Auto Update is not possible, my second choice would be a command button on File2 called "Update", that the observer could click to activate the update on File2. What code wold I use for this? I'm using Excel 2003 not 2007. Hoping someone can help me. Thanks Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
STILL DESPERATE FOR HELP!!! | Excel Discussion (Misc queries) | |||
Desperate | Excel Discussion (Misc queries) | |||
Desperate...please help! | Excel Worksheet Functions | |||
In desperate need of help.... | Excel Discussion (Misc queries) | |||
Sorry, But Getting Desperate | Excel Programming |