I have the following code to read data from closed worksheets. I want to select a range of cells then count if certain values are present, code is a as follows: I want to put a formula in the "cvalue" that will calculate data in column AD, so far I can only select the range.
Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbname As String, dcount As String, frml As String, wbsheet As String, wsname As String, r As Long, cValue As Variant
Dim wblist() As String, wbCount As Integer, i As Integer
Dim dy As String
Dim dlr As String
Worksheets("STATS").Select
dy = Selection.End(xlToLeft).Text
dlr = Selection.End(xlUp).Text
FolderName = "orsfiles"
' create list of workbooks in foldername
wbCount = 0
wbname = Dir(FolderName & "" & "*" & dy & ".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 = 0
Worksheets("Sheet1").Select
For i = 1 To wbCount
r = r + 1
wbsheet = (wblist(i))
Cells(r, 1).Formula = wblist(i)
wsname = Mid(wbsheet, 1, Len(wbsheet) - 4)
cValue = GetInfoFromClosedFile(FolderName, wblist(i), wsname, "AD:AD")
Cells(r, 2).Formula = cValue
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
Dim Trades As String
Dim dlrs
dlrs = Array("MD1", "AM1", "WG1", "HP1", "CW1", "CP1", "DL1")
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
If Dir(wbPath & "" & wbname) = "" Then Exit Function
arg = ("'" & wbPath & "[" & wbname & "]" & wsname & "'!" & Range(cellRef).Address(False, True, xlR1C1))
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbname As String, dcount As String, frml As String, wbsheet As String, wsname As String, r As Long, cValue As Variant
Dim wblist() As String, wbCount As Integer, i As Integer
Dim dy As String
Dim dlr As String
Worksheets("STATS").Select
dy = Selection.End(xlToLeft).Text
dlr = Selection.End(xlUp).Text
FolderName = "orsfiles"
' create list of workbooks in foldername
wbCount = 0
wbname = Dir(FolderName & "" & "*" & dy & ".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 = 0
Worksheets("Sheet1").Select
For i = 1 To wbCount
r = r + 1
wbsheet = (wblist(i))
Cells(r, 1).Formula = wblist(i)
wsname = Mid(wbsheet, 1, Len(wbsheet) - 4)
cValue = GetInfoFromClosedFile(FolderName, wblist(i), wsname, "AD:AD")
Cells(r, 2).Formula = cValue
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
Dim Trades As String
Dim dlrs
dlrs = Array("MD1", "AM1", "WG1", "HP1", "CW1", "CP1", "DL1")
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
If Dir(wbPath & "" & wbname) = "" Then Exit Function
arg = ("'" & wbPath & "[" & wbname & "]" & wsname & "'!" & Range(cellRef).Address(False, True, xlR1C1))
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function