Solved

VBA Word Table put a string into multiple cells in a table

Posted on 2011-03-06
11
960 Views
Last Modified: 2012-05-11
I am working with Word (2010) and have a table with either a selection or a range that includes multiple cells. I want to put a string into each of the selected cells (which will wipe out the contents of those cells). Can I do that with one command or do I have to locate to each cell to paste in the string.
     As always, many thanks for the help.

     John Robin (Allen)
     Priddis, Alberta, Canada
0
Comment
Question by:JohnRobinAllen
  • 6
  • 4
11 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35048032
You can copy and paste contiguous cells in a row or in a column and paste then into another table.

If you have a string and select a number pf columns, Word will split the string into words and will put each word into each column. If more than one row is selected, the word in each cell will be repeated in all the selected cells in the same column.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35048055
You can do it in VBA, e.g.

Activedocument.Tables(1).Rows(1).Range.Paste
0
 

Author Comment

by:JohnRobinAllen
ID: 35048128
My problem is that I do not have the string in memory, and it does not appear in the table such that I could copy it into memory.
     Even if I somehow got the string into memory, would "Activedocument.Tables(1).Rows(1).Range.Paste" be able to refer to and paste into a contiguous string of cells?

     Background information: I have a table of vocabulary words and translations with a new word in each row. A user will select a contiguous section of rows to study. I then want to hide all the non-selected rows above and below the desired selection of rows. The table has a general purpose column for sorting, so I want to put an "X" into each cell in that column in rows the user wants to ignore for the moment. I can then sort the table on that column to put the undesired cells at the bottom, and then I'll delete the "X"s and hide those rows.
    I have to have these rows at the bottom of the table because each row in the table has an automatically-generated sequence number. If there are hidden rows above the unhidden rows, the numbering will be out of sequence and will start with the first number after any hidden rows above.
    A possible work-around would be to hide rows above and below the originally selected rows and then restart the numbering on the visible rows. If the visible rows were then sorted (as the program repeatedly does), then would I not have also to program a new starting number after each sort? I think my sorting solution for hiding rows is the cleaner and more elegant solution, but someone with more experience than I may think differently.

    Thanks for your help. I hope we can solve this problem.

    --j.r.
0
 
LVL 1

Accepted Solution

by:
hirstmg earned 250 total points
ID: 35049201
Hi John
if it is a simple table something like this seems to work. When you select a few cells in a table over multiple rows the selection on screen looks right but the selection range of cells in vba includes the ones in the outer and inner columns you haven't selected. So finding the first and last cell and getting their column value and then only updating the cells within those columns ...

Sub UpdateSelectedCells()
    Call TableCellUpdater("fred")
End Sub

Sub TableCellUpdater(str As String)
'Works for a simple table
    Dim cell As cell
    Dim cells As cells
    Set cells = Selection.Range.cells
    Dim cFirst, cLast As Integer
   
    cFirst = Selection.Range.cells(1).Column.Index
    cLast = cells(cells.Count).Column.Index
    For Each cell In cells
        If (cell.Column.Index >= cFirst) And (cell.Column.Index <= cLast) Then _
        cell.Range.Text = str
    Next
   
End Sub
Cheers
Michael
http://www.clockworksoftware.com.au
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 250 total points
ID: 35049652
It's not entirely clear how the question relates to your requirement.

I would be inclined to cut each x row and to paste them into a separate table. It can be a bit difficult to accurately paste rows at the end of a table.


Sub MoveRows()
    Dim rw As Row
    Dim tbl As Table
    Dim rng As Range
    Dim tmptable As Table
    
    Set tbl = ActiveDocument.Tables(1)
    Set rng = tbl.Range
    rng.Collapse wdCollapseEnd
    rng.InsertParagraphAfter
    rng.Collapse wdCollapseEnd
    Set bmk = ActiveDocument.Bookmarks.Add("XXXX", rng)
    For Each rw In tbl.Rows
        If Left(rw.Cells(1).Range.Text, 1) = "x" Then
            rw.Range.Cut
            Set rng = ActiveDocument.Bookmarks("XXXX").Range
            rng.Collapse wdCollapseEnd
            rng.Paste
        Set tmptable = rng.Tables(1)
        End If
    Next rw
    tmptable.Range.Font.Hidden = True
End Sub

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:JohnRobinAllen
ID: 35059152
Both solutions helped. I will be commenting on them further in about an hour.
        j.r.a.
