Convert Text to Table in Word from Excel (VBA)

[japers]

My second question of the day! I am succesfully copying text from a Excel into a new document in Word using VBA but I am struggling to then convert the resulting table into text (this text is formatted so I cannot choose Paste Special --> unformatted text).

My code is below, the first part is copied and adapted from various code I found on this forum. The second part if a combination of Macro recorder and more internet searches. My problem, I think, is that I don't know how to refer to the appropriate command:

Sub CopyWorksheetToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library

Worksheets("Award").Range("C1:E50").Copy

Dim ws As Worksheet
Dim wdApp As Word.Application
Dim wdDoc As Word.Document

    Application.ScreenUpdating = False
    Application.StatusBar = "Creating new document..."
    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Add
         wdDoc.Range.Paste
         Application.CutCopyMode = False
               
    ' apply print view
    With wdApp.ActiveWindow
        If .View.SplitSpecial = wdPaneNone Then
            .ActivePane.View.Type = wdPrintView
        Else
            .View.Type = wdPrintView
        End If
    End With

' Second Part Below - First Part Above Works

wdApp.Visible = True
wdApp.Activate

With ActiveDocument. ' Here is my problem!!! Tried many combinations.
    .Rows.ConvertToText Separator:=wdSeparateByTabs, NestedTables:= _
        True
End With

' Parts Below untested as Table needs to be converted first

wdDoc.Select

With ActiveDocument.PageSetup
.RightMargin = CentimetersToPoints(4)
End With
        
With ActiveDocument.Styles(wdStyleNormal).Font
    .name = "Arial"
    .Size = 12
End With

End Sub

Any help as usual, very gratefully received!

 

[japers]

I have managed to simplify all this code and for the purposes of this query have removed a few working functions irrelevant to this query. I have also got a little bit further by finding the "Tables" operator but still stuck:

Sub CopytoWord()
Dim Wdapp As Object
    
Worksheets("Award").Range("C1:E50").Copy
     
Application.ScreenUpdating = False
    
    Set Wdapp = CreateObject("Word.Application")
    Wdapp.Documents.Add
      With Wdapp.ActiveDocument
           .Range.Paste
      End With
    Wdapp.Visible = True
  
' All the above works, below is the problem
    
      With Wdapp.ActiveDocument.Tables
          Table.Rows.ConvertToText Separator:=wdSeparateByTabs
      End With
      
End Sub

My problem is that I get a 424 "Object Required" Error. I have set Wdapp to be an object and added it in front of "ActiveDocument" so really not sure where I have gone wrong?

 

[Trevor G]

Look at this link, it suggests you need a bookmark.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=702

You will find a number of options if you google convert table to text word vba

 

[japers]

Thanks very much Trevor, that is a very interesting link and I got it working no problems from Word. There are indeed loads of posts on how to convert Table to Text in VBA but these all work from within Word. I could not find one which would help me do it from Excel despite a lot of searching - perhaps I have missed them?

Based on the linked script above, I have tried to create a Word bookmark in Excel. Again there are plenty of posts on how to do this but I could find one that simply selects "all" and bookmarks it? I have therefore tried the following based on my searches and got stuck:

Dim BMRange As Word.Range

Set BMRange = Wdapp.ActiveDocument.Selection.WholeStory

Wdapp.ActiveDocument.Bookmarks.Add Range:=BMRange, name:="bmTable"

What ever permutation I try, I get stuck on line 2. I also tried Set BMRange = Wdapp.ActiveDocument.Range instead which is perhaps better. Any ideas?

 

[Trevor G]

Sorry in late reply I had to go out for a while.

What I have done is created a spreadsheet with data in the same range as you mention, then I have gone into the VBA screen went to the tools menu and References and set the references to work with Microsoft Word 13.Object Library. I have then set about looking to replicate your code. Now because I am using Office 2007 in word it wasn't possible to highlight the normal way so I have used the keyboard. I have ended up with the following code which works each time from Excel into Word, Create a new document then paste the table in then highlight and convert table to text. Please change sheet name.

I hope this will help you.

Sub wrd1()
Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")
Worksheets("Sheet1").Select
Range("C1:E50").Copy

With wrdApp
.Documents.Add
.Selection.Paste
.Visible = True
.Selection.HomeKey Unit:=wdStory
.Selection.MoveRight Unit:=wdCharacter, Count:=8, Extend:=wdExtend
.Selection.MoveDown Unit:=wdLine, Count:=51, Extend:=wdExtend
.Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
.Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, NestedTables:= _
True
End With
End Sub

 

[japers]

Trevor, that is fantastic - thank you so much for solving my problem! As usual the final solution looks so easy but was so elusive!

I have expanded on your script and after a short fight with Tabstops, I got a funny error cropping up, here is the added code:

.Selection.ParagraphFormat.TabStops.ClearAll
.Selection.ParagraphFormat.TabStops.Add Position:=CentimetersToPoints(0.75) _
        , Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces

Now the funny thing is that this code works intermittently with the following Error:

462 The remote server machine does not exist or is unavailable

