CODEWITHSUNDEEP
vbaexcelms-word
K.R. pnx
K.R. pnx

Reputation: 69

Calculating the number of rows in a column

I want to import the data in the list of listbox1 in the word from column "A" in the excel

Sub Macro3()
Dim excelobject As Object, wb As Object, r As Long, i As Integer
Dim hh As Boolean
Dim a As String
Dim b As Integer
Set excelobject = CreateObject("excel.application") 
excelobject.Visible = False   

Set wb = excelobject.Workbooks.Open("C:\test.xlsx")

Me.ListBox1.Visible = True

Me.ListBox1.Clear
For i = 2 To wb.Sheets(1).Cells(Rows.count, 1).End(xlUp).Row
    If InStr(UCase(wb.Sheets(1).Cells(i, 1).Value), UCase(a)) > 0 Then
    Me.ListBox1.AddItem wb.Sheets(1).Cells(i, 1).Value
    End If
Next i
excelobject.Quit
end sub

but run wb.Sheets(1).Cells(Rows.count, 1).End(xlUp).Row msgbox display "object required". Where did I write wrong, why in Excel, this property is available？

Upvotes: 0

Views: 123

Answers (3)

V. V. Kozlov
V. V. Kozlov

Reputation: 209

If I may make a suggestion, be sure to use Early Binding when accessing other libraries, such as Excel from Word. Not all libraries work with early binding, but when used, you get all the benefits of intellisense, plus all enumerations (what xlUp is a part of) are visible. Also the code should run just a bit faster.

You would have to tick the Microsoft Excel 15.0 Object Library under Tools -> References...

(It may be 13.0, 14.0, or 16.0, depending on your version of Office)

Your code would then look like

Sub Macro3()
    Dim excelobject As Excel.Application, wb As Object, r As Long, i As Integer
    Dim hh As Boolean
    Dim a As String
    Dim b As Integer
    Set excelobject = New Excel.Application
    excelobject.Visible = False

    Set wb = excelobject.Workbooks.Open("C:\test.xlsx")

    Me.ListBox1.Visible = True

    Me.ListBox1.Clear
    For i = 2 To wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
        If InStr(UCase(wb.Sheets(1).Cells(i, 1).Value), UCase(a)) > 0 Then
        Me.ListBox1.AddItem wb.Sheets(1).Cells(i, 1).Value
        End If
    Next i
    wb.Close
    excelobject.Quit
End Sub

I would also close the workbook before closing Excel. It may need an argument like false to indicate not to save the sheet.

As @Mat's Mug correctly suggests, without using Option Explicit, uninitialised variables are Variant with an initial value of 0 by default. Unhelpful for debugging.

Early Binding in conjunction with Option Explicit will do more error checking at compile time and help eliminate errors.

Upvotes: 2

Ahmad
Ahmad

Reputation: 12717

The problem is in the constant xlUp in wb.Sheets(1).Cells(Rows.count, 1).End(xlUp).Row

Because you are not importing or referencing Excel library into your project, there is no way for your program to know what you mean by xlUp.

Luckily, you can obtain the value inside that constant by going to any Visual Basic Immediate tab in Excel VBA and simply typing 

? xlUp

Output:

-4162

Try replacing your line:

wb.Sheets(1).Cells(Rows.count, 1).End(xlUp).Row

with this one:

wb.Sheets(1).Cells(Rows.count, 1).End(-4162).Row
' or if you worry that constant can have different values in 
' different Excel versions, you may simply explicitly define it as wb.xlDirection.xlUp

wb.Sheets(1).Cells(Rows.count, 1).End(wb.xlDirection.xlUp).Row

Note: the same issue can also apply for the Rows.count. In that case, you need to explicitly define it as wb.Sheets(1).Rows.count instead:

wb.Sheets(1).Cells(wb.Sheets(1).Rows.count, 1).End(wb.xlDirection.xlUp).Row

Upvotes: 3

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

So, my first concern is that you may not be opening the workbook correctly. Possibly an incorrect path or file name.

Also, just like you referenced .Cells to your workbook, you also need to reference Rows.Count. I added a With...End With statement for this reason.

Code:

Sub Macro3()
    Dim excelobject As Object, wb As Object, r As Long, i As Integer
    Dim a
    
    Set excelobject = CreateObject("excel.application")
    excelobject.Visible = False
    Set wb = excelobject.Workbooks.Open("C:\test.xlsx")
    If wb Is Nothing Then
        MsgBox "YOU DO NOT HAVE THE CORRECT PATH TO YOUR WORKBOOK"
        Exit Sub
    End If
    With wb.Worksheets(1)
        a = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    MsgBox a
    excelobject.Quit
End Sub

You should also avoid using sheets() and instead use Worksheets(). While in many cases sheets() will work for you, if you have a chart or something other than a worksheet as index 1, then that could be causing your error.

Think about it this way: All worksheets are sheets, but not all sheets are worksheets.

Upvotes: 1

Related Questions