|
|
Thread Tools | Display Modes |
#1
|
||||
|
||||
How to find number of coma and then add that number of rows in word using macro?
I have this table
What I have done is recorde macro and it look like this. Selection.MoveRight Unit:=wdCell Selection.EndKey Unit:=wdLine Selection.TypeText Text:="@" Selection.MoveRight Unit:=wdCell Selection.Copy Selection.MoveLeft Unit:=wdCell Selection.ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=1, _ NumRows:=9, AutoFitBehavior:=wdAutoFitFixed With Selection.Tables(1) .Style = "Table Grid" .ApplyStyleHeadingRows = True .ApplyStyleLastRow = False .ApplyStyleFirstColumn = True .ApplyStyleLastColumn = False End With Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend Selection.Cut Selection.MoveLeft Unit:=wdCell Selection.EndKey Unit:=wdLine Selection.TypeParagraph Selection.PasteAndFormat (wdFormatPlainText) Selection.TypeBackspace Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.InsertRowsBelow 1 Selection.MoveUp Unit:=wdLine, Count:=29 Selection.PasteAndFormat (wdTableOverwriteCells) Selection.MoveRight Unit:=wdCell Selection.MoveDown Unit:=wdLine, Count:=29 Selection.MoveRight Unit:=wdCell Selection.MoveRight Unit:=wdCell My question is is there a short method to count the comma separated number of entries and then add that number of new row and then paste the data accordingly. what I currently do is add some 3O row and then paste the data using paste special merge the entry and then removing the empty row using macro. |
#2
|
|||
|
|||
Code:
Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey Dim lngStart As Long Dim lngIndex As Long Dim lngCount As Long Dim arrList() As String Dim oTbl As Table Dim oRow As Row, oNewRow As Row lngStart = 1 Set oTbl = ActiveDocument.Tables(1) Do On Error GoTo lbl_Exit Set oRow = oTbl.Rows(lngStart) lngStart = lngStart + 1 oRow.Select 'Your text in column 2 is a comma separtated list. You can create an array from it arrList = Split(Left(oRow.Range.Cells(2).Range.Text, _ Len(oRow.Range.Cells(2).Range.Text) - 2), ",") lngCount = UBound(arrList) + 1 For lngIndex = 1 To lngCount lngStart = lngStart + 1 If lngIndex <> lngCount Then oRow.Cells(1).Range.InsertAfter arrList(lngIndex - 1) & vbCr Else oRow.Cells(1).Range.InsertAfter arrList(lngIndex - 1) End If If lngIndex = 1 Then Set oNewRow = oTbl.Rows.Add(oRow.Next) Else Set oNewRow = oTbl.Rows.Add(oNewRow.Next) End If oNewRow.Cells(1).Range.Text = arrList(lngIndex - 1) Next lngIndex Loop lbl_Exit: Exit Sub End Sub |
#3
|
||||
|
||||
I Have searched for days and night to find answer to this question I Gave up. Then One day while searching on internet I found the forum. Earlier I was not sure that I will get the answer. But no matter who I am and where I am from, I am getting Answers. Not I am at some position that I can answer or at least direct people at right place. Thanks to people like you Mr. Maxey who give the answers of silly questions by People Like Me.
Thanks Mr. Maxey |
#4
|
||||
|
||||
Editing The Macro.
Quote:
I am getting something like this. |
#5
|
|||
|
|||
Try modifying as shown:
Code:
oRow.Cells(1).Range.InsertAfter vbCr & arrList(lngIndex - 1) Else oRow.Cells(1).Range.InsertAfter vbCr & arrList(lngIndex - 1) End If |
#6
|
||||
|
||||
Editing Macro.
Dear Sir,
I wanted to modify macro by editing it to make some correction I neglected while asking thinking that I will figure it out later. I want to convert table as follow to The word file containing both information is as follow. https://docs.google.com/viewer?a=v&p...I2NTkwMjA0YWE3 |
#7
|
|||
|
|||
Just change the cell referenences:
Code:
Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey Dim lngStart As Long Dim lngIndex As Long Dim lngCount As Long Dim arrList() As String Dim oTbl As Table Dim oRow As Row, oNewRow As Row lngStart = 1 Set oTbl = ActiveDocument.Tables(1) Do On Error GoTo lbl_Exit Set oRow = oTbl.Rows(lngStart) lngStart = lngStart + 1 oRow.Select 'Your text in column 2 is a comma separtated list. You can create an array from it arrList = Split(Left(oRow.Range.Cells(3).Range.Text, _ Len(oRow.Range.Cells(3).Range.Text) - 2), ",") lngCount = UBound(arrList) + 1 For lngIndex = 1 To lngCount lngStart = lngStart + 1 If lngIndex <> lngCount Then oRow.Cells(2).Range.InsertAfter vbCr & arrList(lngIndex - 1) Else oRow.Cells(2).Range.InsertAfter vbCr & arrList(lngIndex - 1) End If If lngIndex = 1 Then Set oNewRow = oTbl.Rows.Add(oRow.Next) Else Set oNewRow = oTbl.Rows.Add(oNewRow.Next) End If oNewRow.Cells(2).Range.Text = arrList(lngIndex - 1) Next lngIndex Loop lbl_Exit: Exit Sub End Sub |
#8
|
||||
|
||||
How to add one more field into the macro.
Sorry for asking silly questions as I am dummy trying to learn macro.
I want to add one more field, something that i want to copy from the specific source and paste it somewhere else. Questions are how to identify and index the source to copy. In my case the source is assignee of patents ie. athena neuroscience that is a paragraph away from lngStart. I want to copy the text and paste it to simultaneously after each cell of arraylist. I want also to remove paragraph mark from column IV marked blue. |
Tags |
macro vba word, microsoft word 2010, word table |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Global macro across a number of different word files | daffy | Word VBA | 6 | 07-08-2014 05:42 PM |
Creating a table for a variable number of rows | OllieOnline | Mail Merge | 1 | 03-27-2013 02:48 PM |
Using macro to add variable number of rows to a protected word table | Julia | Word Tables | 1 | 01-09-2013 06:04 AM |
Moving data from multiple rows to 1 row for each program number | ballj_35 | Excel | 3 | 08-01-2012 05:10 AM |
Fixing number or rows in a table | burnsie | Word | 2 | 07-12-2011 02:59 AM |