return last row of excel using word macro

Question

How to copy Undefined range from excel and past in a word document using Word macro. I tried the below but could not succeed. can you please help me.

Note : If I mention the range its working fine, whereas I want to use the code NumberOfRows = .Range("A65536").End(xlUp).Row in word macro - I am getting run time error 1004.

Sub InputExcel()

Set appExcel = CreateObject("Excel.Application")

Dim INP_File As Variant
Dim lngRows As Long
Dim LenMgs As Long
Dim NumberOfRows As String

INP_File = appExcel.GetOpenFilename("Excel files (*.xlsx;*.xls),*.xlsx;*.xls", 2)

appExcel.Workbooks.Open INP_File

If INP_File > 0 Then

    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "Sample"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.HomeKey Unit:=wdLine

    LenMgs = appExcel.Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    'NumberOfRows = appExcel.Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    Set Rng = appExcel.Worksheets("Sheet1").Range(appExcel.Worksheets("Sheet1").Cells(4, 1), appExcel.Worksheets("Sheet1").Cells(LenMgs, 5))
    'Rng.Copy
    'appExcel.Worksheets("Sheet1").Range("A1:B5").Copy - This is working !! if I specify the range.
    Selection.Paste

End If
appExcel.ActiveWorkbook.Close

appExcel.Quit

Set appExcel = Nothing

End Sub

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-09-16T11:32:00

You are getting the error because xlUp is an Excel constant and it is not recognized by MS Word as you are using Late Binding to connect with Excel.

You have to declare this as the top of your code

Const xlup = -4162

Also you might want to read THIS for finding last row in Excel?

return last row of excel using word macro

1 Answers