Run-time error '1004' Method 'Rang

[bytehead]

I am developing an application in VB, which reads an Excel file and extracts cell data and writes it to the itermediate window for right now.

I would like for the application NOT to have to be closed to be able to process a second file or group of files. Unfortunately, every other time I run the application from the development area I get the following error after a files has been processed and I select another file to be processed:

Run-time error '1004'
Method 'Range' of object '_Global'failed

When seletecting Debug VB is highlighting the following line of code:
Set WorkRange = Excel.Range("A2&quot.Columns(1).EntireColumn

Below is the line of code before and after:
Dim WorkRange As Range
Set WorkRange = Excel.Range("A2&quot.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)

Any suggestions?

 

[CasperTFG]

From what I understand your saying. This error occurs the second time the file is opened. It could be that it still ahs it open. You could move the opening of the file to a global function that way it is opened and closed once or add the following line once you are down with the file.
[tt]
Set WorkRange = Nothing
[/tt] Craig, mailto:sander@cogeco.ca

Bow Before me for I am ROOT
<Beer>Happiness</Beer>

 

[bytehead]

Craig,

I have included the code which already exist in my application. As you can see I am already making sure the application is closed and quit before setting the range to nothing.

Private Sub Command3_Click()
Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject
FSO.CopyFile &quot;X:\nollb\bsnoflitm_template.xls&quot;, &quot;X:\nollb\bsnoflitm.xls&quot;, True

For ProcessLoop = 0 To List1.ListCount - 1
Dim xlapp As Excel.Application
Set xlapp = New Excel.Application

xlapp.Workbooks.Open (List1.List(ProcessLoop))
BeginRow = 2

Dim WorkRange As Range
Set WorkRange = Excel.Range(&quot;A2&quot.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
EndRow = i
Exit For
End If
Next i

For ExcelRow = BeginRow To EndRow
If (Excel.Range(&quot;A&quot; & ExcelRow).Value) <> &quot;&quot; _
And (Excel.Range(&quot;B&quot; & ExcelRow).Value) <> &quot;&quot; _
And (Excel.Range(&quot;D&quot; & ExcelRow).Value) <> &quot;&quot; Then
TRID07 = &quot;E0IA0101&quot;
ITNO07 = UCase(Replace(Trim(Excel.Range(&quot;A&quot; & ExcelRow).Value) _
& &quot; &quot;, &quot;-&quot;, &quot; &quot)
ITDS07 = Left(UCase(Trim(Excel.Range(&quot;B&quot; & ExcelRow).Value)) _
& &quot; &quot;, 30)
EGNO07 = UCase(Replace(Trim(Excel.Range(&quot;C&quot; & ExcelRow).Value) _
& &quot; &quot;, &quot;-&quot;, &quot; &quot)
UMST07 = UCase(Trim(Excel.Range(&quot;D&quot; & ExcelRow).Value))
ITYP07 = &quot;0&quot;
Debug.Print TRID07 & &quot;|&quot; & ITNO07 & &quot;|&quot; & ITDS07 & &quot;|&quot; _
& EGNO07 & &quot;|&quot; & UMST07 & &quot;|&quot; & ITYP07
Else
Debug.Print &quot;Nothing found in Row: &quot; & ExcelRow
End If
Next ExcelRow

xlapp.Workbooks.Close
xlapp.Quit
xlapp.Quit

Next ProcessLoop

Set FSO = Nothing
Set WorkRange = Nothing
List1.Clear
MsgBox &quot;Done!&quot;
End Sub

 

[CajunCenturion]

First thing that I would do is to move both the Dim xlapp As Excel.Application and Dim WorkRange As Range out of the loop, and place them at the beginning of the sub. I don't think its a good idea to have Dim statements within a loop.

I would move to the SET WorkRange = Nothing to inside the loop, just before the xlapp.workbooks.close, and I would add a Set xlapp = Nothing just after the xlapp.quit, but still inside the loop


Good Luck
------------
Select * from Users where Clue > 0
0 rows returned

 

[bytehead]

cajunCentruion,

I did as you said and still have the same results. Any other suggestions? I have included the new code for the sub.

Private Sub Command3_Click()
Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject
FSO.CopyFile &quot;X:\nollb\bsnoflitm_template.xls&quot;, &quot;X:\nollb\bsnoflitm.xls&quot;, True

Dim xlapp As Excel.Application
Dim WorkRange As Range

For ProcessLoop = 0 To List1.ListCount - 1

Set xlapp = New Excel.Application
xlapp.Workbooks.Open (List1.List(ProcessLoop))
BeginRow = 2

Set WorkRange = Excel.Range(&quot;A2&quot.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
EndRow = i
Exit For
End If
Next i

For ExcelRow = BeginRow To EndRow
If (Excel.Range(&quot;A&quot; & ExcelRow).Value) <> &quot;&quot; _
And (Excel.Range(&quot;B&quot; & ExcelRow).Value) <> &quot;&quot; _
And (Excel.Range(&quot;D&quot; & ExcelRow).Value) <> &quot;&quot; Then
TRID07 = &quot;E0IA0101&quot;
ITNO07 = UCase(Replace(Trim(Excel.Range(&quot;A&quot; & ExcelRow).Value) _
& &quot; &quot;, &quot;-&quot;, &quot; &quot)
ITDS07 = Left(UCase(Trim(Excel.Range(&quot;B&quot; & ExcelRow).Value)) _
& &quot; &quot;, 30)
EGNO07 = UCase(Replace(Trim(Excel.Range(&quot;C&quot; & ExcelRow).Value) _
& &quot; &quot;, &quot;-&quot;, &quot; &quot)
UMST07 = UCase(Trim(Excel.Range(&quot;D&quot; & ExcelRow).Value))
ITYP07 = &quot;0&quot;
Debug.Print TRID07 & &quot;|&quot; & ITNO07 & &quot;|&quot; & ITDS07 & &quot;|&quot; _
& EGNO07 & &quot;|&quot; & UMST07 & &quot;|&quot; & ITYP07
Else
Debug.Print &quot;Nothing found in Row: &quot; & ExcelRow
End If
Next ExcelRow

Set WorkRange = Nothing
xlapp.Workbooks.Close
xlapp.Quit
Set xlapp = Nothing
Next ProcessLoop

Set FSO = Nothing
List1.Clear
MsgBox &quot;Done!&quot;
End Sub