Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Convert Text to Table in Word from Excel (VBA)

This is a discussion on Convert Text to Table in Word from Excel (VBA) within the Excel Questions forums, part of the Question Forums category; My second question of the day! I am succesfully copying text from a Excel into a new document in Word ...

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    18

    Default Convert Text to Table in Word from Excel (VBA)

    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:

    Code:
    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!

  2. #2
    New Member
    Join Date
    Dec 2010
    Posts
    18

    Default Re: Convert Text to Table in Word from Excel (VBA)

    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:

    Code:
    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?

  3. #3
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Convert Text to Table in Word from Excel (VBA)

    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
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  4. #4
    New Member
    Join Date
    Dec 2010
    Posts
    18

    Default Re: Convert Text to Table in Word from Excel (VBA)

    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:

    Code:
    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?

  5. #5
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Convert Text to Table in Word from Excel (VBA)

    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
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  6. #6
    New Member
    Join Date
    Dec 2010
    Posts
    18

    Default Re: Convert Text to Table in Word from Excel (VBA)

    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:

    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?

  7. #7
    New Member
    Join Date
    Dec 2010
    Posts
    18

    Default Re: Convert Text to Table in Word from Excel (VBA)

    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...

  8. #8
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Convert Text to Table in Word from Excel (VBA)

    Quote Originally Posted by japers View Post
    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:

    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
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  9. #9
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Convert Text to Table in Word from Excel (VBA)

    Quote Originally Posted by japers View Post
    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...officeword.htm
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  10. #10
    New Member
    Join Date
    Dec 2010
    Posts
    18

    Default Re: Convert Text to Table in Word from Excel (VBA)

    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!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com