Basically it reliably cycles between working once and failing once and the error points at the second line. MS Word being open or closed before running the whole script makes no difference. The error is linked to commanding Word externally and following quite a bit of googling, I tried adding "wrdApp." before Selection.Paragraph.... but this makes no difference.

I know I've taken enough of your time already but I don't suppose you have any idea what could be causing this?

 

[japers]

Hi Trevor

I have tightened the script from my previous post using "With" where possible and this seems somehow to have eliminated this error

May I ask you one (hopefully) last question, as part of my formatting, I would like to select a specific section of text between two keywords. This section can change in length so using fixed line ranges will not work. I have therefore found the following code which works perfectly within Word. My problem again is applying it from VBA within Excel.

Firstly the code working within Word:

Sub SelectRangeBetween()

' Types the text
Selection.HomeKey Unit:=wdStory
Selection.TypeText Text:="Hello and Goodbye"

' The Real script
Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Execute findText:="Hello", Forward:=True, Wrap:=wdFindStop
Set myrange = Selection.Range
myrange.End = ActiveDocument.Range.End
myrange.Start = myrange.Start + 5
myrange.End = myrange.Start + InStr(myrange, "Goodbye") - 1
myrange.Select
End With
End Sub

And now my Excel adaptation:

Sub SelectRangeBetween()

Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")

With wrdApp
.Documents.Add
.Visible = True

' Types the text
.Selection.HomeKey Unit:=wdStory
.Selection.TypeText Text:="Hello and Goodbye"

' The Real script
Dim myrange As Range
.Selection.HomeKey wdStory
.Selection.Find.ClearFormatting

With .Selection.Find
.Execute findText:="Hello", Forward:=True, Wrap:=wdFindStop
Set myrange = Selection.Range
' Problem is here:
myrange.End = wrdApp.ActiveDocument.Range.End
myrange.Start = myrange.Start + 5
myrange.End = myrange.Start + InStr(myrange, "Goodbye") - 1
myrange.Select
End With

End With
End Sub

The error returned is "Argument not optional" and has me baffled once again...

 

[Trevor G]

Trevor, that is fantastic - thank you so much for solving my problem! As usual the final solution looks so easy but was so elusive!

I have expanded on your script and after a short fight with Tabstops, I got a funny error cropping up, here is the added code:

.Selection.ParagraphFormat.TabStops.ClearAll
.Selection.ParagraphFormat.TabStops.Add Position:=CentimetersToPoints(0.75) _
        , Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces

Now the funny thing is that this code works intermittently with the following Error:

462 The remote server machine does not exist or is unavailable

Basically it reliably cycles between working once and failing once and the error points at the second line. MS Word being open or closed before running the whole script makes no difference. The error is linked to commanding Word externally and following quite a bit of googling, I tried adding "wrdApp." before Selection.Paragraph.... but this makes no difference.

I know I've taken enough of your time already but I don't suppose you have any idea what could be causing this?

I have just come on line,

What I would suggest is that you look to record macros in word to finish your steps and then take a look at the code in Word and copy some of the code across. Dealing with the tabs first I have done exactly as I am suggesting and here is the end result, I have added an extra with statement to deal with the tabs, its not necessary but I wanted to break it down in steps so you can see if there is an issue, once you are happy I would combine the 2 withs into 1. I have highlighted what is missing from your extra code. I will look at your next step and post back.

Sub wrd1()
Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")
Worksheets("Sheet1").Select
Range("C1:E50").Copy
With wrdApp
.Documents.Add
.Selection.Paste
.Visible = True
.Selection.HomeKey Unit:=wdStory
.Selection.MoveRight Unit:=wdCharacter, Count:=8, Extend:=wdExtend
.Selection.MoveDown Unit:=wdLine, Count:=51, Extend:=wdExtend
.Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
.Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, NestedTables:= _
True
End With
With wrdApp
.Selection.ParagraphFormat.TabStops.ClearAll
.ActiveDocument.DefaultTabStop = CentimetersToPoints(1.27)
.Selection.ParagraphFormat.TabStops.Add Position:=CentimetersToPoints(0.75) _
, Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
End With
End Sub

 

[Trevor G]

Hi Trevor

I have tightened the script from my previous post using "With" where possible and this seems somehow to have eliminated this error

May I ask you one (hopefully) last question, as part of my formatting, I would like to select a specific section of text between two keywords. This section can change in length so using fixed line ranges will not work. I have therefore found the following code which works perfectly within Word. My problem again is applying it from VBA within Excel.

Firstly the code working within Word:

And now my Excel adaptation:

The error returned is "Argument not optional" and has me baffled once again...

Please let me know what are you trying to achieve here? This is a separate question compared to your thread about getting data from Excel into Word. Look at this link to a resources site to see if it helps you with your goals.

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeword.htm

 

[japers]

Thanks Trevor, it is indeed a separate question but as it was linked and partially based on your code suggestion I thought I could post it here. But you are right, I will start a new question on the forum for it, my apologies and thanks again, you have saved my working week with your incredibly prompt solutions!