Help - I'm getting desperate

[Doug]

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

 

[Patrick Molloy]

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]

Looks great, I'll try it this afternoon. Thanks for the help!
Doug

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

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

 

[Doug]

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

Looks great, I'll try it this afternoon. Thanks for the help!
Doug

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

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

 

[Patrick Molloy]

check that your file2 is linked....so from the menu, EDIt / LINKS

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

Looks great, I'll try it this afternoon. Thanks for the help!
Doug

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



:

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

 

[Doug]

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

check that your file2 is linked....so from the menu, EDIt / LINKS

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

Looks great, I'll try it this afternoon. Thanks for the help!
Doug

:

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



:

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

 

[Patrick Molloy]

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.

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

check that your file2 is linked....so from the menu, EDIt / LINKS

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

:

Looks great, I'll try it this afternoon. Thanks for the help!
Doug

:

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



:

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

 

[Doug]

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

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.

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

check that your file2 is linked....so from the menu, EDIt / LINKS



:

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

:

Looks great, I'll try it this afternoon. Thanks for the help!
Doug

:

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



:

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