?
Solved

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

Posted on 2011-10-20
8
Medium Priority
?
846 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:mlagrange
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37001381
<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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37001382
Try specifying the worksheet:

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

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37001423


try this revision

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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:Lucas
ID: 37001981
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

0
 

Author Closing Comment

by:mlagrange
ID: 37002163
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!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37002179
Good eyes, cap ;-)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37002192
hi miriam, hang on to the lead for the month.. i'll be your wingman
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37002564
Sounds good.

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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question