Hi All,

I'm running the excel file with a parameter which is a path that contains
the files to process. After processing the files a targetfile will be created
using the contents of the files read.

Problem exist if we try to read more than 400+ files and throws popup with
the message
" File could not be accessed
- Make sure the specified folder exists
- Make sure the folder that contains the file is no read-only
- Make sure the file name does not contain any of the following characters <
? [ ] : ""
- Make sure the file/path name does not contain more than 218 characters
"
with the OK button in it and hangs after that. even if we press the OK
button it won't get closed.

I tried debugging the same and found the error
" error -1004
Method 'Open' of object 'Workbooks' failed" while opening the new file.

After this i tried setting the object to nothing before opening the file but
in vain.

Can some one let me know what might be the problem & how can i solve it.

Thanks

Rajesh

Without much idea of the code you are using, I would guess it has something
to do with trying to open a non-existent file.

Is it always on the same file name?
After the same number of files are processed ?
Over a network ?

Add a statement to check it exists: e.g.
Debug.Print GetAttr(YourPathToFileToOpen)

NickHK

"Rajesh T S" <Rajesh T S @discussions.microsoft.com wrote in message
...
Hi All,

I'm running the excel file with a parameter which is a path that contains
the files to process. After processing the files a targetfile will be
created
using the contents of the files read.

Problem exist if we try to read more than 400+ files and throws popup with
the message
" File could not be accessed
- Make sure the specified folder exists
- Make sure the folder that contains the file is no read-only
- Make sure the file name does not contain any of the following characters
<
? [ ] : ""
- Make sure the file/path name does not contain more than 218 characters
"
with the OK button in it and hangs after that. even if we press the OK
button it won't get closed.

I tried debugging the same and found the error
" error -1004
Method 'Open' of object 'Workbooks' failed" while opening the new file.

After this i tried setting the object to nothing before opening the file
but
in vain.

Can some one let me know what might be the problem & how can i solve it.

Thanks

Rajesh

Hi Nick,
Answering to your question
it is not on the same file or list of files.I'll just provide a path to the
excel from where it'll pick up all the .xls files & process the same. This
path might contain any no of files ranging from 1-600. I'm not getting the
error if it is below some 200 files.

The no of files processed is varying every time. Some time it is on 200th
file , some time it might be after 250.

It is on the local machine.


"NickHK" wrote:

Without much idea of the code you are using, I would guess it has something
to do with trying to open a non-existent file.

Is it always on the same file name?
After the same number of files are processed ?
Over a network ?

Add a statement to check it exists: e.g.
Debug.Print GetAttr(YourPathToFileToOpen)

NickHK

"Rajesh T S" <Rajesh T S @discussions.microsoft.com wrote in message
...
Hi All,

I'm running the excel file with a parameter which is a path that contains
the files to process. After processing the files a targetfile will be
created
using the contents of the files read.

Problem exist if we try to read more than 400+ files and throws popup with
the message
" File could not be accessed
- Make sure the specified folder exists
- Make sure the folder that contains the file is no read-only
- Make sure the file name does not contain any of the following characters
<
? [ ] : ""
- Make sure the file/path name does not contain more than 218 characters
"
with the OK button in it and hangs after that. even if we press the OK
button it won't get closed.

I tried debugging the same and found the error
" error -1004
Method 'Open' of object 'Workbooks' failed" while opening the new file.

After this i tried setting the object to nothing before opening the file
but
in vain.

Can some one let me know what might be the problem & how can i solve it.

Thanks

Rajesh

If your using the Dir command to get the name of the next file to process,
this can cause problems if you change files in the same path during the loop
that is using Dir. Perhaps pick up the list of file into an array using a
Dir loop; then use the list in the array to process the files.

--
Regards,
Tom Ogilvy


"Rajesh T S" wrote:

Hi Nick,
Answering to your question
it is not on the same file or list of files.I'll just provide a path to the
excel from where it'll pick up all the .xls files & process the same. This
path might contain any no of files ranging from 1-600. I'm not getting the
error if it is below some 200 files.

