#1
|
|||
|
|||
programmatically inserting hidden text into a Word 2010 table
I have a Word 2010 table with existing, visible text in it. I want to use some VBA code to insert some text into each cell and then hide the new text. I know how to insert text into a cell using VBA, I just can't figure out how to leave the existing text in the cell visible and only hide the new text. |
#2
|
||||
|
||||
Basically, you create a Range variable that points to where you want the text to go, insert the text, then set the Range's font property to hidden (better still, apply a 'hidden' character Style).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
So what is the simplest way to create a Range variable that points to where I want the text to go?
|
#4
|
||||
|
||||
That largely depends on the code you're now using. Can you attach a document to a post with a representative table, code & data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
programmatically inserting hidden text into a Word 2010 table
Attached is an example of what I'm trying to accomplish. The existing text in the table should remain visible, but the new text that gets inserted needs to be hidden.
|
#6
|
||||
|
||||
Being a docx file, your attachment has no code, so I can't see what needs to be done to the code to make it work for you. You'll need to either:
• copy the code into the body of the document; • post the code separately (using the code tags on the 'Go Advanced' tab); • attach you docm file (as a zip archive, as you can't attach them directly).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
programmatically inserting hidden text into a Word 2010 table
Code:
Sub Insert_R_C_into_every_cell() ndx = 1 For Each aTable In ActiveDocument.Tables ' no. of rows and columns in each table Rows = aTable.Rows.Count Cols = aTable.Columns.Count Dim rng As Range ' insert each cell value into the master table For r = 1 To Rows For c = 1 To Cols ' insert a cell ID into every cell cellvalue = "Cell_ID[" & r & ", " & c & "]" ActiveDocument.Tables(ndx).Cell(r, c).Range.InsertAfter cellvalue ActiveDocument.Tables(ndx).Cell(r, c).Range.Font.Hidden = True Next Next Exit For ndx = ndx + 1 Next aTable End Sub |
#8
|
||||
|
||||
Try the following:
Code:
Sub Insert_R_C_into_every_cell() Dim Rng As Range, aTable As Table, r As Long, c As Long, cellvalue As String For Each aTable In ActiveDocument.Tables With aTable ' insert each cell value into the master table For r = 1 To .Rows.Count For c = 1 To .Columns.Count ' insert a cell ID into every cell cellvalue = "Cell_ID[" & r & ", " & c & "]" Set Rng = .Cell(r, c).Range With Rng .End = .End - 1 .Collapse wdCollapseEnd .Text = cellvalue .Font.Hidden = True End With Next Next End With Next aTable End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Thanks! Your code does exactly what I am trying to accomplish. Very much appreciated.
|
#10
|
||||
|
||||
Do note that your basic code won't work on tables with cells that are merged.
For code to return cell addresses in any table, try: Code:
Sub Insert_R_C_into_every_cell() Dim Rng As Range, aTable As Table, TblCell As Cell, cellvalue As String For Each aTable In ActiveDocument.Tables For Each TblCell In aTable.Range.Cells With TblCell cellvalue = "Cell_ID[" & .RowIndex & ", " & .ColumnIndex & "]" Set Rng = .Range With Rng .End = .End - 1 .Collapse wdCollapseEnd .Text = cellvalue .Font.Hidden = True End With End With Next TblCell Next aTable End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Good point. The actual set of tables that we are going to be using do have some merged cells, so this is very helpful. Thanks again!
|
#12
|
|||
|
|||
programmatically inserting hidden text into a Word 2010 table
One more question on this topic. Now that I have a hidden cell ID in each cell, I want to have some new data that needs to replace the existing data in a particular cell. I wrote the following code to loop through the cells and replace the data in a given cell. However, it replaces both the visible and hidden text; I want the hidden text to remain as is.
Code:
Sub update_cell_data() NewCellValue = "test data" ' new value to insert into the cell below CellID = "Cell_ID[2, 2]" ' temporarily turn on hidden text ActiveDocument.ActiveWindow.View.ShowHiddenText = True Dim Rng As Range, aTable As Table, r As Long, c As Long, CellText As String, FoundText as Boolean For Each aTable In ActiveDocument.Tables With aTable For r = 1 To .Rows.Count For c = 1 To .Columns.Count CellText = .Cell(r, c).Range.Text FoundText = InStr(CellText, CellID) If FoundText Then MsgBox "here" .Cell(r, c).Range.Text = NewCellValue End If Next Next End With Next aTable ' turn off hidden text ActiveDocument.ActiveWindow.View.ShowHiddenText = False End Sub |
#13
|
|||
|
|||
programmatically inserting hidden text into a Word 2010 table
So I tried the code you sent yesterday on a portion of a "real" data table and it choked on a merged cell after it got past the first row. Any suggestions? See attached doc for table. Thanks in advance for your help.
Code:
Sub Insert_R_C_into_every_cell() Dim Rng As Range, aTable As Table, TblCell As Cell, cellvalue As String For Each aTable In ActiveDocument.Tables For Each TblCell In aTable.Range.Cells With TblCell cellvalue = "Cell_ID[" & .RowIndex & ", " & .ColumnIndex & "]" Set Rng = .Range With Rng .End = .End - 1 .Collapse wdCollapseEnd .Text = cellvalue .Font.Hidden = True End With End With Next TblCell Next aTable End Sub |
#14
|
||||
|
||||
The code I posted works fine for me with your document & tables. As for the second problem, try:
Code:
Sub update_cell_data() Dim Rng As Range, aTable As Table, TblCell As Cell, cellvalue As String, CellID As String cellvalue = "test data" ' new value to insert into the cell below CellID = "Cell_ID[2, 2]" ' temporarily turn on hidden text ActiveDocument.ActiveWindow.View.ShowHiddenText = True For Each aTable In ActiveDocument.Tables For Each TblCell In aTable.Range.Cells With TblCell Set Rng = .Range With Rng If InStr(.Text, CellID) > 0 Then .End = .Start + InStr(.Text, CellID) - 1 .Text = cellvalue .Font.Hidden = False Exit For End If End With End With Next TblCell Next aTable ' turn off hidden text ActiveDocument.ActiveWindow.View.ShowHiddenText = False End Sub Code:
Sub update_cell_data() Dim aTable As Table, cellvalue As String cellvalue = "test data" ' new value to insert into the cell below For Each aTable In ActiveDocument.Tables aTable.Cell(2, 2).Range.Text = cellvalue Next aTable End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
programmatically inserting hidden text into a Word 2010 table
I'll try the code in a little while, but the answer to your last question is that we have a large, existing report template (200+ pages) and some extensive analysis programs that produce a lot of statistical output. Last year, we manually cut and pasted the data into the report which was very labor intensive. This year, I'm trying to automate the process by outputting the data from the statistical programs into a simple, pipe-delimited file (see sample below) and then read this file in VBA and put the data directly into Word tables in the report template. We want each cell to have an ID # so that in case a new row gets inserted into a table in the future, none of the existing cell references would have to be changed.
Code:
TABLE 3.3.6.1.1 | cell_ID[2,3] | 50 TABLE 3.3.6.1.1 | cell_ID[2,4] | 102503.04 TABLE 3.3.6.1.1 | cell_ID[2,5] | 48.78 (36.2, 64.31) TABLE 3.3.6.1.1 | cell_ID[2,5] | 49.37 (36.59, 65.17) TABLE 3.3.6.1.1 | cell_ID[3,3] | 9 TABLE 3.3.6.1.1 | cell_ID[3,4] | 9623.88 TABLE 3.3.6.1.1 | cell_ID[3,5] | 93.52 (42.76, 177.53) TABLE 3.3.6.1.1 | cell_ID[3,5] | 91.76 (41.61, 175.16) TABLE 3.3.6.1.1 | cell_ID[4,3] | 0 TABLE 3.3.6.1.1 | cell_ID[4,4] | 67.81 TABLE 3.3.6.1.1 | cell_ID[4,5] | 0 (0, 5439.72) TABLE 3.3.6.1.1 | cell_ID[4,5] | 0 (0, 5439.72) TABLE 3.3.6.1.1 | cell_ID[5,3] | 0 TABLE 3.3.6.1.1 | cell_ID[5,4] | 185.13 TABLE 3.3.6.1.1 | cell_ID[5,5] | 0 (0, 1992.55) TABLE 3.3.6.1.1 | cell_ID[5,5] | 0 (0, 1992.55) TABLE 3.3.6.1.1 | cell_ID[6,3] | 0 TABLE 3.3.6.1.1 | cell_ID[6,4] | 12.10 TABLE 3.3.6.1.1 | cell_ID[6,5] | 0 (0, 30469.54) TABLE 3.3.6.1.1 | cell_ID[6,5] | 0 (0, 30469.54) TABLE 3.3.6.1.1 | cell_ID[7,3] | 94 TABLE 3.3.6.1.1 | cell_ID[7,4] | 232977.36 TABLE 3.3.6.1.1 | cell_ID[7,5] | 40.35 (32.6, 49.37) TABLE 3.3.6.1.1 | cell_ID[7,5] | 41.36 (32.72, 51.59) |
Tags |
vba, vba word, word table |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
getting rid of hidden text in MS word | allenglishboy | Word | 1 | 11-06-2012 05:48 PM |
Hidden text in table | Angel9520 | Word Tables | 1 | 05-02-2012 01:00 AM |
Hidden style applied over already-hidden text. | christie | Word | 1 | 08-17-2011 09:10 AM |
inserting a string of data into an MS Word table??? | matto | Word VBA | 0 | 07-16-2010 09:35 AM |
Programmatically get File Path for currently opened WORD document | franferns | Word | 1 | 11-26-2009 12:36 PM |