Control WORD from VBA EXCEL. ¿How can I erase data?

Hi all,

I am trying to input some data in word from excel. I want to know if there is any way to erase rows from a table in word.

thanks a lot!

Hi internacio,

Once you have identified where your cursor is located in your table :

Selection.SelectRow
Selection.Rows.Delete

HTH
Cheers
Carim

Thanks a lot! although still have problems!!

How can I identify from a whole document, lets say, the text "hello"??

thanks again!!!

It might be a bit easier to give you some direction if we knew a little bit
more about what you are trying to do. Is the text in the Word table you
want to import into Excel? Is it the row you want to delete? It might be
easier to deal with all of this after you have it in Excel. Or it might be
easier to use Word VBA to get the data like you want it, then push it into
an Excel workbook.

Ed

"internacio" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks a lot! although still have problems!!
>
> How can I identify from a whole document, lets say, the text "hello"??
>
> thanks again!!!
>
>
> --
> internacio
> ------------------------------------------------------------------------
> internacio's Profile:
http://www.excelforum.com/member.php...o&userid=30960
> View this thread: http://www.excelforum.com/showthread...hreadid=519727
>

Yes, an explanation may help.

I have some data in excel and I want to create a word document. I don´t know how many rows I would have to export since it can change depending of the selection.

So if I select "x" I would like to export to a table in excel:

Name 1 Adress 1 Telephone 1
Name 2 Adress 2 Telephone 2
Name 3 Adress 3 Telephone 3
Name 4 Adress 4 Telephone 4

but if I select something different I would like to have 10 rows instead of 4. I have created a template and defined a matrix inside it as follows:


{1,1} {1,2} {1,3}
{2,1} ................
........................
{15,1} {15,2} {15,3}

and what I am doing is using find and replace from VBA Excel to change this values to name, adress and telephone. If I have only 4 records in my list, the rest of the values to 15 I replace them with a blank space.

The problem is that I want to add some text after the table, and if I add only 1 row to it, the text below it its going to be far below (like if I had 15 rows). I want to be able to select some of the table and delete the rows I have not used.

Hope it helps a little bit more.

Need somebodys help....

Hello again,

To find the string "Hello" :

Selection.Find.ClearFormatting
Selection.Find.Text = "Hello"
Selection.Find.Execute

HTH
Cheers
Carim

> So if I select "x" I would like to export to a table in excel:
This is a bit confusing to me. I'm assuming you want to select something,
and your selection will determine which rows you export into Word as a
table?

How were you going to determine which rows? It almost sounded like you were
going to export everything, and then try to delete the rows that you didn't
want. Would it be easier to use a sort or filter in Excel to create a
collection of only the data you want to export? Or am I missing something
here?

Ed

"internacio" <[email protected]> wrote
in message news:[email protected]...
>
> Yes, an explanation may help.
>
> I have some data in excel and I want to create a word document. I don´t
> know how many rows I would have to export since it can change depending
> of the selection.
>
> So if I select "x" I would like to export to a table in excel:
>
> Name 1 Adress 1 Telephone 1
> Name 2 Adress 2 Telephone 2
> Name 3 Adress 3 Telephone 3
> Name 4 Adress 4 Telephone 4
>
> but if I select something different I would like to have 10 rows
> instead of 4. I have created a template and defined a matrix inside it
> as follows:
>
>
> {1,1} {1,2} {1,3}
> {2,1} ................
> .......................
> {15,1} {15,2} {15,3}
>
> and what I am doing is using find and replace from VBA Excel to change
> this values to name, adress and telephone. If I have only 4 records in
> my list, the rest of the values to 15 I replace them with a blank
> space.
>
> The problem is that I want to add some text after the table, and if I
> add only 1 row to it, the text below it its going to be far below (like
> if I had 15 rows). I want to be able to select some of the table and
> delete the rows I have not used.
>
> Hope it helps a little bit more.
>
>
> --
> internacio
> ------------------------------------------------------------------------
> internacio's Profile:
http://www.excelforum.com/member.php...o&userid=30960
> View this thread: http://www.excelforum.com/showthread...hreadid=519727
>

> So if I select "x" I would like to export to a table in excel:
This is a bit confusing to me. I'm assuming you want to select something,
and your selection will determine which rows you export into Word as a
table?

How were you going to determine which rows? It almost sounded like you were
going to export everything, and then try to delete the rows that you didn't
want. Would it be easier to use a sort or filter in Excel to create a
collection of only the data you want to export? Or am I missing something
here?

Ed

