worksheetfunction not working when controlling excel from word.

gmayor · #9 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word to Excel Links Stop Working	DAC8190	Word	1	10-08-2015 05:56 PM
Controlling Widows (single-word lines at the end of a paragraph)	downtownbooks	Word	2	12-09-2014 03:57 PM
Word and Excel stopped working	judyn	Office	9	04-23-2013 05:10 AM
Controlling search option in word	suvvi	Word	0	08-05-2011 06:10 AM
working with excel tables in MS word	radman154	Word Tables	1	03-25-2011 12:04 AM