The no of files processed is varying every time. Some time it is on 200th
file , some time it might be after 250.

It is on the local machine.


"NickHK" wrote:

Without much idea of the code you are using, I would guess it has something
to do with trying to open a non-existent file.

Is it always on the same file name?
After the same number of files are processed ?
Over a network ?

Add a statement to check it exists: e.g.
Debug.Print GetAttr(YourPathToFileToOpen)

NickHK

"Rajesh T S" <Rajesh T S @discussions.microsoft.com wrote in message
...
Hi All,

I'm running the excel file with a parameter which is a path that contains
the files to process. After processing the files a targetfile will be
created
using the contents of the files read.

Problem exist if we try to read more than 400+ files and throws popup with
the message
" File could not be accessed
- Make sure the specified folder exists
- Make sure the folder that contains the file is no read-only
- Make sure the file name does not contain any of the following characters
<
? [ ] : ""
- Make sure the file/path name does not contain more than 218 characters
"
with the OK button in it and hangs after that. even if we press the OK
button it won't get closed.

I tried debugging the same and found the error
" error -1004
Method 'Open' of object 'Workbooks' failed" while opening the new file.

After this i tried setting the object to nothing before opening the file
but
in vain.

Can some one let me know what might be the problem & how can i solve it.

Thanks

Rajesh

Hi TOM,

I'm doing the same way as you have suggested even then I'm getting the same
error. FYI Find the code below


Public Sub ReadDDI11ForALL()

On Error GoTo ErrHandler
'For Logging
'' Reading the File names into an array
FPathArray = ReadFiles(FOLDER_PATH)
If (FPathArray(0) < "") Then
Set TargetWb = Workbooks.Open(ThisWorkbook.Path & "\Template.xls",
False, False)
TargetWb.Application.DisplayAlerts = False
'Reading the values from the source worksheet
For i = 0 To UBound(FPathArray())

If not (SourceWb is nothing) then set SourceWb = Nothing -- Error
might be here
Set SourceWb = Workbooks.Open(FPathArray(i), False, True)
''''''' Processing File: " & FPathArray(i))
------------------------
------------------------
------------------------
''''''''Data reading completed

''''''''Writing data to CSV using the range object

'Disable the BeforeClose event of the source workbook so
'that it does not ask to save the worksheet with cycle date
custom message
SourceWb.Application.EnableEvents = False
'Close the source workbook
SourceWb.Close (False)
set SourceWb = nothing

Next
TargetWb.Close (False)
set TargetWb = Nothing
End If
Exit Sub
ErrHandler:
' Closing all the opened files Before quitting the application
Set SourceWb = Nothing
Set TargetWb = Nothing
' Logging the error and Quit the application
End Sub



"Tom Ogilvy" wrote:

If your using the Dir command to get the name of the next file to process,
this can cause problems if you change files in the same path during the loop
that is using Dir. Perhaps pick up the list of file into an array using a
Dir loop; then use the list in the array to process the files.

--
Regards,
Tom Ogilvy


"Rajesh T S" wrote:

Hi Nick,
Answering to your question
it is not on the same file or list of files.I'll just provide a path to the
excel from where it'll pick up all the .xls files & process the same. This
path might contain any no of files ranging from 1-600. I'm not getting the
error if it is below some 200 files.

The no of files processed is varying every time. Some time it is on 200th
file , some time it might be after 250.

It is on the local machine.


"NickHK" wrote:

Without much idea of the code you are using, I would guess it has something
to do with trying to open a non-existent file.

Is it always on the same file name?
After the same number of files are processed ?
Over a network ?

Add a statement to check it exists: e.g.
Debug.Print GetAttr(YourPathToFileToOpen)

NickHK

"Rajesh T S" <Rajesh T S @discussions.microsoft.com wrote in message
...
Hi All,

I'm running the excel file with a parameter which is a path that contains
the files to process. After processing the files a targetfile will be
created
using the contents of the files read.

