Many ways to extract data from closed workbook. I chose to use the following function written by David Hager (I'm not smart enough to come with this kind of stuff!):
_____________________________________________
Function CWRIA(fPath As String, fName As String, sName As String, rng As String)
Dim sRow As Integer
Dim sColumn As Integer
Dim sRows As Integer
Dim sColumns As Integer
Dim vrow As Integer
Dim vcol As Integer
Dim fpStr As String
Dim cArr()
On Error GoTo NoArr
If Right(fPath, 1) <> "" Then fPath = fPath & ""
If Dir(fPath & fName) = "" Then
CWA = CVErr(xlErrValue)
Exit Function
End If
sRow = Range(rng).Row
sColumn = Range(rng).Column
sRows = Range(rng).Rows.Count
sColumns = Range(rng).Columns.Count
ReDim cArr(sRows, sColumns)
For vrow = 1 To sRows
For vcol = 1 To sColumns
fpStr = "'" & fPath & "[" & fName & "]" & sName & "'!" & _
"r" & sRow + vrow - 1 & "c" & sColumn + vcol - 1
cArr(vrow, vcol) = ExecuteExcel4Macro(fpStr)
Next
Next
CWRIA = cArr
Exit Function
NoArr:
CWRIA = CVErr(xlErrValue)
End Function
Sub CWRIR(fPath As String, fName As String, sName As String, rng As String, destRngUpperLeftCell As String )
Dim sRow As Integer
Dim sColumn As Integer
Dim sRows As Integer
Dim sColumns As Integer
Dim vrow As Integer
Dim vcol As Integer
Dim fpStr As String
Dim cArr()
On Error GoTo NoArr
If Right(fPath, 1) <> "" Then fPath = fPath & ""
If Dir(fPath & fName) = "" Then
CWA = CVErr(xlErrValue)
Exit Function
End If
sRow = Range(rng).Row
sColumn = Range(rng).Column
sRows = Range(rng).Rows.Count
sColumns = Range(rng).Columns.Count
ReDim cArr(sRows, sColumns)
Set destRange = ActiveSheet.Range(destRngUpperLeftCell)
For vrow = 1 To sRows
For vcol = 1 To sColumns
fpStr = "'" & fPath & "[" & fName & "]" & sName & "'!" & _
"r" & sRow + vrow - 1 & "c" & sColumn + vcol - 1
destRange.Offset(vrow - 1, vcol - 1) = ExecuteExcel4Macro(fpStr)
Next
Next
NoArr:
End Sub
_____________________________________________
Cut and paste the above code into a module and then write your macro similar to below (in your case):
Sub ExtractDataSample()
Dim Filepath$, Filename$, Sheetname$, Src$, Dest$
'Source Information
Filepath = "C
athwhereeveryourfileis"
Filename = "file.xls"
Sheetname = "total"
'Extract data from source range:
Src = "G5"
'Destination cell
Dest = "A1"
'insert line here if you need to activate your workbook/sheet
CWRIR Filepath, Filename, Sheetname, Src, Dest
End Sub
Change source info (path, filename, sheetname, & source cell range) to meet your needs. You can specify a single cell or an array as range. Code assumes that the destination cell is in the activeworkbook--which is why you only specify the cell--so you may have to add a line of code specifying which workbook worksheet to activate if it's not the activesheet at runtime. Hope it helps.
Aloha!
