Use of Everything from Excel VBA

[urijoh]

Hej there,

I am not really a programmer, but at least a passionate user of VBA in Excel and Solidworks. I'd love to integrate everything in some of my VBA code, but failed so far:
- register the everything64.dll with regsvr32 does return an error
- set a reference in VBA does not work
- using the declare function does not work
Has anybody ever used the Everything SDK using VBA?
Does somebody have an example (I could not make the VB sample working on VBA)?

[NotNull]

- register the everything64.dll with regsvr32 does return an error

everything64.dll does not need registration.
(can't help yiu with your other questions)

[void]

I only have the Visual Basic SDK example.
https://www.voidtools.com/Everything.SDK.Visual.Basic.Example.zip

I could not make the VB sample working on VBA

What happens?
Hope I can help..

[urijoh]

Trials_Everything.zip

(22 Bytes) Downloaded 1587 times

to me, it seems that it does nothing?!?
Find attached the Excel-File I used for my trials. I had to change some stuff in order to make it run in VBA (see header in this VBA module). It seems now to run, but it does always return a "0" on the GetNumResults request.

Would be really cool if you could find the solution for this

[void]

The SDK requires Everything is running in the background.

Is Everything running in the background?

Trials_Everything.zip looks empty?

[urijoh]

Trials_Everything.zip

(16.19 KiB) Downloaded 1740 times

Sorry for the missing content in the .zip - it should now contain the Excel file with the VBA Macro.
Everything is running in the background (always all day long...)

[void]

Thanks for the Trials VBA.

I've made the following changes:

Code: Select all

'Note: sample copied from https://www.voidtools.com/support/everything/sdk/visual_basic/
'Replaced for VBA usage
' - UINT32 with LONG
' - UINT64 with LONGLONG
' - INTPtr with LONGPtr
' - System.Text.StringBuilder with String
' - System.DateTime with String
' - filename.Capacity with filesize

Public Declare PtrSafe Function Everything_SetSearchW Lib "C:\SDK\Everything64.dll" (ByVal ins As LongPtr) As Long
Public Declare PtrSafe Function Everything_SetRequestFlags Lib "C:\SDK\Everything64.dll" (ByVal dwRequestFlags As Long) As Long
Public Declare PtrSafe Function Everything_QueryW Lib "C:\SDK\Everything64.dll" (ByVal bWait As Integer) As Integer
Public Declare PtrSafe Function Everything_GetNumResults Lib "C:\SDK\Everything64.dll" () As Long
Public Declare PtrSafe Function Everything_GetResultFileNameW Lib "C:\SDK\Everything64.dll" (ByVal index As Long) As LongPtr
Public Declare PtrSafe Function Everything_GetLastError Lib "C:\SDK\Everything64.dll" () As Long
Public Declare PtrSafe Function Everything_GetResultFullPathNameW Lib "C:\SDK\Everything64.dll" (ByVal index As Long, ByVal ins As LongPtr, ByVal size As Long) As Long
Public Declare PtrSafe Function Everything_GetResultSize Lib "C:\SDK\Everything64.dll" (ByVal index As Long, ByRef size As LongLong) As Integer         'size UInt64
Public Declare PtrSafe Function Everything_GetResultDateModified Lib "C:\SDK\Everything64.dll" (ByVal index As Long, ByRef ft As LongLong) As Integer   'ft UInt64

Public Const EVERYTHING_REQUEST_FILE_NAME = &H1
Public Const EVERYTHING_REQUEST_PATH = &H2
Public Const EVERYTHING_REQUEST_FULL_PATH_AND_FILE_NAME = &H4
Public Const EVERYTHING_REQUEST_EXTENSION = &H8
Public Const EVERYTHING_REQUEST_SIZE = &H10
Public Const EVERYTHING_REQUEST_DATE_CREATED = &H20
Public Const EVERYTHING_REQUEST_DATE_MODIFIED = &H40
Public Const EVERYTHING_REQUEST_DATE_ACCESSED = &H80
Public Const EVERYTHING_REQUEST_ATTRIBUTES = &H100
Public Const EVERYTHING_REQUEST_FILE_LIST_FILE_NAME = &H200
Public Const EVERYTHING_REQUEST_RUN_COUNT = &H400
Public Const EVERYTHING_REQUEST_DATE_RUN = &H800
Public Const EVERYTHING_REQUEST_DATE_RECENTLY_CHANGED = &H1000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_FILE_NAME = &H2000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_PATH = &H4000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_FULL_PATH_AND_FILE_NAME = &H8000

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Public Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Private Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32" (ByRef ft As LongLong, lpSystemTime As SYSTEMTIME) As Long
Private Declare PtrSafe Function SystemTimeToTzSpecificLocalTime Lib "kernel32" (ByVal tzi As LongPtr, lpst As SYSTEMTIME, lplt As SYSTEMTIME) As Long
Private Declare PtrSafe Function SystemTimeToVariantTime Lib "OLEAUT32.DLL" (lpSystemTime As SYSTEMTIME, vtime As Date) As Long

Sub SimpleTest()
    Dim EyText As String
    Dim test As Boolean
    EyText = "Everything"
    Everything_SetSearchW (StrPtr(EyText))
    Everything_SetRequestFlags (EVERYTHING_REQUEST_FILE_NAME Or EVERYTHING_REQUEST_PATH Or EVERYTHING_REQUEST_SIZE Or EVERYTHING_REQUEST_DATE_MODIFIED)
    test = Everything_QueryW(True)
    Debug.Print test

    Dim NumResults As Long
    Dim i As Long
    Dim filename As String
    Dim filenameLength As Long
    Dim filesize As Long
    Dim size As LongLong
    Dim ftdm As LongLong
    Dim stdm As SYSTEMTIME
    Dim ltdm As SYSTEMTIME
    Dim DateModified As Date

    filename = String(260, 0)

    NumResults = Everything_GetNumResults()
    Debug.Print NumResults

    If NumResults > 0 Then
        For i = 0 To NumResults - 1
            filenameLength= Everything_GetResultFullPathNameW(i, StrPtr(filename), 260)
            Debug.Print Left(filename, filenameLength)
            
            test = Everything_GetResultSize(i, size)
            Debug.Print size
            
            test = Everything_GetResultDateModified(i, ftdm)
            test = FileTimeToSystemTime(ftdm, stdm)
            test = SystemTimeToTzSpecificLocalTime(0, stdm, ltdm)
            test = SystemTimeToVariantTime(ltdm, DateModified)
            Debug.Print DateModified
        Next
    End If
End Sub

Notes:

Use LongPtr for strings.

[urijoh]

WOW, it does work, You are the best!!!

[Tony.Zhou]

Hi Void,

I applied your solution, and when I run it, it says "User-defined type not defined", would you please help on this?

Thank you,

[urijoh]

attached are the references I use in this Excel-VBA macro.

Refernces.gif (6.21 KiB) Viewed 27722 times

[Tony.Zhou]

Hi urijoh,

Thank you, but I already have all the references selected, it still does not work.


Thank you,

[NotNull]

No image ...

And your Everything64.dll is in "C:\SDK\Everything64.dll"?

[Tony.Zhou]

I put the Everything64.dll in a different path like "C:\Program Files\Everything\dll\Everything64.dll", and I changed it in the Public statement accordingly.
It does work in my personal computer, however, when I use the same program in my work computer, it shows the error, is it because of the comptuer setting environment?

Thank you

[Tony.Zhou]

One more problem is that, when I run the program in my personal computer, the first time it runs smoothly, however, when I run the second time, it breaks the Excel, can you help on this also?

Thank you,

[void]

To improve stability, please try:

Code: Select all

            ...
            Dim filenameLength As Long
    
            filenameLength= Everything_GetResultFullPathNameW(i, StrPtr(filename), 260)
            Debug.Print Left(filename, filenameLength)
            ...

I have updated my code above.

[void]

To run on Excel x86, please try the following:

Code: Select all

'Note: sample copied from https://www.voidtools.com/support/everything/sdk/visual_basic/
'Replaced for VBA usage
' - UINT32 with LONG
' - UINT64 with LARGE_INTEGER
' - INTPtr with LONGPtr
' - System.Text.StringBuilder with String
' - System.DateTime with String
' - filename.Capacity with filesize

Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

Public Declare PtrSafe Function Everything_SetSearchW Lib "C:\SDK\Everything32.dll" (ByVal ins As LongPtr) As Long
Public Declare PtrSafe Function Everything_SetRequestFlags Lib "C:\SDK\Everything32.dll" (ByVal dwRequestFlags As Long) As Long
Public Declare PtrSafe Function Everything_QueryW Lib "C:\SDK\Everything32.dll" (ByVal bWait As Integer) As Integer
Public Declare PtrSafe Function Everything_GetNumResults Lib "C:\SDK\Everything32.dll" () As Long
Public Declare PtrSafe Function Everything_GetResultFileNameW Lib "C:\SDK\Everything32.dll" (ByVal index As Long) As LongPtr
Public Declare PtrSafe Function Everything_GetLastError Lib "C:\SDK\Everything32.dll" () As Long
Public Declare PtrSafe Function Everything_GetResultFullPathNameW Lib "C:\SDK\Everything32.dll" (ByVal index As Long, ByVal ins As LongPtr, ByVal size As Long) As Long
Public Declare PtrSafe Function Everything_GetResultSize Lib "C:\SDK\Everything32.dll" (ByVal index As Long, ByRef size As LARGE_INTEGER) As Integer         'size UInt32
Public Declare PtrSafe Function Everything_GetResultDateModified Lib "C:\SDK\Everything32.dll" (ByVal index As Long, ByRef ft As LARGE_INTEGER) As Integer   'ft UInt64

Public Const EVERYTHING_REQUEST_FILE_NAME = &H1
Public Const EVERYTHING_REQUEST_PATH = &H2
Public Const EVERYTHING_REQUEST_FULL_PATH_AND_FILE_NAME = &H4
Public Const EVERYTHING_REQUEST_EXTENSION = &H8
Public Const EVERYTHING_REQUEST_SIZE = &H10
Public Const EVERYTHING_REQUEST_DATE_CREATED = &H20
Public Const EVERYTHING_REQUEST_DATE_MODIFIED = &H40
Public Const EVERYTHING_REQUEST_DATE_ACCESSED = &H80
Public Const EVERYTHING_REQUEST_ATTRIBUTES = &H100
Public Const EVERYTHING_REQUEST_FILE_LIST_FILE_NAME = &H200
Public Const EVERYTHING_REQUEST_RUN_COUNT = &H400
Public Const EVERYTHING_REQUEST_DATE_RUN = &H800
Public Const EVERYTHING_REQUEST_DATE_RECENTLY_CHANGED = &H1000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_FILE_NAME = &H2000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_PATH = &H4000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_FULL_PATH_AND_FILE_NAME = &H8000

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Public Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Private Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32" (ByRef ft As LARGE_INTEGER, lpSystemTime As SYSTEMTIME) As Long
Private Declare PtrSafe Function SystemTimeToTzSpecificLocalTime Lib "kernel32" (ByVal tzi As LongPtr, lpst As SYSTEMTIME, lplt As SYSTEMTIME) As Long
Private Declare PtrSafe Function SystemTimeToVariantTime Lib "OLEAUT32.DLL" (lpSystemTime As SYSTEMTIME, vtime As Date) As Long

Sub SimpleTest()
    Dim EyText As String
    Dim test As Boolean
    EyText = "Everything"
    Everything_SetSearchW (StrPtr(EyText))
    Everything_SetRequestFlags (EVERYTHING_REQUEST_FILE_NAME Or EVERYTHING_REQUEST_PATH Or EVERYTHING_REQUEST_SIZE Or EVERYTHING_REQUEST_DATE_MODIFIED)
    test = Everything_QueryW(True)
    Debug.Print test

    Dim NumResults As Long
    Dim i As Long
    Dim filename As String
    Dim filenameLength As Long
    Dim filesize As Long
    Dim size As LARGE_INTEGER
    Dim ftdm As LARGE_INTEGER
    Dim stdm As SYSTEMTIME
    Dim ltdm As SYSTEMTIME
    Dim DateModified As Date

    filename = String(260, 0)

    NumResults = Everything_GetNumResults()
    Debug.Print NumResults

    If NumResults > 0 Then
        For i = 0 To NumResults - 1
            filenameLength= Everything_GetResultFullPathNameW(i, StrPtr(filename), 260)
            Debug.Print Left(filename, filenameLength)
            
            test = Everything_GetResultSize(i, size)
            Debug.Print size.lowpart
            
            test = Everything_GetResultDateModified(i, ftdm)
            test = FileTimeToSystemTime(ftdm, stdm)
            test = SystemTimeToTzSpecificLocalTime(0, stdm, ltdm)
            test = SystemTimeToVariantTime(ltdm, DateModified)
            Debug.Print DateModified
        Next
    End If
End Sub

[jay-ar.villavicencio]

Hello everyone,
I am currently using Excel 2016 MSO 32-bit and i tried using the code below in order to do a simple test of searching files with everything though excel.
The problem is: the code just gives me back "false" and "0" as results.
I think it is not accesing Everything correctly. I have done the corrections needed for the code to work.
I have everything running in the background.
I even tried to put the dll as a reference in the VB window (tools --> reference --> browse) but excel says that it cant add the dll file.
What else can i do to make it work? I do not know anymore what I can try.
Please help! Thanks!!
Note: I am running this version of everything Version 1.5.0.1315a (x64)

'Note: sample copied from https://www.voidtools.com/support/every ... ual_basic/
'Replaced for VBA usage
' - UINT32 with LONG
' - UINT64 with LARGE_INTEGER
' - INTPtr with LONGPtr
' - System.Text.StringBuilder with String
' - System.DateTime with String
' - filename.Capacity with filesize

Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Public Declare PtrSafe Function Everything_SetSearchW Lib "C:\SDK\Everything32.dll" (ByVal ins As LongPtr) As Long
Public Declare PtrSafe Function Everything_SetRequestFlags Lib "C:\SDK\Everything32.dll" (ByVal dwRequestFlags As Long) As Long
Public Declare PtrSafe Function Everything_QueryW Lib "C:\SDK\Everything32.dll" (ByVal bWait As Integer) As Integer
Public Declare PtrSafe Function Everything_GetNumResults Lib "C:\SDK\Everything32.dll" () As Long
Public Declare PtrSafe Function Everything_GetResultFileNameW Lib "C:\SDK\Everything32.dll" (ByVal index As Long) As LongPtr
Public Declare PtrSafe Function Everything_GetLastError Lib "C:\SDK\Everything32.dll" () As Long
Public Declare PtrSafe Function Everything_GetResultFullPathNameW Lib "C:\SDK\Everything32.dll" (ByVal index As Long, ByVal ins As LongPtr, ByVal size As Long) As Long
Public Declare PtrSafe Function Everything_GetResultSize Lib "C:\SDK\Everything32.dll" (ByVal index As Long, ByRef size As LARGE_INTEGER) As Integer 'size UInt32
Public Declare PtrSafe Function Everything_GetResultDateModified Lib "C:\SDK\Everything32.dll" (ByVal index As Long, ByRef ft As LARGE_INTEGER) As Integer 'ft UInt64

Public Const EVERYTHING_REQUEST_FILE_NAME = &H1
Public Const EVERYTHING_REQUEST_PATH = &H2
Public Const EVERYTHING_REQUEST_FULL_PATH_AND_FILE_NAME = &H4
Public Const EVERYTHING_REQUEST_EXTENSION = &H8
Public Const EVERYTHING_REQUEST_SIZE = &H10
Public Const EVERYTHING_REQUEST_DATE_CREATED = &H20
Public Const EVERYTHING_REQUEST_DATE_MODIFIED = &H40
Public Const EVERYTHING_REQUEST_DATE_ACCESSED = &H80
Public Const EVERYTHING_REQUEST_ATTRIBUTES = &H100
Public Const EVERYTHING_REQUEST_FILE_LIST_FILE_NAME = &H200
Public Const EVERYTHING_REQUEST_RUN_COUNT = &H400
Public Const EVERYTHING_REQUEST_DATE_RUN = &H800
Public Const EVERYTHING_REQUEST_DATE_RECENTLY_CHANGED = &H1000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_FILE_NAME = &H2000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_PATH = &H4000
Public Const EVERYTHING_REQUEST_HIGHLIGHTED_FULL_PATH_AND_FILE_NAME = &H8000

Public Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Public Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32" (ByRef ft As LARGE_INTEGER, lpSystemTime As SYSTEMTIME) As Long
Private Declare PtrSafe Function SystemTimeToTzSpecificLocalTime Lib "kernel32" (ByVal tzi As LongPtr, lpst As SYSTEMTIME, lplt As SYSTEMTIME) As Long
Private Declare PtrSafe Function SystemTimeToVariantTime Lib "OLEAUT32.DLL" (lpSystemTime As SYSTEMTIME, vtime As Date) As Long

Sub SimpleTest()
Dim EyText As String
Dim test As Boolean
EyText = "Everything"
Everything_SetSearchW (StrPtr(EyText))
Everything_SetRequestFlags (EVERYTHING_REQUEST_FILE_NAME Or EVERYTHING_REQUEST_PATH Or EVERYTHING_REQUEST_SIZE Or EVERYTHING_REQUEST_DATE_MODIFIED)
test = Everything_QueryW(True)
Debug.Print test

Dim NumResults As Long
Dim i As Long
Dim filename As String
Dim filenameLength As Long
Dim filesize As Long
Dim size As LARGE_INTEGER
Dim ftdm As LARGE_INTEGER
Dim stdm As SYSTEMTIME
Dim ltdm As SYSTEMTIME
Dim DateModified As Date

filename = String(260, 0)

NumResults = Everything_GetNumResults()
Debug.Print NumResults

If NumResults > 0 Then
For i = 0 To NumResults - 1
filenameLength = Everything_GetResultFullPathNameW(i, StrPtr(filename), 260)
Debug.Print Left(filename, filenameLength)

test = Everything_GetResultSize(i, size)
Debug.Print size.lowpart

test = Everything_GetResultDateModified(i, ftdm)
test = FileTimeToSystemTime(ftdm, stdm)
test = SystemTimeToTzSpecificLocalTime(0, stdm, ltdm)
test = SystemTimeToVariantTime(ltdm, DateModified)
Debug.Print DateModified
Next
End If
End Sub

[void]

If you are not using Everything 1.4, please try disabling the Everything 1.5a instance:

• Copy and paste the following into your Everything search box:
  /alpha_instance=0
• Press ENTER in your Everything search box.
• If successful, alpha_instance=0 is shown in the status bar for a few seconds.

Restart Everything:

• From the File menu, click Exit.
• Restart Everything.

Please let me know if the issue persists.

[jay-ar.villavicencio]

Yes! It is working.
However the copying and psting /alpaha_instance=0 into search box did not work. I manually changed it inside the .ini file which was located in the same folder where the exe is found.
It also would not overwrite the existing one...so i copied it into another folder, changed the value and then pasted it in the original folder.
I tried the code and like one of the users, I am also having trouble with the fact that the first time i try to run the code, it runs smoothly but when i try to run it again, it breaks excel. Has the issue been resolved? How was it resolved?
Thanks a lot again!!!!

[therube]

/alpaha_instance=0

Typo (I'm guessing). Must be

/alpha_instance=0

.

[MGadAllah]

If I may ask, what is the benefits of using EveryThing search with Microsoft Excel?

[jay-ar.villavicencio]

Yep it was a typo.
For the main benefit, at least for my type of use, is that it allows a very fast and reliable search in comparison to not using Everything.
For example, I want to verify if a list of certain files exist, I could use everything to scan a very large folder (containing a lot of subfolders) and then ask it to give me back the a list of each file's full path. With the full path and name of each file of the list, i can easily then make a hyperlink in excel to these files.
I am sure there are other lots of uses for everything in excel. You just have to be creative!

[AutoSoft]

If I may ask, what is the benefits of using EveryThing search with Microsoft Excel?

if you can search in vbs and vba, of course, it is very convenient, each has its own needs.

[Tank Ersley]

Hi. I am trying to modify the information here to work with a RegEx search. I've done the following so far.

Added this public function declaration.

Code: Select all

Public Declare PtrSafe Function Everything_SetRegex Lib "C:\SDK\dll\Everything32.dll" (void) As Integer

Added this statement to the sub.

Code: Select all

'Reference to cell with RegEx string
EyText = ActiveSheet.Range("A:A").Find("Everything RegEx").Offset(0, 1).Value
    
'### ADDED ###
Everything_SetRegex (True)

Everything_SetSearchW (StrPtr(EyText))

Since I only want the filenames, I removed the statements regarding ResultSize and DateModified.

This gives the number of results and the filenames that I am looking for.

However, I have these issues.

1. Did I handle the SetRegex property correctly? Setting it to True or False does not seem to have any effect.
2. How do I set a filter? I tried this (viewtopic.php?p=53608&hilit=sdk+filter#p53608) but I get no results.
3. I'm using 32-bit Excel 2019 and the X86 version of the code. As others have mentioned, if I run it a couple of times, Excel freezes and stops responding. I have to force quit and restart it.

[void]

Please declare Everything_SetRegex as:

Code: Select all

Public Declare PtrSafe Function Everything_SetRegex Lib "C:\SDK\Everything32.dll" (ByVal bEnable As Integer) As Integer

How do I set a filter?

Typically, the SDK does not have access to filters.
You'll need to make your own filters and include them in your search.

For example:

ext:jpg;png regex:^foo
folder: abc 123

Everything 1.5 will let you reference filters with filter: or filter macro.

Excel freezes and stops responding

Please check the IPC requests in Everything:

• Copy and paste the following into your Everything search box:
  /debug
• Press ENTER in your Everything search box.
  ---this will show a debug console
  ---IPC requests are shown in Cyan color.

To close the debug console:

• Copy and paste the following into your Everything search box:
  /debug
• Press ENTER in your Everything search box.

Sending IPC requests can take a long time for very large lists.

Please try reducing the maximum number of results.
Before calling Everything_QueryW, please call Everything_SetMax(100)

Code: Select all

Public Declare PtrSafe Function Everything_SetMax Lib "C:\SDK\Everything32.dll" (ByVal dwMax As Long) As Long

Does the freezing persist?