worksheetfunction not working when controlling excel from word.

gbrew584 · #1 05-23-2016, 04:28 AM

Many thanks for the reply, i thought I tried that, but I think maybe my method of testing the code may have failed. I tried to edit the code and then I would use F8 and drag the yellow arrow down to that bit of code, then I would press F5. but i think the problem is that my code was reset causing the code to loose the stored variables? Is there a way I test just one section of my code? Sorry I'm getting a little off the topic.

i tried your suggestion, but now I i am getting a script out of range error. I added xlbook.sheet(1), in front of .cell, but I don't think that is helping things. I changed sheet to sheets, but nothing happened. I'm getting the script out of range error. Do you have any suggestions for that?

Thanks again for taking the time to help me.

gbrew584 · #2 05-23-2016, 04:33 AM

gmayor,

thanks for the reply and the suggestion. I have added constants to my code to take care of the Excel specific commands, at least I thought that is what I they were doing. Is that not correct? Is there better or more correct way to do this?

macropod · #3 05-23-2016, 05:15 AM

Quote:

Originally Posted by gbrew584

Is there a way I test just one section of my code? Sorry I'm getting a little off the topic.

That's fairly basic. Just use something like:

Code:

Sub Test()
Dim xlApp As Object, xlbook As Object, LastRow As Long
Const xlpart = 2
Const xlbyrows = 1
Const xlprevious = 2
Const xlformulas = 5
Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.workbooks.Add
xlbook.Sheets(1).Range("A1").PasteSpecial
With xlbook.Sheets(1)
    If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
        LastRow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlpart, _
                      LookIn:=xlformulas, _
                      SearchOrder:=xlbyrows, _
                      SearchDirection:=xlprevious, _
                      MatchCase:=False).Row
     Else
         LastRow = 1
     End If
  End With
  MsgBox LastRow
End Sub

gbrew584 · #4 05-23-2016, 06:25 AM

Thanks, again, Paul for the suggestion. I guess I just don't know enough about vba to know the basics. Thanks for teaching me. I doubt I would know which bit of code to keep to do a test, but i appreciate the pointers. I can experiment with your advice and see how it goes.

Do you have any tips or hints as to why the code stops at

Code:

 LastRow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlpart, _
                      LookIn:=xlformulas, _
                      SearchOrder:=xlbyrows, _
                      SearchDirection:=xlprevious, _
                      MatchCase:=False).Row

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