performance – The best and most efficient way to browse files using Excel VBA

I have a procedure to browse all the files of folders and subfolders from a folder that the user can select. The user can select both source and target folders. I use Excel VBA for this. Spreadsheets contain all or some of the file names to look for.

It works like this. I have function GetFiles which returns a string (including the path) separated by a pipeline (|). Then, I browse all the cells in column A containing the file name (or part of it) to search for. the GetFiles browses all the folders and subfolders of the selected source path. It takes longer if a high level of the source folder is selected.

The main function looks like this:

Under MoveFilesToFolder ()

Dim filePath As String: filePath = ""
Dim moveToPath As String: moveToPath = ""
Dim filename as a string
Dim fileNameFront As String
Dim fileNameRear As String
Dim currentFileName As String
Dim cell As Range
Dim fileCopied As Boolean: fileCopied = False
Dim i as an integer
Sun J also long
Dim StartTime As Double
Dim SecondsElapsed As Double
Dim result As String
Dim ws as sheet sheet
Dim frm As ufImageSearcher

ExactMatch = True
OverwriteExistingFile = False

Application.DisplayAlerts = False
Application.ScreenUpdating = False

On Error GoTo ErrorHandling

If (wsExists ("Images")) then

fileNameString = ""

& # 39; filePath = InputBox ("File Path, Close with Backslash ()", "Source Folder", ActiveWorkbook.Path)
& # 39; moveToPath = InputBox ("File copy path in! Close with backslash ()", "Target folder", ActiveWorkbook.Path & " copy ")

filePath = GetFolderPath ("Bron directory")
If (IsStringEmpty (filePath)) then
Output under
End if
moveToPath = GetFolderPath ("Doel directory")
If (IsStringEmpty (moveToPath)), then
Output under
End if

If not (IsStringEmpty (filePath) or IsStringEmpty (moveToPath)), then

If ((FolderExists (filePath)) And _
(FolderExists (moveToPath))) And (filePath <> moveToPath) Then

If Right (moveToPath, 1) <> "" Then
moveToPath = moveToPath & ""
End if

If (Dir (moveToPath & "*. *") <> ""), Then
result = MsgBox (moveToPath & "contains files! Choose an empty folder!" & _
vbCrLf & vbCrLf & "Go to the folder:" & moveToPath & "?", vbYesNo + vbQuestion, "Result!")
If (result = vbYes) Then
OpenFolderInExplorer (moveToPath)
End if
Output under
End if

wsActivate ("Images")
Set frm = New ufImageSearcher

With frm
.lblSource.Caption = file_path
.lblTarget.Caption = moveToPath

If .Tag <> "Canceled" Then
ExactMatch = .cbxExactMatch.Value
OverwriteExistingFile = .cbxOverwrite.Value
Output under
End if
Finish by

Start time = Timer

& # 39; Retrieves all files, including the path, separated by a pipeline.
GetFiles (filePath)

If not (IsStringEmpty (fileNameString)), then
Dim imgArray As Variant: imgArray = Split (fileNameString, "|")
& # 39; Column A contains all the strings used to compare the files found from the GetFiles function
For each cell in ActiveSheet.Range ("A1: A" & Range ("A1"). End (xlDown) .row)
fileCopied = False
filename = Mid (cell.Value, lastpositionOfChar (cell.Value, "/") + 1, Len (cell.Value))

Application.StatusBar = "(File Nbr:" & CStr (UBound (imgArray)) & ")"

If not (IsStringEmpty (filename)), then
For i = LBound (imgArray) to UBound (imgArray)
If not (IsStringEmpty (CStr (imgArray (i)))), then
If ExactMatch Then
If (GetFileName (imgArray (i)) = file name), then
If DoesFileExist (moveToPath & GetFileName (imgArray (i))) and do not overwrite the existing file
FileCopy imgArray (i), moveToPath & GetFileName (imgArray (i)) & "-" & Format (now, "yyyymmddhhmmss")
FileCopy imgArray (i), moveToPath & GetFileName (imgArray (i))
End if
fileCopied = True

If fileCopied then
ActiveSheet.Range ("B" & cell.row) .Value = imgArray (i)

