MS-Access send records to Excel and Count Records in Excel

SamwiseVB 0
Viewed 28

I have an access form with a button to send a record set to excel.

It works 70% of the time but for some reason I get an error "Runtime error '1004' Method 'Rows' of object '_Global' Failed".

Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range

rst.open [Select Query]

Loop through record set and copy values

i = 2
            
Do Until rst.EOF
    dblTotHr = rst!Hours
                
    i = 2
            
    Do Until rst.EOF
        dblTotHr = rst!Hours
                
        xlws.Range("a" & i).Value = rst!A
        xlws.Range("b" & i).Value = rst!B
        xlws.Range("c" & i).Value = rst!C
                
        i = i + 1
        rst.MoveNext
    Loop

Code that fails (sometimes):

lrow = xlws.Cells(Rows.Count, 1).End(xlUp).Row

Clean up:

xlApp.Visible = True
Set xlApp = Nothing
Set xlwb = Nothing
Set xlws = Nothing
Set xlrng = Nothing
Set rst = Nothing
Set rst1 = Nothing

Since this code works sometimes and not others with no pattern, I am confused on where to try and start looking for answers.

Any help is much appreciated!!

ms-access vba excel
1 Answers

The fix is

lrow = xlws.Cells(xlws.Rows.Count, 1).End(xlUp).Row

Otherwise you cannot be sure Rows refers to the sheet xlws. Interesting that it sometimes resp. quite often works.

If you use Rows in a VBA code within Excel it refers to the ActiveSheet unless you add an explicit reference. This can even cause issues in Excel as the ActiveSheet can be a diagramm.

To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application object or the Excel Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when you try to run the automation code multiple times. Note that even if the line of code begins with the object variable, a call may be made to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.

Further reading

Storax0
Related