Check Excel Cell Values in all files in folder (1 Viewer)

Hi,
I'm trying to loop through all of the Excel Workbooks in a folder and for each, check the value of a cell. If this value equals X then I want to move the file to folder A, if it does not then I want to move it to folder B.

I've got parts of the code to work, I can do both parts but lack the expertise to string it all together to do both.

'===============
'this routine works and cycles through all of the Files in a Folder.

Private Sub FilterExcel()
Dim ImportFileName As String
Dim InputFolderName As String
Dim SuspectFolderName As String 'used later on

Dim intButSelected As Integer, intButType As Integer
Dim strMsgPrompt As String, strMsgTitle As String

strMsgPrompt = "Are you sure you want to Filter the files"
strMsgTitle = "Filter Files?"

intButType = vbYesNo + vbQuestion + vbDefaultButton2
If MsgBox(strMsgPrompt, intButType, strMsgTitle) = vbYes Then


InputFolderName = Forms![frmMenuMain]![txtImportFolder]'location of Folder
ImportFileName = Dir(InputFolderName & "\*.xls") ' to just do xls files

While ImportFileName > ""

Me.txtProcessingFile = ImportFileName 'unbound field on form to display processing of each file
MsgBox "Checking " & ImportFileName & "..." 'remove this later -just to show that each file being examined during testing

'Code in here to check each workbook's cells?

ImportFileName = Dir

Wend


' end of are you sure?
Else
Exit Sub
End If
End Sub
'==================
'This routine checks the Cell values in a named sheet ok.
'but will need to be made able to accept the "live" filename in the routine above

Private Sub cmdCellCheck_Click()

Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")

With objXL.Application
.Visible = False '
.workbooks.Open "c:\Book2.xls" 'but want to be able to put variable filename in here.
End With

If objXL.ActiveWorkbook.Worksheets("Sheet1").Range("a1") = "OK" Then
MsgBox "OK found - File will be moved to Import Folder", vbOKOnly, "Data Ok"
'code in here to move file to desired folder
Else
MsgBox "Not OK found - file will be moved to Manual Inspection Folder", vbOKOnly, "Data Suspect"
'code in here to move file to desired inspection folder
End If

'trying to ensure Excel is closed properly - not always working ,why?
ObjXL.ActiveWorkbook.Close 'Closes Excel WorkSheet
ObjXL.Quit
Set objXL = Nothing


End Sub
'=====================

So, I can loop through the Workbooks in a folder and I can check the contents of a cell in a named sheet. How do I combine the code to accomplish both?

Any pointer greatfully received!
tx
Simon

Could be horribly wrong but think that if you change

Private Sub cmdCellCheck_Click()

to

Sub cmdCellCheck_Click(ImportFolderName, ImportFileName)

Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")

With objXL.Application
.Visible = False '
.workbooks.Open ImportFolderName & "\" & ImportFileName 'but want to be able to put variable filename in here.
End With

If objXL.ActiveWorkbook.Worksheets("Sheet1").Range("a1") = "OK" Then
MsgBox "OK found - File will be moved to Import Folder", vbOKOnly, "Data Ok"
'code in here to move file to desired folder
Else
MsgBox "Not OK found - file will be moved to Manual Inspection Folder", vbOKOnly, "Data Suspect"
'code in here to move file to desired inspection folder
End If

'trying to ensure Excel is closed properly - not always working ,why?
ObjXL.ActiveWorkbook.Close 'Closes Excel WorkSheet
ObjXL.Quit
Set objXL = Nothing


End Sub

Call this procedure from your main bit by the usual means ie:

While ImportFileName > ""

Me.txtProcessingFile = ImportFileName 'unbound field on form to display processing of each file
MsgBox "Checking " & ImportFileName & "..." 'remove this later -just to show that each file being examined during testing

'Code in here to check each workbook's cells?

cmdCellCheck_Click(ImportFolderName, ImportFileName)

Think that to move the file look at Move method in help: ought to be:

Myfile.move new_destination

HTH

Nearly there...

Harry,

Not horribly wrong, frighteningly right! Great !

Using your code changes, I've got it to cycle through all of the Files in the folder. However it seems to falls down on the value checking - it says all of the files are ok, and in the attached demo 3 are fine and 3 are not.

When the original code is used (pointing to a single named file) it spots the contents of the cell correctly and the correct message comes up.

I also had a thought about when should Excel be closed? as I want to move the file to another folder and I'm assuming it won't be able to be moved whilst still open.


Any further flashes of inspiration?

Many thanks,

Simon

As an idea, where you carry out the check on the cell, try assigning the value of the cell to a variable and a) return that in a message box so that you can see what the code views and then pass that variable through the check. ie:

Instead of:

If objXL.ActiveWorkbook.Worksheets("Sheet1").Range("a1") = "OK" Then
MsgBox "OK found - File will be moved to Import Folder", vbOKOnly, "Data Ok"
'code in here to move file to desired folder
Else
MsgBox "Not OK found - file will be moved to Manual Inspection Folder", vbOKOnly, "Data Suspect"
'code in here to move file to desired inspection folder
End If

TRY THIS:

MyVal = objXL.ActiveWorkbook.Worksheets("Sheet1").Range("A1")
Msgbox "This is my value: " & MyVal
If MyVal = "OK" then
Move
Else
Move
End If

At least this way you can see what is happening

HTH