Problem exist if we try to read more than 400+ files and throws popup with
the message
" File could not be accessed
- Make sure the specified folder exists
- Make sure the folder that contains the file is no read-only
- Make sure the file name does not contain any of the following characters
<
? [ ] : ""
- Make sure the file/path name does not contain more than 218 characters
"
with the OK button in it and hangs after that. even if we press the OK
button it won't get closed.

I tried debugging the same and found the error
" error -1004
Method 'Open' of object 'Workbooks' failed" while opening the new file.

After this i tried setting the object to nothing before opening the file
but
in vain.

Can some one let me know what might be the problem & how can i solve it.

Thanks

Rajesh

there is nothing in the code you posted that would indicate a problem. Only
thing I can think of is that your array is bigger than the list of filenames
and when it gets to an empty element after it runs out of real filenames in
the array, it errors.

--
Regards,
Tom Ogilvy


"Rajesh T S" wrote:

Hi TOM,

I'm doing the same way as you have suggested even then I'm getting the same
error. FYI Find the code below


Public Sub ReadDDI11ForALL()

On Error GoTo ErrHandler
'For Logging
'' Reading the File names into an array
FPathArray = ReadFiles(FOLDER_PATH)
If (FPathArray(0) < "") Then
Set TargetWb = Workbooks.Open(ThisWorkbook.Path & "\Template.xls",
False, False)
TargetWb.Application.DisplayAlerts = False
'Reading the values from the source worksheet
For i = 0 To UBound(FPathArray())

If not (SourceWb is nothing) then set SourceWb = Nothing -- Error
might be here
Set SourceWb = Workbooks.Open(FPathArray(i), False, True)
''''''' Processing File: " & FPathArray(i))
------------------------
------------------------
------------------------
''''''''Data reading completed

''''''''Writing data to CSV using the range object

'Disable the BeforeClose event of the source workbook so
'that it does not ask to save the worksheet with cycle date
custom message
SourceWb.Application.EnableEvents = False
'Close the source workbook
SourceWb.Close (False)
set SourceWb = nothing

Next
TargetWb.Close (False)
set TargetWb = Nothing
End If
Exit Sub
ErrHandler:
' Closing all the opened files Before quitting the application
Set SourceWb = Nothing
Set TargetWb = Nothing
' Logging the error and Quit the application
End Sub



"Tom Ogilvy" wrote:

If your using the Dir command to get the name of the next file to process,
this can cause problems if you change files in the same path during the loop
that is using Dir. Perhaps pick up the list of file into an array using a
Dir loop; then use the list in the array to process the files.

--
Regards,
Tom Ogilvy


"Rajesh T S" wrote:

Hi Nick,
Answering to your question
it is not on the same file or list of files.I'll just provide a path to the
excel from where it'll pick up all the .xls files & process the same. This
path might contain any no of files ranging from 1-600. I'm not getting the
error if it is below some 200 files.

The no of files processed is varying every time. Some time it is on 200th
file , some time it might be after 250.

It is on the local machine.


"NickHK" wrote:

Without much idea of the code you are using, I would guess it has something
to do with trying to open a non-existent file.

Is it always on the same file name?
After the same number of files are processed ?
Over a network ?

Add a statement to check it exists: e.g.
Debug.Print GetAttr(YourPathToFileToOpen)

NickHK

"Rajesh T S" <Rajesh T S @discussions.microsoft.com wrote in message
...
Hi All,

I'm running the excel file with a parameter which is a path that contains
the files to process. After processing the files a targetfile will be
created
using the contents of the files read.

Problem exist if we try to read more than 400+ files and throws popup with
the message
" File could not be accessed
- Make sure the specified folder exists
- Make sure the folder that contains the file is no read-only
- Make sure the file name does not contain any of the following characters
<
? [ ] : ""
- Make sure the file/path name does not contain more than 218 characters
"
with the OK button in it and hangs after that. even if we press the OK
button it won't get closed.

I tried debugging the same and found the error
" error -1004
Method 'Open' of object 'Workbooks' failed" while opening the new file.

After this i tried setting the object to nothing before opening the file
but
in vain.

Can some one let me know what might be the problem & how can i solve it.

Thanks

Rajesh