I have some workbooks which contain links to an external data retrieval
service. When a file is opened it takes about 30 seconds for that linked data
to be updated.

My objective is to open each file in turn, wait untill the data comes in
(say 30 seconds), convert the linked cells to values so that they are no
longer volatile and finally save the file to a different folder.

I have no difficulty doing the entire process in a loop EXCEPT that I have a
problem with introducing that delay between each pass of the loop which is
absolutely essential.

The WAIT method does not work because it locks up the machine and prevents
the data from coming in. I have used the ON.TIME method in the past to
trigger a partucular procedure but I'm not sure how to apply it to introduce
a delay between each pass of the loop.

Any other suggestions would be appreciated.

THANK YOU

Ben,

give this a try. Create a new module and paste the code below. You run it
by calling the ProcessFiles subroutine. It works by having the files to be
opened in a collection object. Each file is then opened and "waits" 5
seconds by using OnTime, and then closes the workbook and opens the next
one... and so on.

you'll need to change the part where the files are put in the collection and
you'll need to put your code in the part after the workbook is opened.


Private colFiles As Collection
Private iIndex As Integer
Private curWB As Workbook
Sub ProcessFiles()

Set colFiles = New Collection
Set curWB = Nothing
iIndex = 1

'put all files in the colFiles collection here.
'replace this part with your code
colFiles.Add "D:\TEMP\book1.xls"
colFiles.Add "D:\TEMP\book2.xls"


'Start "looping".
If colFiles.Count 0 Then
OpenFiles
End If

End Sub
Sub OpenFiles()
Dim NextTime As Date


'if a workbook is open, work with it.
If Not curWB Is Nothing Then
'do something with the workbook.
'30 second delay would have expired by now.

'when done. close the workbook and set curwb to nothing
curWB.Close
Set curWB = Nothing
'increment the Index
iIndex = iIndex + 1
End If

'we are done.
If iIndex colFiles.Count Then Exit Sub

If curWB Is Nothing Then
'there is no current workbook. open a new one
Set curWB = Workbooks.Open(colFiles(iIndex))
End If

NextTime = Now + TimeValue("00:00:05")
Application.OnTime NextTime, "OpenFiles"

End Sub




--
Hope that helps.

Vergel Adriano


"Ben" wrote:

I have some workbooks which contain links to an external data retrieval
service. When a file is opened it takes about 30 seconds for that linked data
to be updated.

My objective is to open each file in turn, wait untill the data comes in
(say 30 seconds), convert the linked cells to values so that they are no
longer volatile and finally save the file to a different folder.

I have no difficulty doing the entire process in a loop EXCEPT that I have a
problem with introducing that delay between each pass of the loop which is
absolutely essential.

The WAIT method does not work because it locks up the machine and prevents
the data from coming in. I have used the ON.TIME method in the past to
trigger a partucular procedure but I'm not sure how to apply it to introduce
a delay between each pass of the loop.

Any other suggestions would be appreciated.

THANK YOU

That was helpful, Thank you

"Ben" wrote:

I have some workbooks which contain links to an external data retrieval
service. When a file is opened it takes about 30 seconds for that linked data
to be updated.

My objective is to open each file in turn, wait untill the data comes in
(say 30 seconds), convert the linked cells to values so that they are no
longer volatile and finally save the file to a different folder.

I have no difficulty doing the entire process in a loop EXCEPT that I have a
problem with introducing that delay between each pass of the loop which is
absolutely essential.

The WAIT method does not work because it locks up the machine and prevents
the data from coming in. I have used the ON.TIME method in the past to
trigger a partucular procedure but I'm not sure how to apply it to introduce
a delay between each pass of the loop.

Any other suggestions would be appreciated.

THANK YOU

Vergel, I have one more request. As part of the solution, you suggest
creating a collection as follows: colFiles.Add "D:Temp\book.xls" ... etc
Instead of explicitly specifying each file I'd like specify one folder and
then add all the files in that folder to colFiles.

Thanks again

"Ben" wrote:

That was helpful, Thank you

"Ben" wrote:

I have some workbooks which contain links to an external data retrieval
service. When a file is opened it takes about 30 seconds for that linked data
to be updated.

My objective is to open each file in turn, wait untill the data comes in
(say 30 seconds), convert the linked cells to values so that they are no
longer volatile and finally save the file to a different folder.

I have no difficulty doing the entire process in a loop EXCEPT that I have a
problem with introducing that delay between each pass of the loop which is
absolutely essential.

The WAIT method does not work because it locks up the machine and prevents
the data from coming in. I have used the ON.TIME method in the past to
trigger a partucular procedure but I'm not sure how to apply it to introduce
a delay between each pass of the loop.

Any other suggestions would be appreciated.

THANK YOU

Ben,

try this

Dim colFiles As New Collection
Dim strPath As String
Dim strFile As String

strPath = "D:\TEMP\"

strFile = Dir(strPath)

While strFile < ""
colFiles.Add strPath & strFile
strFile = Dir
Wend

--
Hope that helps.

Vergel Adriano


"Ben" wrote:

Vergel, I have one more request. As part of the solution, you suggest
creating a collection as follows: colFiles.Add "D:Temp\book.xls" ... etc
Instead of explicitly specifying each file I'd like specify one folder and
then add all the files in that folder to colFiles.

Thanks again

"Ben" wrote:

That was helpful, Thank you

"Ben" wrote:

I have some workbooks which contain links to an external data retrieval
service. When a file is opened it takes about 30 seconds for that linked data
to be updated.

My objective is to open each file in turn, wait untill the data comes in
(say 30 seconds), convert the linked cells to values so that they are no
longer volatile and finally save the file to a different folder.

I have no difficulty doing the entire process in a loop EXCEPT that I have a
problem with introducing that delay between each pass of the loop which is
absolutely essential.

The WAIT method does not work because it locks up the machine and prevents
the data from coming in. I have used the ON.TIME method in the past to
trigger a partucular procedure but I'm not sure how to apply it to introduce
a delay between each pass of the loop.

Any other suggestions would be appreciated.

THANK YOU