Hi

I want to create a sheet which shows the current main folder in a cell.
I know of getopenfilename in vba but there the user has to specify the
file as well where I just want the folder.

How do I get a list of Files which are in a specific folder? As I want
to create a list of them and then open them up, extract some data and
then close them.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997

Use Application.Filesearch

HTH
--
AP

"funkymonkUK" a
écrit dans le message de news:
...

Hi

I want to create a sheet which shows the current main folder in a cell.
I know of getopenfilename in vba but there the user has to specify the
file as well where I just want the folder.

How do I get a list of Files which are in a specific folder? As I want
to create a list of them and then open them up, extract some data and
then close them.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997

sorry coudl you give me an example of this application.filesearch? I
cant seem to get it to work.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997

The current folder for the current drive can be found with

CurDir

Sub Openfiles()
sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.xls")
do while sName < ""
set bk = workbooks.open(sPath & sName
set sh = bk.worksheets(1)
set rng = sh.Range("A1")
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

"funkymonkUK" wrote:


Hi

I want to create a sheet which shows the current main folder in a cell.
I know of getopenfilename in vba but there the user has to specify the
file as well where I just want the folder.

How do I get a list of Files which are in a specific folder? As I want
to create a list of them and then open them up, extract some data and
then close them.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997

Filesearch has been claimed to be unreliable although I personally haven't
had any problems. the help example is pretty compreshensive:

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.FileName = ".txt"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

--
regards,
Tom Ogilvy


"funkymonkUK" wrote:


sorry coudl you give me an example of this application.filesearch? I
cant seem to get it to work.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997

Dim FSO As Object

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
sFolder = "C:\MyTest"
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
'do something with the workbook
.Close
End With
End If
Next file

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"funkymonkUK"
wrote in message
...

Hi

I want to create a sheet which shows the current main folder in a cell.
I know of getopenfilename in vba but there the user has to specify the
file as well where I just want the folder.

How do I get a list of Files which are in a specific folder? As I want
to create a list of them and then open them up, extract some data and
then close them.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997

Left out a closing parenthesis

Sub Openfiles()
Dim sPath as String, sName as String
dim bk as workbook, sh as worksheet
dim rng as Range
sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.xls")
do while sName < ""
set bk = workbooks.open(sPath & sName)
set sh = bk.worksheets(1)
set rng = sh.Range("A1")
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

The current folder for the current drive can be found with

CurDir

Sub Openfiles()
sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.xls")
do while sName < ""
set bk = workbooks.open(sPath & sName
set sh = bk.worksheets(1)
set rng = sh.Range("A1")
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

"funkymonkUK" wrote:


Hi

I want to create a sheet which shows the current main folder in a cell.
I know of getopenfilename in vba but there the user has to specify the
file as well where I just want the folder.

How do I get a list of Files which are in a specific folder? As I want
to create a list of them and then open them up, extract some data and
then close them.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997

I have a few examples here
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"funkymonkUK" wrote in message
...

Hi

I want to create a sheet which shows the current main folder in a cell.
I know of getopenfilename in vba but there the user has to specify the
file as well where I just want the folder.

How do I get a list of Files which are in a specific folder? As I want
to create a list of them and then open them up, extract some data and
then close them.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=537997