Sub HideColumnsNotSelected()
'   If user has selected cells in more than one row in a table, this sub hides the other rows so that the user can work with
'   what she selected. The table has visible data in the first three columns; the remaining columns should remain hidden
Dim Msg As String
Dim i As Integer
Dim StartRow As Integer
Dim EndRow As Integer
Dim LastRow As Integer
Dim tbl As Table

    '   Set some variables
    Set tbl = ActiveDocument.Tables(1)
    LastRow = tbl.Rows.Count
    StartRow = Selection.Information(wdStartOfRangeRowNumber)
    EndRow = Selection.Information(wdEndOfRangeRowNumber)
    
    '   Check that the selection is inside a table
    If Selection.Information(wdWithInTable) = False Then
        Exit Sub
    End If
        
    If EndRow - StartRow <= 0 Then   '   Selection is in a table. Are at least two rows selected?
        Msg = "Nothing selected. Nothing changed"
        MsgBox Msg, vbOKOnly
        Exit Sub
    Else
        '   User has selected cells in at least two rows, so we can get to work.
        Application.ScreenUpdating = False   '   Hide the temporary changes we are about to make
        tbl.Select                                        '   Unhide all hidden rows and columns
        Selection.Font.Hidden = False           '   Now everything in the table is visible, but we do not want cols 4-8 visible.
                
        '   Hide columns 4 to 8. They contain numbers used in sorting but not needed now
        ActiveDocument.Tables(1).Columns(4).Select
        Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend
        Selection.Font.Hidden = True
        
        'Delete whatever may be in column 9
        ActiveDocument.Tables(1).Columns(9).Select
        Selection.Delete Unit:=wdCharacter, Count:=1
        
        '   Mark then ninth cell in each row that is not in what the user had selected
        With ActiveDocument
                For i = 1 To StartRow - 1
                    tbl.cell(i, 9).Range.Text = "X"
                Next i
                For i = EndRow + 1 To LastRow
                    tbl.cell(i, 9).Range.Text = "X"
                Next i
        End With
        
        '   The following sort moves all the unwanted rows to the bottom
        Selection.Sort ExcludeHeader:=False, _
            FieldNumber:="Column 9", SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending
        
        ActiveDocument.Tables(1).Columns(9).Select      '   We don't need the info in column 9 anymore.
        Selection.Delete Unit:=wdCharacter, Count:=1    '   Info deleted
        ActiveDocument.Tables(1).Columns(9).Select      '   Next, hide the column
        Selection.Font.Hidden = True                            '   Column invisible
        tbl.Rows(EndRow - StartRow + 2).Select           '   Calculate location of the first unwanted row, then select it
        Selection.EndKey Unit:=wdStory, Extend:=wdExtend      '   Extend selection to the bottom row
        Selection.Font.Hidden = True                                        '   Hide those unwanted rows.
    End If
End Sub

Open in new window

0
 

Author Comment

by:JohnRobinAllen
ID: 35059162
    Hirstmg’s comments are valuable in that they show how tables work. One simply cannot do what I wanted to do: paste the same text into a certain column when the rows are selected. I had to revise his code a bit to ensure that my string would be pasted in column 9 in my table:
   
For Each cell In cells
        If (cell.Column.Index = 9) Then cell.Range.Text = str
    Next
Since one has to do that with, say, 500 rows with 10 columns, that means the computer has to make up to 5,000 checks to insert 500 “X” marks in column 9. That being the case, it seems wiser to go through the table by one row rather than one cell at a time.
I have attached some revised code based on Hirstmg’s suggestion. It works with the document “Sample vocab data.doc,” a subset of a much larger table with around 600 rows. If a user selects cells in at least two rows, the code then moves the unmarked cells to the bottom of the table where it hides them.
The data document contains a table with the three left columns visible, and the remaining columns are invisible. (Those hidden columns contain information for sorting the data alphabetically, in sequence order, and so forth.) We use the last column for sorting the table in the attached code.
It may well turn out that Graham Skan’s solution is a better course of action. He suggested that instead of moving and hiding the unwanted rows, one can just expand the user’s selected cells to include entire rows, and then one can copy those rows into another document for the user. The advantage here is that one does not then have to “clean up” the original document to restore it to its original state.
I intend to implement both systems in my program and then let the user choose whether to create a new document à la mode de Graham or use the current document with rows hidden à la mode de Hirstmg. I am therefore increasing the value of this question from 250 to 500 so that I can assign 250 points to the author of each solution.

0
 

Author Comment

by:JohnRobinAllen
ID: 35059189
Two comments above contain the code I used with the data below. My thoughts are in the previous comment.
      Points awarded as described above.
      Many thanks!
          --j.r.a.
Sample-vocab-data.doc
0
 

Author Closing Comment

by:JohnRobinAllen
ID: 35059205
My comments are above. Both solutions helped with the problem.

Both deserve credit
   --j.r.a.
0
 

Author Comment

by:JohnRobinAllen
ID: 37054292
I apologize for opening this question again. If anyone is interested, there is a much simpler solution that I am currently using to fill adjacent cells in a table column. The following code puts a string (here “i”) in a column (here column 6 of the first table in the document) in cells from the integer x to the integer y. The code assumes that i, x, and y have been declared as integers:

Dim Sortcol As Column
Set Sortcol = ActiveDocument.Tables(1).Columns(6)
For i = x To y
    Sortcol.Cells(i).Range.Text = "i"
Next i

JRA
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37054434
Thanks JRA. It looks as if we were over-interpreting the question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now