Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileCopy Command Giving "Subscript Out of Range" Error Message
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileCopy Command Giving "Subscript Out of Range" Error Message
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. On Error Resume Next "Jim Hagan" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileCopy Command Giving "Subscript Out of Range" Error Message
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 "Damon Longworth" wrote: 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. On Error Resume Next "Jim Hagan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Message "Subscript our of range" | Excel Discussion (Misc queries) | |||
"Subscript out of range Runtime Error 9" | Excel Programming | |||
strange "subscript out of range" error! | Excel Programming | |||
SaveAs "subscript out of range" error (COM - SOAP) | Excel Programming | |||
SaveAs "subscript out of range" error (COM - SOAP) | Excel Programming |