asked on

Getting "1004 - Method 'Rows' of object '_Global' failed"

Hello - I've worked up a VBA routine that runs from Access 2003 to search a Excel 2003 xls cell by cell, using the code below.

It is called by a routine that searches a set of nested folders recoursively, looking for files with a particular string in the file name.

It works fine in the first workbook it finds, but when it runs through the next workbook, it blows up with:
1004 - Method 'Rows' of object '_Global' failed
on the line below:
LastRow = .Range("A" & Rows.count).End(xlUp).Row

I believe I am closing all the Excel obj var's properly (also below), but I must not be?
When I look at Task Manager after it blows up, I see two "EXCEL.EXE"'s running in the Process tab.

What am I doing wrong?

Thanks

For Each wks In wbk.Worksheets
   With wks
      LastRow = .Range("A" & Rows.count).End(xlUp).Row '-- <<< blows up here 2nd time thru
      LastCol = .UsedRange.Columns.count
   End With

   For intRows = 1 To wks.UsedRange.Rows.count
      For intCols = 1 To wks.UsedRange.Columns.count
      Next '-- Col
   Next '-- row
Next '-- wks

'-- at the end of each workbook
    wbk.Close
    app.Quit

    Set wks = Nothing
    Set wbk = Nothing
    Set app = Nothing

Open in new window

Rey Obrero (Capricorn1)

<When I look at Task Manager after it blows up, I see two "EXCEL.EXE"'s running in the Process tab.>

select each one and click End Task

try your codes again..

if error still, post your whole code

mbizup

Try specifying the worksheet:

LastRow = .Range("A" & wks.Rows.count).End(xlUp).Row

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lucas

Can you post your variable declarations? I had a similar problem with this back in 2006. The reason why it crashes is because the pointer in memory is no longer valid. Put a watch on your wks and wbk variables.

You have to tell it exactly by using the your excel object, workbook, and worksheet.

I'll dig up my old code to show you exactly but i'm 100% certain that this is the problem. The code below is just an example.

For Each wks In wbk.Worksheets
      wks.Activate
      LastRow = Excelobject.Workbook.wks.Range("A" & Excelobject.Workbook.wks.Rows.count).End(xlUp).Row '-- <<< blows up here 2nd time thru
      LastCol = Excelobject.Workbook.wks.UsedRange.Columns.count
   End With

   For intRows = 1 To wks.UsedRange.Rows.count
      For intCols = 1 To wks.UsedRange.Columns.count
      Next '-- Col
   Next '-- row
Next '-- wks

Open in new window

mlagrange

ASKER

That did it! Made 2 runs, processing 4 wkb's each; ran to completion both times, and no EXCEL.EXE's left behind.

Thanks very much!

mbizup

Good eyes, cap ;-)

Rey Obrero (Capricorn1)

hi miriam, hang on to the lead for the month.. i'll be your wingman

mbizup

Sounds good.

I don't think I can keep the pace up for more than a month, though! :)

Getting &quot;1004 - Method 'Rows' of object '_Global' failed&quot;

Getting "1004 - Method 'Rows' of object '_Global' failed"