Prev: finding matching text in different sheet
Next: Getting the name & path of a hidden WB
From: Len on 19 Feb 2010 01:18
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

From: Homey on 19 Feb 2010 06:58
change this code : If objFile.Type = "Microsoft Excel Worksheet" Then

to this

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


"Len" <ltong2000mal(a)yahoo.co.uk> wrote in message
news:d8c64681-74e8-4eb7-863d-05cdb7df47ca(a)q2g2000pre.googlegroups.com...
| 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
|

From: Len on 22 Feb 2010 03:56
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



 | 
Pages: 1
Prev: finding matching text in different sheet
Next: Getting the name & path of a hidden WB