Popping up message box with "Make Sure the Specified folder Exist"

[Guest]

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

 

[NickHK]

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

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

 

[Guest]

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.

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

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

 

[Guest]

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

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.

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

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

 

[Guest]

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

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

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.

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

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

 

[Guest]

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

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

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

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.


:

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

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

 

[Guest]

Hi Tom.
thanks for the response

I can assure you that the array will have valid file names that we have
read. Find below the whole code for your information. have a walk through and
let me know if any instance of possible error
-------------------------------------------------------------------
Public Sub ReadMainIPLuxembourg()
Dim IsFileDeleted As Boolean
Dim FPathArray() As String
Dim IsValid As Boolean
Dim Proceed As Boolean
Dim sRows As Integer
Dim sColumns As Integer
Dim CellNum As Integer
Dim FilePath As String
Dim FundRange As Range
Dim RowCount As Integer
Dim FOLDER_PATH As String
Dim strFundName As String
If Trim$(strDataSourcePath) = "" Then Exit Sub
FOLDER_PATH = strDataSourcePath & "\Luxembourg\" -- Source folder

FPathArray = ReadFiles(FOLDER_PATH) -- Reading all the file(s) into
the array.
If (FPathArray(0) <> "") Then
Set TargetWb = Workbooks.Open(ThisWorkbook.Path & "\Template.xls",
False, False) -- OPening the target file to

write
'Reading the values from the source worksheet
For i = 0 To UBound(FPathArray())
sRows = 2
CellNum = 16
Proceed = True
If Not (SourceWb Is Nothing) Then Set SourceWb = Nothing
If Trim$(FPathArray(i)) <> "" Then Set SourceWb =
Workbooks.Open(FPathArray(i), False, True) Else GoTo NextFile


Logging.Publish (" Processing File: " & FPathArray(i))
'Check if the source file has a valid file format
IsValid = IsValidSourceMainIPLux
If (IsValid) Then
Logging.Publish (" Data reading started.")
'Loop till the rows are present in the Account Calculation
Chart
While Proceed
sColumns = 1
If (Trim$(SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BC"
& CellNum).Value) = "") Then
Proceed = False
Else
'For Fund Name
strFundName =
SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BA" & CellNum).Value
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value = strFundName
'For Basis
sColumns = sColumns + 1
If Not (Trim$(strFundName) = "" Or
Trim$(SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BB" &

CellNum).Value) = "") Then
' For Basis
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BB" & CellNum).Value
'For Current Cycle Date
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_MAIN_BLR).Range("AA14").Value
'For Fund Local Currency
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_MAIN_BLR).Range("L14").Value
'For Interest Income - IP1
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BL" & CellNum).Value
'For Interest Income Equalization - IP1
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BM" & CellNum).Value
'For Amort on FI Zero Coupon - IP3
sColumns = sColumns + 3
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BH" & CellNum).Value
'For Amort on FI Zero Coupon Equalization - IP3
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BI" & CellNum).Value
'For Real G/L on FI - IP4
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BJ" & CellNum).Value
'For Real G/L on FI Equalization - IP4
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BK" & CellNum).Value
'For Direct Expenses - IP9
sColumns = sColumns + 7
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BN" & CellNum).Value
'For Direct Expenses Equalization - IP9e
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BO" & CellNum).Value
'For Indirect Expenses - IP10
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BP" & CellNum).Value
'For Indirect Expenses Equalization- IP10e
sColumns = sColumns + 1
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BQ" & CellNum).Value
'For Shares Outstanding Activity
sColumns = sColumns + 9
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_ACC).Range("BE" & CellNum).Value
'For YAR
sColumns = sColumns + 3
TargetWb.Sheets("Sheet1").Cells(sRows,
sColumns).Value =

