The best way to avoid opened files to be activated

ArviLaanemets · #1 10-06-2023, 06:36 AM

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?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can employer see private files opened in Office 365	thosdmg	Office	0	02-27-2023 01:13 AM
list of Opened excel files	SDondeti	Word VBA	10	05-10-2018 03:52 PM
vba opened word files lock up sometimes	littlepeaks	Word VBA	1	01-28-2016 01:16 PM
XLM Files are not being opened in newer version of Microsoft Office 2013	KetulShah	Excel	0	03-18-2014 08:23 PM
How to download prev. opened Gmail files.	libertyfelix	Outlook	1	03-25-2012 09:10 PM