Sub ReadDataFromAllWorkbooksInFolder1()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

FolderName = "C:\cube\GLForm"

' 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 = 9

'perlu edit tuk letak template

'Workbooks.Add

Sheets("Guarantee Letter").Select

For I = 1 To wbCount

r = r + 1

cValue = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F57")

'edit

a = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F58")

b = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F59")

c = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F60")

d = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F62")

e = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F63")

F = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F64")

g = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F65")

h = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "F66")

j = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "B990")

k = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "B991")

l = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "C990")

m = GetInfoFromClosedFile(FolderName, wbList(I), "GLForm", "C991")

'Cells(r, 2).Formula = wbList(i)

Cells(r, 3).Formula = cValue

'edit

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 = h

Cells(r, 12).Formula = j

Cells(r, 13).Formula = k

Cells(r, 14).Formula = l

Cells(r, 15).Formula = m

'Cells(i, "W").Value = cb15

'Cells(i, "X").Value = others

'Cells(i, "Y").Value = resit

Next I

End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _

wbName As String, wsName As String, cellRef As String) As Variant

Dim arg As String

GetInfoFromClosedFile = ""

If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"

If Dir(wbPath & "\" & wbName) = "" Then Exit Function

arg = "'" & wbPath & "[" & wbName & "]" & _

wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

On Error Resume Next

GetInfoFromClosedFile = ExecuteExcel4Macro(arg)

End Function