Microsoft Office Forums - View Single Post - [Solved] worksheetfunction not working when controlling excel from word.

gmayor · 05-23-2016, 08:54 PM

It doesn't work for me either - but the following does. Note that Paul's code will leave an invisible instance of Excel running, with a worksheet containing the pasted values, which you will need to close from Windows Task manager. If you are going to run Excel invisibly then ensure that you close the workbook and quit the Excel application when it is finished with. The following displays the workbook for testing purposes.

Code:

Sub Test()
Dim xlApp As Object, xlbook As Object, LastRow As Long

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlbook = xlApp.workbooks.Add
    xlbook.Sheets(1).Range("A1").PasteSpecial
    With xlbook.Sheets(1)
        If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow = xlbook.Sheets(1).Range("A" & xlbook.Sheets(1).Rows.Count).End(-4162).Row
        Else
            LastRow = 1
        End If
    End With
    MsgBox LastRow
End Sub