Code to open multiple files from within a folder

[Karen]

Hi all,

I'm having problems with this script below. I need some Excel VBA code that
will open multiple files from within a folder. Sometimes there maybe 5 files
to open, sometimes there may be more. I will never know how many files will
be in the folder each time I execute the code, and I won't know the file
names in the folder either.

The script below is nearly correct, however it seems to retain the file
names from the last time it was executed. I need a way of flushing it out so
it doesn't retain anything when it ends. If it retains the file names then it
expects them to be there when it is next executed, which of corse causes
errors.

Thanks for any help with this - regards Karen. The script I'm using is
pasted below.

With Application.FileSearch
.NewSearch
.LookIn = "C:\testfolder"
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i)
MsgBox .FoundFiles(i)
Next i
End If
End With

 

[anon]

If you are simply looking to open every file within the folder that is
Excel try;

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objSubfolder As Scripting.Folder
Dim objFile As Scripting.File
Dim iRow As Long
Dim istr as string

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\test\")
For Each objFile In objFolder.files
If objFile.Type = "Microsoft Excel Worksheet" Then
istr = objFolder.Path & "\" & objFile.Name
Workbooks.Open Filename:=str
else
'do nothing
end if
next objfile

 

[Karen]

Thanks for your help anon - however is there someting else I need to include
as it comes up with the error message "User-defined type not defined" - on
the Dim variable declarations.

Thanks
Karen

 

[Bob Phillips]

Try this variation

Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim iRow As Long
Dim istr as string

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\test\")
For Each objFile In objFolder.files
If objFile.Type Like "Microsoft*Excel*Worksheet*" Then
istr = objFolder.Path & "\" & objFile.Name
Workbooks.Open Filename:=str
else
'do nothing
end if
next objfile

 

[Dave Peterson]

Try changing:

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objSubfolder As Scripting.Folder
Dim objFile As Scripting.File

to

Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object

======
An alternative is to set a reference (tools|references) to
Microsoft Scripting Runtime

 

[Karen]

Thanks for your help guys.

Kind regards
Karen