ExApp.Workbooks.Open not working

[damrus]

hi,
Why does this code work in Excel 2000 and doesn't work in Excel 2007? VBA shows runtime error '1004' at line:
Set Wb = ExApp.Workbooks.Open(nextfile, 0)

Sub h()
directory = ThisWorkbook.Path & "\"
Dim ExApp As Excel.Application
Dim Wb As Workbook
Dim nextfile As String
Dim myfile As String
myfile = "szkod*.xls"
nextfile = VBA.Dir(directory & myfile)

Do Until Len(nextfile) = 0

Set ExApp = New Excel.Application
'Set Wb = ExApp.Workbooks.Open(.FoundFiles(x))
Set Wb = ExApp.Workbooks.Open(nextfile, 0)
Debug.Print (nextfile)
Wb.Close False

Set Wb = Nothing
Set ExApp = Nothing
nextfile = VBA.Dir()
Loop
End Sub

 

[SkipVought]

Hi,

Why are you assigning a new application object and opening the workbook in that instance? Way too much unnecessary stuff...

Do Until Len(nextfile) = 0
        
    Set Wb = Workbooks.Open(nextfile, 0)
    Debug.Print (nextfile)
    Wb.Close False
    
    nextfile = VBA.Dir()
Loop
Set Wb = Nothing

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[damrus]

ok, but still the same problem. I can't open any file.

 

[SkipVought]

The modified code works for me in BOTH 2003 & 2007, opening workbooks in the target folder.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Are you running from a MACRO ENABLE WORKBOOK? Big difference in 2007!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]