worksheetfunction not working when controlling excel from word.

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

gbrew584 · #2 05-24-2016, 04:28 AM

Thanks for the help and the great suggestions. I did as Paul suggested and tried to run the snipet of code by itself but it failed, that is what prompted my question about why it stopped working. I try to do my part and i always try people suggestions, run the code, make corrections, and then do tests before I come back and say something isn't working. I Appreciate that you are taking the time to help me as I am trying to lean more about writing code.

I will work on the code this afternoon to try and get the kinks worked out, thanks for your suggestions. I'll keep you posted on the progress. Thanks!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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