Extracting data from some files in folder

[Alf]

I have a number of Excel files (Excel 2003) in a folder and want t
extract data from some of them.

All the files in this folder are named "MS06XXX.0.xls" where XXX ar
numbers.

I would like to open a number of files from say MS06090.0.xls t
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" shee
so I will need some error handling for this.

The files with a "Trans" sheet are abouth 500 kb in size and the one
without this sheet are abouth 70 kb in size so perhaps this could b
used in stead of error handling. Something like "If filesize > 400 k
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
diggits this wont work.

Pleas post your ideas how to solve this problem

 

[Ardus Petus]

="MS06" & TEXT(i,"000") &".0.xls"

HTH

 

[RB Smissaert]

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]

Thanks that solved the file name problem

 

[Alf]

Thanks RB for info I'll see if I can mange it work. The information
need is taken from a small range (A2:C2) so using your sugestion wil
speed up the macro.

Have managed to write a macro but have troubles with error handeling.

See post: Problem with file finding macro

If you could look it over I'll be gratefull for any hints