Code:
Sub ReadDataFromAllWorkbooksInFolder()Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, I As Integer
Dim a, b, C, d, e, f, g, h, j, k, l, m, n As String
'Obj As OLEObject
FolderName = "C:\PMCare Automation Report\Historical\PC"
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 11
Worksheets("Personal Claim").Visible = xlSheetVisible
Sheets("Personal Claim").Select
For I = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "E61")
'edit
a = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B60")
b = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B68")
C = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "G69")
If a = b Then
d = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "C70")
'Exit For
Else
d = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "C100")
End If
e = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B72")
f = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B75")
g = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "D74")
ActiveSheet.Unprotect
Cells(r, 3).Formula = cValue
Cells(r, 4).Formula = a
Cells(r, 5).Formula = b
Cells(r, 6).Formula = C
Cells(r, 7).Formula = d
Cells(r, 8).Formula = e
Cells(r, 9).Formula = f
Cells(r, 10).Formula = g
Cells(r, 11).Formula = "=HYPERLINK(""" & FolderName & "\" & wbList(I) & """, """ & wbList(I) & """)"
' Cells(r, 12).Formula = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
' DisplayAsIcon:=False, Left:=1584.75, Top:=1400.25, Width:=14.25, _
' Height:=10.5).Select
' Selection.ShapeRange.IncrementLeft 12#
' Selection.ShapeRange.IncrementTop -1.5
ActiveSheet.Protect
Next I
End Sub
Bookmarks