The only times I use "Dir" are already shown in the original post. But below is the full code inside the loop....
Do While MyFile <> ""
Debug.Print MyFile
Debug.Print (InStr(MyFile, "backup"))
'' If the filename doesnt include the word "Backup"
If ((InStr(MyFile, "backup") = 0) And (InStr(MyFile, "Backup") = 0)) Then
''' 1. Add the new formulas and template into the DB
Worksheets("ConformanceDBTemplate").Activate
ActiveSheet.Range(conformanceDBTemplateRange).Select
Selection.Copy
Worksheets("CONFORMANCE DB").Select
Range("A" & newRecordRow).Select
ActiveSheet.Paste
''' 2. Open each workbook
'Set wb = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)
Set wb = Workbooks.Open(MyFolder & "\" & MyFile, True, True)
''' 3. Copy the reconciliation data from the Block Reconciliatoin file we are currently looking at
'''' First - Test to see if the Block Workbook has an X4 Reconciliation worksheet, if not, add it. '''
doesR2SheetExist = False
For Each ws In wb.Worksheets
If ws.Name = "X4 Reconciliation" Then
doesR2SheetExist = True
End If
Next ws
wb.Activate
If doesR2SheetExist = True Then
wb.Worksheets("X4 Reconciliation").Select
Range(reconciliationDataRange).Select
Selection.Copy
Else
Call ADD_X4_SHEET
wb.Worksheets("X4 Reconciliation").Select
Range(reconciliationDataRange).Select
Selection.Copy
End If
''' 4. Paste the data over to the CONFORMANCE DB worksheet
Workbooks(confDBName).Activate
Worksheets("CONFORMANCE DB").Select
Range(x4ValuesBeginColumnLetter & newRecordRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
''' 5. Input the date for the new record
Range("A" & newRecordRow).Value = newDate
Debug.Print newDate
''' 6. Input the Fret Name, Ver Name & Bench for the new record
Range("D" & newRecordRow).Value = wb.Worksheets("Presentation").Range("C2").Value ' Fret Name
Range("E" & newRecordRow).Value = wb.Worksheets("Grade Blocks").Range("B2").Value ' Ver Name
Range("F" & newRecordRow).Value = wb.Worksheets("Grade Blocks").Range("B3").Value ' Bench
''' 7. Close the workbook without saving
' If we want to save, use this code: wb.Save
Application.CutCopyMode = False 'Clears the clipboard first
wb.Close (False)
''' 8. Update the newRecordRow
newRecordRow = newRecordRow + numberOfRowsPerRecord
Else
backup_count = backup_count + 1
End If
''' 9. On to the next reconciliation workbook file
MyFile = Dir
fileCount = fileCount + 1
Loop
Bookmarks