CutAndPaste
Registered User.
- Local time
- Today, 01:33
- Joined
- Jul 16, 2001
- Messages
- 60
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
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