0

As the title states, this macro searches for and opens the workbook in the directory if it contains the value I'm looking for. The value I'm looking for is in ActiveSheet.Range("I9"). It works, but it takes a looonnnng time. It's slow because it's opening every file in the directory while it searches for the value. Is there an easy way to avoid that? I'm thinking that entering partial file name in the cell with the value I'm searching for may speed it up but I would much rather avoid using file names (100's of workbooks in the directory with similar names). Thanks! 

sub search()

Dim directory As String
Dim fileName As String
Dim sheet As Worksheet

' criteria variables
Dim crit1 As Variant

' Booleans to store results of individual tests
Dim b1 As Boolean

' Adjust these to reference your combo boxes values - I just used a1,2,3
crit1 = ActiveSheet.Range("I9").Value

' set directory variable to directory of subject files - adjust path to suit
directory = "C:\Users\jesseh\Desktop\DESKTOPFOLDER\"
Application.ScreenUpdating = False
fileName = Dir(directory & "*.xlsm")
Do While fileName <> ""

' initialize tests as false
b1 = False
Workbooks.Open (directory & fileName)

' Change the sheet and range values to match your inspection cells
' Cell with value of first combo box

If Sheets("MEMO").Range("B8").Value = crit1 Then b1 = True
' Cell with value of second combo box

'If Sheets("Sheet1").Range("B1").Value = crit2 Then b2 = True
' Cell with value of third combo box

'If Sheets("Sheet1").Range("C1").Value = crit3 Then b3 = True
' If all 3 don't match, close the workbook

If Not (b1) Then Workbooks(fileName).Close SaveChanges:=False

' get the next filename and inspect its contents

fileName = Dir()
Loop
Application.ScreenUpdating = False


End Sub
JHunter123
  • 1
  • *It's slow because it's opening every file in the directory while it searches for the value. Is there an easy way to avoid that?* Yes. You need to avoid *opening* the workbook. You can use ADO or [ExecuteExcel4Macro](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-executeexcel4macro-method-excel) method to query data from a closed workbook. Both methods should be much faster than opening all of the workbooks. If still not sufficient, then you may need to use some logic to reduce the number of files you need to query (regex the filename/etc). – David Zemens Feb 27 '18 at 15:18
  • https://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook – David Zemens Feb 27 '18 at 15:19

0 Answers0