Excel Files not opened

[Len]

Hi,

It seems that after running the codes below in excel 2007, no files
are detected and opened from "J" path. In fact there are 4 excel files
in "J" folder that need to be opened
Does the code below miss out anything ?

Sub OpenFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("J:\BBC\LO\Budget\Acad") ' Change to
suit
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
'Do things
ActiveWorkbook.Close True 'or false
End If
Next
End Sub

Any help will be greatly appreciated

Thanks & Regards
Len

 

[Homey]

change this code : If objFile.Type = "Microsoft Excel Worksheet" Then

to this

If InStr(1, objFile.Type, "Microsoft Excel") > 0 Then


| Hi,
|
| It seems that after running the codes below in excel 2007, no files
| are detected and opened from "J" path. In fact there are 4 excel files
| in "J" folder that need to be opened
| Does the code below miss out anything ?
|
| Sub OpenFiles()
| Dim objFSO As Object
| Dim objFolder As Object
| Dim objFile As Object
| Set objFSO = CreateObject("Scripting.FileSystemObject")
| Set objFolder = objFSO.GetFolder("J:\BBC\LO\Budget\Acad") ' Change to
| suit
| For Each objFile In objFolder.Files
| If objFile.Type = "Microsoft Excel Worksheet" Then
| Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
| 'Do things
| ActiveWorkbook.Close True 'or false
| End If
| Next
| End Sub
|
| Any help will be greatly appreciated
|
| Thanks & Regards
| Len
|

 

[Len]

Hi Homey,

Thanks for your reply as I just came back from holidays

After work around based on your codes, it doesn't work
Later, I found the codes below quite similar from other forums and it
works in excel 2007

If objFile.Type Like "Microsoft*Excel*Worksheet*" Then

Thanks anyway

Regards
Len