SourceWb.Sheets(SHEET_NAME_FOR_OP_BAL).Range("AC14").Value
sRows = sRows + 1
Else
If Trim$(strFundName) = "" Then
Logging.Publish ("The FUND NAME VALUE IS
BLANK AND SKIPPING THE SAME")
Else
Logging.Publish ("The BASIS VALUE FOR THE
FUND " & strFundName & " IS BLANK AND SKIPPING THE

SAME")
End If

End If
CellNum = CellNum + 1
End If
Wend
Logging.Publish (" Data reading completed.")
If (sRows > 2) Then
Logging.Publish (" Moving data to CSV file started.")
'FilePath = strDataSourcePath & "\" & "TargetExcel.csv"
'getting the range of data from the excel
Set FundRange = TargetWb.Sheets("Sheet1").Rows("2:" &
sRows - 1)
RowCount = FundRange.Rows.Count
'write the data to the file in the CSV format
WriteToFile RowCount:=RowCount, FundRange:=FundRange
'delete the data present in te excel which is copied to
the CSV
FundRange.Delete
Logging.Publish (" Moving data to CSV file completed.")
End If
Else
Logging.LogError FPathArray(i), blnNoDataExists
End If
'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
Logging.Publish (" Processing of File: " & FPathArray(i) & "
Completed.")
Next
TargetWb.Close (False)
Set TargetWb = Nothing
End If
End Sub

-----------------------------------------------------------------------------

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

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

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


:

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.


:

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

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

 

[NickHK]

Hard to say much as you did not include the important ReadFiles routine.

Any chance you have some file names with Unicode characters in them ?
Does the length of the path + filename exceed MAX_PATH (260 characters) ?

NickHK

Hi Tom.
thanks for the response

I can assure you that the array will have valid file names that we have
read. Find below the whole code for your information. have a walk through and
let me know if any instance of possible error
-------------------------------------------------------------------
Public Sub ReadMainIPLuxembourg()
Dim IsFileDeleted As Boolean
Dim FPathArray() As String
Dim IsValid As Boolean
Dim Proceed As Boolean
Dim sRows As Integer
Dim sColumns As Integer
Dim CellNum As Integer
Dim FilePath As String
Dim FundRange As Range
Dim RowCount As Integer
Dim FOLDER_PATH As String
Dim strFundName As String
If Trim$(strDataSourcePath) = "" Then Exit Sub
FOLDER_PATH = strDataSourcePath & "\Luxembourg\" -- Source folder

FPathArray = ReadFiles(FOLDER_PATH) -- Reading all the file(s) into
the array.
If (FPathArray(0) <> "") Then
Set TargetWb = Workbooks.Open(ThisWorkbook.Path & "\Template.xls",
False, False) -- OPening the target file to

---- CUT --------------

 

[Guest]

Hi Nick,

Find below the code for ReadFiles procedure. I assume that the length of
file name does not exceed the MAX_PATH, since i'll be using the local machine.
---------------------------------
Public Function ReadFiles(ByVal FolderPath As String) As String()
Dim FileCount As Integer
Dim Files As String
Dim FileArray() As String
Dim FilePathArray() As String
FileCount = 0
Files = Dir(FolderPath & "*.xls")
ReDim Preserve FilePathArray(0)
While Files <> ""
ReDim Preserve FileArray(0 To FileCount)
ReDim Preserve FilePathArray(0 To FileCount)
FileArray(FileCount) = Files
FilePathArray(FileCount) = FolderPath & Files
FileCount = FileCount + 1
Files = Dir()
Wend
ReadFiles = FilePathArray
End Function

 

[NickHK]

Strip down your code to something like that below, to check whether the
problem really is something to do with filename:

Private Sub CommandButton1_Click()
Call ReadMainIPLuxembourg
End Sub


Public Sub ReadMainIPLuxembourg()
Dim FPathArray() As String
Dim FOLDER_PATH As String

Dim i As Long
Dim SourceWb As Workbook

FOLDER_PATH = "C:\Excel Test\"

FPathArray = ReadFiles(FOLDER_PATH)

For i = 0 To UBound(FPathArray())
Worksheets(1).Range("A1").Offset(i, 0).Value = FPathArray(i)

Set SourceWb = Workbooks.Open(FPathArray(i))

With SourceWb
Debug.Print .Name
.Close False
End With
Next

End Sub

NickHK

 

[Guest]

Hi NICK / TOM

Is there any restrictions on No of files that need to be processed something
like only some no of file handlers were allowed at a time ?
Thanks
Rajesh

 

[NickHK]

Well, you only open then close the WB so that is not a concern.
And VBA can handle more than a 300 strings in an array.

NickHK

 

[Guest]

Thanks for information and all the support NICK and TOM .

Thanks for all the help

 

[NickHK]

For the record, what was the cause of the error ?

NickHK

 

[Guest]

The problem related to the opening large no of excel files and in between it
pops up a message box which need to be avoided while running the macros even
after using Displayalerts = false. This pop up comes up only when no of files
are more than 250.

 

[NickHK]

What does the msgbox say ?
Something about memory ?

You can open and close WBs all day, but maybe your code is not releasing
some references/WB/resources as you think.

Copying WSs in a loop will raise memory problems sfter a while. Possibly
copying graphics and chrats also.

NickHK

The problem related to the opening large no of excel files and in between it
pops up a message box which need to be avoided while running the macros even
after using Displayalerts = false. This pop up comes up only when no of files
are more than 250.

---------------- CUT ----------------------

 

[Guest]

The Message box will have the below 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

In My case I'm reading and writing the same into another file and closing
it. This happens smoothly if the no. of files is less than 250, other wise it
is popping up the above message

As you have mentioned even i think it might be some thing related to memory,
since if i try the same thing after some an hour or so... then it is running
fine..

This is where i'm getting confused..

 

[NickHK]

From the code you have posted and errors you are getting, I'm not convinced
it's a memory problem but rather something to do with the files/file names
you are trying to open.
As I cannot see your folder listing, I can't say

NickHK

The Message box will have the below 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

In My case I'm reading and writing the same into another file and closing
it. This happens smoothly if the no. of files is less than 250, other wise it
is popping up the above message

As you have mentioned even i think it might be some thing related to memory,
since if i try the same thing after some an hour or so... then it is running
fine..

This is where i'm getting confused..

What does the msgbox say ?
Something about memory ?

You can open and close WBs all day, but maybe your code is not releasing
some references/WB/resources as you think.

Copying WSs in a loop will raise memory problems sfter a while. Possibly
copying graphics and chrats also.

NickHK

between
it macros
even of
files
---------------- CUT ----------------------

 

[Guest]

FYI, the folder structure is as follows

SOURCE
---- AXA --> 46 Files
-------- Unrealised --> 20 Files
---- DDI11 --> 1 File
---- Ireland -- > 2 File
-------- Dist_carry --> 1 File
-------- IP9ANd10 --> 1 File
---- Luxembourg --> 266 Files
---- UK --> 1 File

Totally around 306 files, some times it stops after processing 150 file,
sometimes it is 250...

From the code you have posted and errors you are getting, I'm not convinced
it's a memory problem but rather something to do with the files/file names
you are trying to open.
As I cannot see your folder listing, I can't say

NickHK

The Message box will have the below 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

In My case I'm reading and writing the same into another file and closing
it. This happens smoothly if the no. of files is less than 250, other wise it
is popping up the above message

As you have mentioned even i think it might be some thing related to memory,
since if i try the same thing after some an hour or so... then it is running
fine..

This is where i'm getting confused..

What does the msgbox say ?
Something about memory ?

You can open and close WBs all day, but maybe your code is not releasing
some references/WB/resources as you think.

Copying WSs in a loop will raise memory problems sfter a while. Possibly
copying graphics and chrats also.

NickHK

The problem related to the opening large no of excel files and in between
it
pops up a message box which need to be avoided while running the macros
even
after using Displayalerts = false. This pop up comes up only when no of
files
are more than 250.

:

For the record, what was the cause of the error ?

NickHK

Thanks for information and all the support NICK and TOM .

Thanks for all the help

:

Well, you only open then close the WB so that is not a concern.
And VBA can handle more than a 300 strings in an array.

NickHK
---------------- CUT ----------------------

 

[NickHK]

Yes, but you need to check (or Debug.Print) each path/file name in your code
before you try to open, to determine why it is failing.
Maybe the path/filename you are using in .Open is not what you think,
contains Unicode/unexpected characters, permission denied,
invalid/corruption etc.

NickHK

FYI, the folder structure is as follows

SOURCE
---- AXA --> 46 Files
-------- Unrealised --> 20 Files
---- DDI11 --> 1 File
---- Ireland -- > 2 File
-------- Dist_carry --> 1 File
-------- IP9ANd10 --> 1 File
---- Luxembourg --> 266 Files
---- UK --> 1 File

Totally around 306 files, some times it stops after processing 150 file,
sometimes it is 250...

From the code you have posted and errors you are getting, I'm not convinced
it's a memory problem but rather something to do with the files/file names
you are trying to open.
As I cannot see your folder listing, I can't say

NickHK

The Message box will have the below 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

In My case I'm reading and writing the same into another file and closing
it. This happens smoothly if the no. of files is less than 250, other

wise
it

is popping up the above message

As you have mentioned even i think it might be some thing related to memory,
since if i try the same thing after some an hour or so... then it is running
fine..

This is where i'm getting confused..


:

What does the msgbox say ?
Something about memory ?

You can open and close WBs all day, but maybe your code is not releasing
some references/WB/resources as you think.

Copying WSs in a loop will raise memory problems sfter a while. Possibly
copying graphics and chrats also.

NickHK

The problem related to the opening large no of excel files and in between
it
pops up a message box which need to be avoided while running the macros
even
after using Displayalerts = false. This pop up comes up only when

no
of

files
are more than 250.

:

For the record, what was the cause of the error ?

NickHK

Thanks for information and all the support NICK and TOM .

Thanks for all the help

:

Well, you only open then close the WB so that is not a concern.
And VBA can handle more than a 300 strings in an array.

NickHK
---------------- CUT ----------------------