How to prevent Reopening of target file ?

[Milind]

I have an iteration thus:
For each c in worksheets.

For Each c In Worksheets("Sheet1").Range("A2:A5").Cells
valie = c.Value
If c.Value = 22381 Then
wala = c.Offset(0, 5).Value
If wala = "pills" Then
milla = c.Offset(0, 2).Value

counter = 0
ChDir "C:\milinda"
Workbooks.Open FileName:="C:\milinda\sample-1.xls"
Range("E" & counter + 1).Value = milla
counter = counter + 1
Endif
Endif
Next c


In this, if there are more than 1 occurrence, Excel reminds that the file
Sample-1 is already open, and reopening would reset it. How to prevent it?

Milind

 

[Tom Ogilvy]

Dim wkbk as workbook
For each c in worksheets.

For Each c In Worksheets("Sheet1").Range("A2:A5").Cells
valie = c.Value
If c.Value = 22381 Then
wala = c.Offset(0, 5).Value
If wala = "pills" Then
milla = c.Offset(0, 2).Value

counter = 0
ChDir "C:\milinda"
set Wkbk = nothing
On error Resume next
set Wkbk = Workbooks("sample-1.xls")
On Error goto 0
if wkbk is nothing then
Workbooks.Open FileName:="C:\milinda\sample-1.xls"
End if
Range("E" & counter + 1).Value = milla
counter = counter + 1
Endif
Endif
Next c