You don't have to open the workbooks, and if the range to get values from
is small then that could be faster:
Sub Test()
Dim i As Long
Dim vValue
Dim arr(1 To 10)
vValue = GetValueFromWB("C:\testfolder\", _
"testfile.xls", _
"Sheet1", _
Cells(1).Address)
MsgBox vValue
For i = 1 To 10
arr(i) = GetValueFromWB("C:\testfolder\", _
"testfile.xls", _
"Sheet1", _
Cells(i, 1).Address)
MsgBox arr(i)
Next
End Sub
Function GetValueFromWB(path, file, sheet, ref)
'Retrieves a value from a closed workbook
'----------------------------------------
Dim strSep As String
Dim arg As String
'Make sure the file exists
'-------------------------
If Right$(path, 1) < "\" Then
path = path & "\"
End If
If bFileExists(path & file) = False Then
GetValueFromWB = "File Not Found"
Exit Function
End If
'Create the argument
'-------------------
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
'Execute an XLM macro
'--------------------
GetValueFromWB = ExecuteExcel4Macro(arg)
End Function
Function bFileExists(ByVal sFile As String) As Boolean
Dim lAttr As Long
On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0
End Function
RBS
"Alf" wrote in message
...
I have a number of Excel files (Excel 2003) in a folder and want to
extract data from some of them.
All the files in this folder are named "MS06XXX.0.xls" where XXX are
numbers.
I would like to open a number of files from say MS06090.0.xls to
MS06210.0.xls
After opening a file a need to extract data from a sheet called "Trans"
and close the file. In some of the files there will be no "Trans" sheet
so I will need some error handling for this.
The files with a "Trans" sheet are abouth 500 kb in size and the ones
without this sheet are abouth 70 kb in size so perhaps this could be
used in stead of error handling. Something like "If filesize 400 kb
Then"
I also thought I could use i = 90 To 210 Step 1
and construct the file name ="MS06" & i &".0.xls" but if "i" is only 2
diggits this wont work.
Pleas post your ideas how to solve this problem.
--
Alf
------------------------------------------------------------------------
Alf's Profile:
http://www.excelforum.com/member.php...fo&userid=7112
View this thread: http://www.excelforum.com/showthread...hreadid=550660