|
Popping up message box with "Make Sure the Specified folder Exist"
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 |
Popping up message box with "Make Sure the Specified folder Exist"
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 |
Popping up message box with "Make Sure the Specified folder Ex
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 |
Popping up message box with "Make Sure the Specified folder Ex
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 |
Popping up message box with "Make Sure the Specified folder Ex
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 |
Popping up message box with "Make Sure the Specified folder Ex
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 |
Popping up message box with "Make Sure the Specified folder Ex
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("B B" & 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("AA 14").Value 'For Fund Local Currency sColumns = sColumns + 1 TargetWb.Sheets("Sheet1").Cells(sRows, sColumns).Value = SourceWb.Sheets(SHEET_NAME_FOR_MAIN_BLR).Range("L1 4").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 ----------------------------------------------------------------------------- "Tom Ogilvy" wrote: 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 |
Popping up message box with "Make Sure the Specified folder Ex
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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
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" wrote: 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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
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 "Rajesh T S" ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
Thanks for information and all the support NICK and TOM .
Thanks for all the help "NickHK" wrote: 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 "Rajesh T S" ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
For the record, what was the cause of the error ?
NickHK "Rajesh T S" wrote in message ... Thanks for information and all the support NICK and TOM . Thanks for all the help "NickHK" wrote: 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 "Rajesh T S" ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
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" wrote: For the record, what was the cause of the error ? NickHK "Rajesh T S" wrote in message ... Thanks for information and all the support NICK and TOM . Thanks for all the help "NickHK" wrote: 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 "Rajesh T S" ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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 -------------- |
Popping up message box with "Make Sure the Specified folder Ex
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 "Rajesh T S" wrote in message ... 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" wrote: For the record, what was the cause of the error ? NickHK "Rajesh T S" wrote in message ... Thanks for information and all the support NICK and TOM . Thanks for all the help "NickHK" wrote: 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 ---------------------- |
Popping up message box with "Make Sure the Specified folder Ex
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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: For the record, what was the cause of the error ? NickHK "Rajesh T S" wrote in message ... Thanks for information and all the support NICK and TOM . Thanks for all the help "NickHK" wrote: 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 ---------------------- |
Popping up message box with "Make Sure the Specified folder Ex
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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: For the record, what was the cause of the error ? NickHK "Rajesh T S" wrote in message ... Thanks for information and all the support NICK and TOM . Thanks for all the help "NickHK" wrote: 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 ---------------------- |
Popping up message box with "Make Sure the Specified folder Ex
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... "NickHK" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: For the record, what was the cause of the error ? NickHK "Rajesh T S" wrote in message ... Thanks for information and all the support NICK and TOM . Thanks for all the help "NickHK" wrote: 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 ---------------------- |
Popping up message box with "Make Sure the Specified folder Ex
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 "Rajesh T S" wrote in message ... 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... "NickHK" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: 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 "Rajesh T S" wrote in message ... 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" wrote: For the record, what was the cause of the error ? NickHK "Rajesh T S" wrote in message ... Thanks for information and all the support NICK and TOM . Thanks for all the help "NickHK" wrote: 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 ---------------------- |
| All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com