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

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

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

"anon" wrote:

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

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

--
__________________________________
HTH

Bob

"Karen" <(E-Mail Removed)> wrote in message
news:321351F7-14B4-4095-8F6B-(E-Mail Removed)...
> 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
>
> "anon" wrote:
>
>> 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
>>

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 wrote:
>
> 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
>
> "anon" wrote:
>
> > 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
> >

--

Dave Peterson

Thanks for your help guys.

Kind regards
Karen

"Karen" wrote:

> 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