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?

ArviLaanemets · #2 10-13-2023, 01:02 AM

The crashing was caused by exessive 'Exit For' in my code. I divided my script into separate blocks to lessen the amount of nestings - so now I have better overview of them!

Also I now open the every source file I read the data from in a separate instance, and close it after the data are read and processed. Like:

Code:

...
Dim oInst As Object
...
For strSourceNameRow = 1 To NumberOfSourceFiles
        strSourceFile = <full path for source file to be opened>
        Set oInst = GetObject(strSourceFile)
...
        GoTo ExitSourceNameRow
...
ExitSourceNameRow:
        ' Activate the converter file, and Close the source file
        Workbooks(strSourceFileName).Activate
        Workbooks(strSourceFileName).Close savechanges:=False
        oInst.Close
        Set oInst = Nothing
        Workbooks(strThisFile).Activate
...

AllekieF · #3 10-18-2023, 03:53 AM

Why are you using VBA for this? This will be much, much quicker and easier with Power Query (get & transform). There are a lot of videos on YouTube to give you a jumpstart with it if you are unfamiliar with it.

ArviLaanemets · #4 10-18-2023, 04:57 AM

Quote:

Originally Posted by AllekieF

Why are you using VBA for this? This will be much, much quicker and easier with Power Query (get & transform)

The sheet in workbooks the info is read from doesn't have properly designed table (neither standard or defined one). Instead there are regions on sheet, which can be handled as different tables, but not designed as ones (some regions have data, but not headers, some have header row, but not all headers, placement of regions and the number of data rows in them can vary, there may be empty rows in regions, the number of columns and their placement in region may differ, etc. The script opens the file, analyzes it to determine, can it read wanted data from there or not (essentially by searching for certain strings in some single row, which are defined as column headers), and when it can, then reads this data. Also comments for files or data rows are created, when there was a reason the data couldn't be read, or when some info was missing.

As a result, a table with info from all ~1000 files in folder (from 1 to up over 100 rows per file) is created, plus another one where all problems with files, or with data read from them, are listed.

As you can see, no way this can be done using a query! And the structure of those files is not controlled by users of my app - they are mailed from 3rd party.

Btw. Currently I'm designing the app, so that the info will be updated only for new files or ones edited after previous run. So after inital run, next updates will be much faster.

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