ExcelBanter - Extracting data from some files in folder

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Extracting data from some files in folder (https://www.excelbanter.com/excel-programming/363896-extracting-data-some-files-folder.html)

Extracting data from some files in folder

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

--
Al
-----------------------------------------------------------------------
Alf's Profile: http://www.excelforum.com/member.php...nfo&userid=711
View this thread: http://www.excelforum.com/showthread.php?threadid=55066

Extracting data from some files in folder

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

Extracting data from some files in folder

Thanks that solved the file name problem

--
Al
-----------------------------------------------------------------------
Alf's Profile: http://www.excelforum.com/member.php...nfo&userid=711
View this thread: http://www.excelforum.com/showthread.php?threadid=55066

Extracting data from some files in folder

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

--
Al
-----------------------------------------------------------------------
Alf's Profile: http://www.excelforum.com/member.php...nfo&userid=711
View this thread: http://www.excelforum.com/showthread.php?threadid=55066