#1
|
|||
|
|||
The best way to avoid opened files to be activated
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 ... 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 Has anyone an idea how to make this to work too? |
#2
|
|||
|
|||
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 ... |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
Quote:
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 |