"internacio" <[email protected]> wrote
in message news:[email protected]...
>
> Yes, an explanation may help.
>
> I have some data in excel and I want to create a word document. I don´t
> know how many rows I would have to export since it can change depending
> of the selection.
>
> So if I select "x" I would like to export to a table in excel:
>
> Name 1 Adress 1 Telephone 1
> Name 2 Adress 2 Telephone 2
> Name 3 Adress 3 Telephone 3
> Name 4 Adress 4 Telephone 4
>
> but if I select something different I would like to have 10 rows
> instead of 4. I have created a template and defined a matrix inside it
> as follows:
>
>
> {1,1} {1,2} {1,3}
> {2,1} ................
> .......................
> {15,1} {15,2} {15,3}
>
> and what I am doing is using find and replace from VBA Excel to change
> this values to name, adress and telephone. If I have only 4 records in
> my list, the rest of the values to 15 I replace them with a blank
> space.
>
> The problem is that I want to add some text after the table, and if I
> add only 1 row to it, the text below it its going to be far below (like
> if I had 15 rows). I want to be able to select some of the table and
> delete the rows I have not used.
>
> Hope it helps a little bit more.
>
>
> --
> internacio
> ------------------------------------------------------------------------
> internacio's Profile:
http://www.excelforum.com/member.php...o&userid=30960
> View this thread: http://www.excelforum.com/showthread...hreadid=519727
>

Hi ed, thanks for your attention.

I have in excel 100 rows of data containing 3 columns each (name, adress and phone).

Lets say I want to export to excel only the records that are from the USA, that are only 10. I have no problem on filtering them in excel. The problem comes on word.

I have created a table in the template so the data can be seen as follows

1 John USA
2 Mary USA
3 James USA
4 Gil USA

and because in the template I don´t know how many rows I will like to import, I have established a maximun of 15, so if I fill less the rest I replace them with a blank space.

That blank space is the one I want to delete.

I think i have found something that migth help:

ActiveDocument.Tables(1).Rows(i).Delete

lets try!

Finally it works!!

With the table in word i execute this sentece as many times as rows I dont need. (i is the first row I has not changed)

ActiveDocument.Tables(1).Rows(i).Delete


Thanks a lot for all the help given, I really apreciate it.

Sorry I couldn't get back to you sooner. I'm glad you found a solution.
What I use is a Word macro that takes data copied from Excel and creates a
Word table with the same basic formatting (as opposed to pasting the Excel
data into an empty table that it won't fit into.) To use, filter your
range in Excel and copy, then open Word and run this.

Cheers!
Ed

Sub PasteTable()
'
Dim MyTable As Table
Dim strStyle As String, szFont As Long
strStyle = Selection.Style
szFont = Selection.Font.Size

Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
wdInLine, DisplayAsIcon:=False

Set MyTable = Selection.Tables(1)
With MyTable
.Shading.Texture = wdTextureNone
.Shading.ForegroundPatternColor = wdColorAutomatic
.Shading.BackgroundPatternColor = wdColorAutomatic
.Range.Font.Color = wdColorBlack
.Range.Style = strStyle
.Range.Font.Size = szFont
With .Borders
.InsideLineStyle = wdLineStyleSingle
.InsideLineWidth = wdLineWidth050pt
.OutsideLineStyle = wdLineStyleSingle
.OutsideLineWidth = wdLineWidth050pt
End With
End With

MyTable.Select
WordBasic.TableRowHeight RulerStyle:=0, LeftIndent:="0", Alignment:=0, _
AllowRowSplit:=1, TableDir:=0
Selection.Cells.AutoFit
WordBasic.TableRowHeight RulerStyle:=0, LeftIndent:="0", Alignment:=1, _
AllowRowSplit:=1, TableDir:=0
WordBasic.TableRowHeight RulerStyle:=0, LineSpacingRule:=0, LeftIndent:=
_
"0", Alignment:=1, AllowRowSplit:=1, TableDir:=0

End Sub
"internacio" <[email protected]> wrote
in message news:[email protected]...
>
> Finally it works!!
>
> With the table in word i execute this sentece as many times as rows I
> dont need. (i is the first row I has not changed)
>
> ActiveDocument.Tables(1).Rows(i).Delete
>
>
> Thanks a lot for all the help given, I really apreciate it.
>
>
> --
> internacio
> ------------------------------------------------------------------------
> internacio's Profile:
http://www.excelforum.com/member.php...o&userid=30960
> View this thread: http://www.excelforum.com/showthread...hreadid=519727
>