FileCopy Command Giving "Subscript Out of Range" Error Message

[Jim Hagan]

I am using the following statements to copy a file from one location to
another and getting an error message "subscript out of range" on the FileCopy
command. (Note: I used a bunch of debug.print statements to determine the
offending line).

Dim ofsFileSys As New FileSystemObject

If ofsFileSys.FileExists(SourceFile) Then
FileCopy SourceFile, DestinationFile ' Copy source to
destination
end if

Both the Source and Destination files exist on the same network drive.

The SourceFile is being created by a data acquisition system and is updated
every few seconds. My macro is programmed to copy the latest SourceFile to
the DestinationFile every 20 seconds or so and grab the latest data entry
from the newly created DestinationFile.

For the most part, the macro works fine. But every so often I get the
"Subscript Out of Range" error message associated with the FileCopy command.
I cannot go into the debugger and repeat the error, since it seems to occur
randomly.

I suspect the problem lies with copying the file and the same time it is
being updated, but I don't know enough about how files are handled by
Windows.

Any ideas as to what may be causing the problem or suggestions of what to do
about it would be appreciated.

My operating system is Windows 2000.

Thanks for your help,

Jim

 

[Damon Longworth]

Try adding an error handler. If your routine coincides with the file
creation, wait a few seconds and try again or wait for the next 20 second
interval.

 

[Jim Hagan]

Thanks Damon,

I did have an error handler in the subroutine, but it apparently didn't
capture the error. Interestingly enough, the error handler from the calling
routine is the one that reported the error. Which gets me thinking that
perhaps the error handler in the subroutine has an error in it as well!!
I'll check this out. Thanks for your help.

Jim