Can I have a loop to open a set of workbooks get some data, close it one a time.

[wellie]

I'm trying to have a loop to open a set of workbook one at
a time to sort and copy some data out and then close it.
The following code ONLY works for the first workbook, it
fails when it tries to open the 2nd workbook and prompted
me "Run-time error '1004' Application-defined or object-
defined error."

Can someone please tell me what I need to do to have s.t.
I can open this set of workbooks in a loop ?

Thanks in advance for any assistance.

dim wkbk as Workbook
dim fPathname as String

For i=1 to 5
fPathname = ("c:\Myfile"+cstr(i)+".xs"
Set wkbk = Workbooks.Open(fPathname)
j = 10
Do While wkbk.Sheets(1).Cells(j, 8).Value > 0
j = j + 1
Loop
MsgBox (fPathname + " contains " + CStr(j - 1) + " rows
of data.")
' *** Copy data and other stuff here.
Application.DisplayAlerts = False
wkbk.Close fPathname
Application.DisplayAlerts = True
Set wkbk = Nothing
Next i

 

[Dan E]

Wellie,

I didn't test this out, but it looks like your setting your wkbk to nothing
then trying to have it set to a workbook again without doing it...

Try putting

dim wkbk as Workbook
dim fPathname as String

inside your loop instead of before it

Dan E

 

[Dave Peterson]

You can get a 1004 error if you try to open a workbook that doesn't exist. So
you could add a check for existance:

Option Explicit
Sub testme99()

Dim i As Long
Dim j As Long
Dim wkbk As Workbook
Dim fPathname As String

For i = 1 To 5
fPathname = "c:\Myfile" & CStr(i) & ".xls"
If Dir(fPathname) = "" Then
MsgBox "missing file: " & fPathname
Else
Set wkbk = Workbooks.Open(fPathname)
j = 10
Do While wkbk.Sheets(1).Cells(j, 8).Value > 0
j = j + 1
Loop
MsgBox (fPathname + " contains " + CStr(j - 1) + " rows of data.")
' *** Copy data and other stuff here.
Application.DisplayAlerts = False
wkbk.Close fPathname
Application.DisplayAlerts = True
Set wkbk = Nothing
End If
Next i
End Sub

I dimmed some more variables and changed the + to & in the fpathname line. (and
added an L to the fpathname, too.)