Hi
I'm designing an Excel App, which must read from a lot of Excel files in certain folder some info into Excel table in this app. The number of files in folder is currently about 900, and there are new files added.
The list of files to be processed id read into VBA array, and then a for cycle opens file, process it, writes needed data into another array, then closes the source file and continues with next one. Like
Code:
...
lngSourceNameRow = 0
For Each oFile In oFolder.Files
If ... the condition for files to be read was True... Then
lngSourceNameRow = lngSourceNameRow + 1
ReDim Preserve arrSourceNames(2, lngSourceNameRow) As Variant
arrSourceNames(1, lngSourceNameRow) = "" & oFile.Name
arrSourceNames(2, lngSourceNameRow) = oFile.DateCreated
End If
Next oFile
...
strSourceFileName = arrSourceNames(1, lngSourceNameRow)
strSourceFile = strSF & strSourceFileName ' where strSF is the path for folder
' Opening the source file
Workbooks.Open Filename:=strSourceFile
...
If ... the check for file structure was passed ... Then
...
' There are several checks made. When those aren't passed, the file is ignored - i.e. the source file is closed, and the for cycle will continue with next file
Workbooks(strSourceFileName).Activate
ActiveWorkbook.Close savechanges:=False
Workbooks(strThisFile).Activate ' the app calling the script is activated
Exit For
...
' The info from source file was read into array. The source file is closed without saving, and the app calling the script is activated
Workbooks(strSourceFileName).Activate
ActiveWorkbook.Close savechanges:=False
Workbooks(strThisFile).Activate ' the app calling the script is activated
End If
Next lngSourceNameRow ' Opening next file in Soure Folder
...
When tested with small number of source files in test folder, the data was read in. But trying it with real folder, somewhere after 300 files something went wrong - the script crashed, and the App did show an "Empty" sheet - no pages, no menus, etc. I cold access only VBA (using Alt+F11). And in VBAProject window all those opened source files were listed, and remained there until the session was ended! Has anyone some idea what happened and what to do?
Another thing is, that opened source files flicking on screen are quite annoying, and probably time consuming too. I did try like this:
Code:
Application.ScreenUpdating = False
Workbooks.Open Filename:=FilePath, UpdateLinks:=True, ReadOnly:=True
ActiveWindow.Visible = False
ThisWorkbook.Activate
, but probably I messed something up with this. When I tried it, even with test folder with couple of files the App crashed (and again all what was left was VBA module!)
Has anyone an idea how to make this to work too?