For J = 2 to 15
Dim newFileName As String
newFileName = CreateFileName (CStr (imgArray (i)), LeadingZeroString (J))
If not (IsStringEmpty (newFileName)), then
If (DoesFileExist (newFileName)), then
If not (IsFileOpen (newFileName)), then
FileCopy newFileName, moveToPath & Right (newFileName, Len (newFileName) - lastpositionOfChar (newFileName, "") + 1)
ActiveSheet.Range (GetColLetter (J + 1) & cell.row) .Value = newFileName
ActiveSheet.Range (GetColLetter (J + 1) & cell.row) .Font.Color = RGB (0, 102, 0)
End if
ActiveSheet.Range (GetColLetter (J + 1) & cell.row) .Value = "(Niet aanwezig)" & Right (newFileName, Len (newFileName) - lastpositionOfChar (newFileName, "") + 1)
ActiveSheet.Range (GetColLetter (J + 1) & cell.row) .Font.Color = RGB (255, 153, 51)
End if
End if
Next J
End if
End if
If (InStr (1, GetFileName (imgArray (i)), filename, vbTextCompare)> 0), and then
If not (IsFileOpen (CStr (imgArray (i)))), then
If DoesFileExist (moveToPath & GetFileName (imgArray (i))) and do not overwrite the existing file
FileCopy imgArray (i), moveToPath & GetFileName (imgArray (i)) & "-" & Format (now, "yyyymmddhhmmss")
FileCopy imgArray (i), moveToPath & GetFileName (imgArray (i))
End if
fileCopied = True

& # 39; Find the first empty columnid.

lCol = Cells (cell.row, Columns.Count) .End (xlToLeft) .Column
ActiveSheet.Cells (cell.row, lCol + 1) .Value = imgArray (i)
End if
End if

End if
End if
Then I
If not copied then
ActiveSheet.Range ("B" & cell.row) .Value = "** NOT FOUND **"
ActiveSheet.Range ("B" & cell.row) .Font.Color = RGB (250, 0, 0)
End if
End if
End if

Sheets ("Images"). Columns ("B: Z"). Automatic adjustment
SecondsElapsed = Timer - Start Time

Application.DisplayAlerts = True
Application.ScreenUpdating = True

