Files\folders in VBA

[funkymonkUK]

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.

[Ardus Petus]

Use Application.Filesearch

HTH
--
AP

"funkymonkUK" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> 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
>

[funkymonkUK]

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

[Tom Ogilvy]

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
>
>

[Tom Ogilvy]

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
>
>

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Tom Ogilvy]

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
> >
> >

[Ron de Bruin]

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


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


"funkymonkUK" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>