How to prevent Reopening of target file ?
 

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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 7/24/2003

How to prevent Reopening of target file ?
 

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

--
Regards,
Tom Ogilvy

Milind wrote in message
...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 7/24/2003