result = MsgBox ("Date Exported in:" & moveToPath & vbCrLf & "This was done in:" & Format (SecondsElapsed / 86400, "hh: mm: ss") & "seconds." & _
vbCrLf & vbCrLf & "Go to the folder:" & moveToPath & "?", vbYesNo + vbQuestion, "Result!"
If (result = vbYes) Then
OpenFolderInExplorer (moveToPath)
End if

If not (FolderExists (filePath)) then
MsgBox (filePath & ": the path is niet gevonden!")
End if
If not (FolderExists (moveToPath)), then
MsgBox (moveToPath & ": Path is niet gevonden!")
End if
End if
MsgBox ("No source and / or target selected" & vbCrLf & _
"Source:" & filePath & vbCrLf & _
"Target:" & moveToPath)
End if
MsgBox ("This procedure expects a worksheet" Images & # 39; "& vbCrLf & _
"and the name or part of the name of the image to be found in column A")
End if
If (IsObject (ws)) Then
Set ws = Nothing
End if

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Output under
Error handling:
MsgBox ("An error has occurred! (" & Err.Description & ")")
End Sub

The GetFiles function looks like:

Sub GetFiles (ByVal path as a string)

On Error GoTo ErrorHandling
Dim fso As Object: Define fso = CreateObject ("Scripting.FileSystemObject")
Dim As Object folder: Set folder = fso.GetFolder (path)

Dim subfolder as object
Dim file as object

For each subfolder in folder.SubFolders
GetFiles (subfolder.path)
Subfolder next

For each file in folder.Files
fileNameString = fileNameString & file.path & "|"
Next file

Set fso = Nothing
Define the folder = nothing
Set the subfolder = Nothing
Set file = Nothing

Output under

Error handling:
MsgBox ("An error has occurred! (" & Err.Description & ")")
End Sub

Everything works, but the execution can take a long time, especially when the selected source folder contains a lot of folders and subfolders.

To give you an idea, it takes 13 minutes to compare 100 rows of column A with 10 000 files found. The average loop 100 x 10,000 = 1 million times.

I have two questions:

  1. Is there a more efficient way to do this with Excel VBA?
  2. Is the DoEvents function used correctly?

Windows App – Efficient Diary Pro 5.60 Build 552 Multilingual Portable | NulledTeam UnderGround

Effective Journal Pro 5.60 Build 552 Multilingual Notebook | 20.61 MB

Efficient Diary is a powerful, elegant, easy to use and powerful electronic journal software. With its unique and powerful Flash full-text search technique, you can simply enter a word in the journal to quickly find the corresponding entries! The product has a strong editing function similar to that of Microsoft Word.

You can insert various elements such as tables, images, emotions, URLs or even attachments. You can set the color and background image of each log entry separately so that your log is rich and colorful. The login password is encrypted by the irreversible algorithm of SHA; In addition, the contents of the file itself are encrypted so that your private information is fully protected. With the exception of managing log entries by date, you can also manage it by hierarchical grouping.
There is a recycle bin in the software, so you do not have to worry about deleting a log entry by mistake. It also provides various unique features such as you can specify a weather icon and an emotion icon for each log entry, you can specify the importance of log entries, copy / paste log entries, etc. Time passes, while our perceptions and experiences can be recorded along the way. Every day, spend time with Efficient Diary – examine the feelings of today, remember the memories of the past and expect the happiness of the future. A happy life can not be without an effective newspaper!
Several interface styles
8 interface styles are provided to meet your individual needs! They have different color tones like blue, green or metallic and have standard Vista and XP standard window appearances separately.
Easy to search
Efficient Diary has powerful search features that allow you to quickly find your information.
Powerful document editor
Efficient Diary has integrated a powerful document editor similar to MS-Word, which can be used to write your journal. You can insert pictures, emotions, pictures, etc. to make your newspaper more colorful and alive.
Security safeguards of information
There is a trash in this software, you do not have to worry about deleting some important information by mistake. Backup and restore functions can help you easily back up and migrate your data.
Windows 10, Windows 8 / 8.1, Windows 7



Data structures – What is the most efficient algorithm to evenly distribute n points on a 2-dimensional array so that they are as separate as possible?

Try a physical network.

Each island is a point that pushes all other points by one amount and is pushed back by a certain amount by the other points. Reduces repulsion as the effect of distance.

Add up the effects and move the points of a fraction (say one third) of the repulsion effect.

Repeat this procedure for a number of iterations or until none of the points move.

In the end, all the points will be roughly separated, roughly equidistant, but with enough variation to seem somewhat random.

To give it a more varied appearance, assign slightly different repulsion forces to the sides and points. Some islands will be slightly closer, others slightly further away. You just have to vary the strength of some units, say that the average is 10 units, + -2 units.

Take a look at the blog of Amit. He has published many articles on interesting topics like this one.

Efficient Crowdfunding with Smart Contracts – Cryptocencies Corner

Smart contracts are digital versions of agreements. They are designed to run automatically when the coded conditions are met. With regard to crowdfunding, a smart contract can be extremely beneficial. This digital contract eliminates third parties and can determine whether funds will be received by a campaign or not. When the conditions promised by a token issuer are met, the contract acts accordingly. Ethereum smart contracts can create more efficient and secure crowdfunding. Looking for a smart contract development? Blockchain App Factory can help you with the process in no time.

Logic – Why does not the $ mu $ unbound operator preserve efficient computability?

Let $ f $ to be a partial function of $ mathbb {N} ^ {p + 1} $ at $ mathbb {N} $. Partial function $ (x_1, …, x_p) mapsto mu y[f(x_1,…,x_p,y)=0]$ is defined as follows:
If there is at least one integer $ z $ such as $ f (x_1, dots, x_p, z) = $ and if for each $ z <$ z, $ f (x_1, dots, x_p, z) $ is defined, then $ mu y[f(x_1,dots, x_p,y)=0]$ is at least equal $ z $. On the other hand, $ mu y[f(x_1,dots, x_p,y)=0]$ is not defined.

I do not understand why this unlimited $ mu $ The operator preserves the efficiency of calculation, in my manual and in a note that I found online, it is mentioned as a trivial fact.

I appreciate any help!

The most efficient way to convert lists of variable length lists into np.array into python?

What is the most efficient way to convert lists of variable length lists into np.array (three-dimensional matrix or n-dim matrix in general) into python?

Which windows are the most energy efficient and the least expensive to install in a home?

Which windows are the most energy efficient and the least expensive to install in a home?

oc.optimization and control – efficient optimization (constraint) of polynomials of sum of squares

Consider the problem
min p (x) text {subject to} g_j (x) ge 0
p, g_j in text {SOS},

that is to say. $ p, g_j $ ($ j = 1, ldots, $ m) are sum of squares Polynomials (SOS). Can this problem be solved effectively?

The paper [1] shows that the unconstrained minimization of SOS polynomials can be reduced to a convex program. Surprisingly, the document reviews the general constraint polynomial optimization (non-SOS) to derive the Lasserre hierarchy, but never explicitly discusses the particular case $ (*) $ above.

[1] Lasserre, Jean B., Global optimization with polynomials and the problem of momentsSIAM J. Optim. 11, No. 3, 796-817 (2001). ZBL1010.90061.

Help me make my database efficient

I have a very large database of meteorological parameters for almost every city in the world. The database contains millions of lines.

I have to add weather information from a few thousand cities every day. In fact, I have to add the new data to a few thousand cities, preserving the previous data. In other words, I should not update the meteorological information of these cities. I have to add new information every day.

The problem is that I have a table containing millions of lines for millions of cities and that when I want to add new information to only a few cities, the size of my database increases dramatically.

In summary, I do not want to add millions of unnecessary bytes to the size of my database every time I want to add new information to just a few cities.

How can I do it? I need an effective way to add new data to my database every day. Thank you.

algorithms – Tips for creating an efficient graph data structure for a word scale research program?

I have to write a program that, with two strings, will find the smallest scale of words between them or explain that a scale of words between them is not possible. It must use a graphical data structure.

I have a text file of over 200,000 words of different lengths that could be used to form scales of words. I am new to graphical data structures and I do not know how to create an efficient data structure for such a large amount of words of different lengths.

What qualities would I like in the graph data structure I create?