Deleting/removing content on Word table using Excel VBA (not Word VBA)

[ulster0709]

Hi Experts,


I have just learned vba few months ago and I have a problem that I havent been able to solve, could you help me or give me some advice please.


I am using VBA excel to copy a table from excel into a table on word (the table on word has invisible border and it is just for layout/structure purposes and facilitate navigation when using excel vba), however, before I paste the table, how can I delete/clear the content that was already present on the word table using excel vba? Below is the code where changes needed to be made.

WDApp.Activate
With WDApp
WDdoc.Tables(1).Rows(5).Select
'WDdoc.tables(1).Rows(5).Clearcontents <- it didnt work
'WDdoc.tables(1).Rows(5).delete <- it worked, but I dont know how to add a row on word table, I need this particualr row becuase it is the row to which the table from excel will be pasted
End With

Thank you

 

[yky]

That's alright. However, same thing happened again, 'variable not defined' for wdAlignParagraphCenter D:

The following is the actual code I tested on my computer. It worked, meaning the row 5 in "Document1" was center-aligned once the code was executed (was left-aligned before).

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error Resume Next

Set wdApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If

On Error GoTo 0

Set wdDoc = wdApp.Documents("Document1")
wdDoc.Tables(1).Rows(5).Select
wdApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

 

[Macropod]

The following is the actual code I tested on my computer. It worked, meaning the row 5 in "Document1" was center-aligned once the code was executed (was left-aligned before).

Your code would only work if you had set a reference to Word (via Tools|References) before running it. In other words, for use with early binding. However, the fact the OP got an error with both Dim wdCell As Word.Cell and wdAlignParagraphCenter shows he is using late binding. Your code will not work with late binding. To make it work with late binding, you would need to change wdAlignParagraphCenter to 1 or declare a wdAlignParagraphCenter variable using code like:
Const wdAlignParagraphCenter as Long = 1

There also seems little point in having:

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

as none of that code has any bearing on the processing.

 

[ulster0709]

[COLOR=#333333]Dim wdCell As Object[/COLOR]Const wdAlignRowCenter = 1
With WDApp ' I have splited this line of code into two, as it doesnt work together
With WDdoc.Tables(1)
  For Each wdCell In .Rows(5).Range.Cells
    wdCell.Range.Text = ""
  Next
  .Rows.Alignment = wdAlignRowCenter
  .Rows.LeftIndent = 0
End With
End With

However somewhat it deletes the table instead?:S

 

[ulster0709]

Thank you. The text in row 5 was center-aligned, however, the problem is that the table within row 5 is still left aligned. Not sure whether I explained it clearly:S

 

[ulster0709]

Below is the problem that I would like to solve. It is the table in row 5 that I would like to centre aligned, and I would like to use excel vba to execute the operation.

Problem:

[TABLE="class: grid, width: 1000"]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: outer_border, width: 500"]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[/TABLE]


Expected result after executing the code:

[TABLE="class: grid, width: 1000"]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: outer_border, width: 500, align: center"]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[/TABLE]


FYI, the table copied from excel is not in table format (if you do Ctrl + T, it will convert a range into a table format) , it is just a range.

Thank you

 

[Macropod]

[COLOR=#333333]Dim wdCell As Object[/COLOR]Const wdAlignRowCenter = 1
With WDApp ' I have splited this line of code into two, as it doesnt work together
With WDdoc.Tables(1)
  For Each wdCell In .Rows(5).Range.Cells
    wdCell.Range.Text = ""
  Next
  .Rows.Alignment = wdAlignRowCenter
  .Rows.LeftIndent = 0
End With
End With

However somewhat it deletes the table instead?:S

The code, as posted by me, works fine with late binding. it is impossible for that code to delete a table; it can't even delete a cell...

As for your alignment problems, it would help if you said what it is you want to centre - the table or the content within one or more cells. It would also help if you posted the code containing all of your Word variable declarations and their implementations. As it is we're left guessing...

PS: Notwithstanding your thread heading, the code doing all the hard lifting is Word VBA, not Excel VBA.

 

[ulster0709]

The code, as posted by me, works fine with late binding. it is impossible for that code to delete a table; it can't even delete a cell...

As for your alignment problems, it would help if you said what it is you want to centre - the table or the content within one or more cells. It would also help if you posted the code containing all of your Word variable declarations and their implementations. As it is we're left guessing...

PS: Notwithstanding your thread heading, the code doing all the hard lifting is Word VBA, not Excel VBA.

Thank you for your reply.

I have got the VBA code for copying and pasting the tables from Excel to Word (basically, it is something that we would normally do: copying a range of cells from Excel and paste it on Word, and then center align it on Word) , however, instead of center-aligned the table using word, I would like to use Excel VBA to carry out this particular action. Below is the code I have got so far.

Option Explicit


Sub word_report()


Dim WDApp As Object
Dim WDdoc As Object
Dim forecast, history As Range
Dim table1, table2 As Worksheet
Dim NewRow As Range




Set tbl1 = Worksheets("table1")
Set tbl2 = Worksheets("tabl2")
Set WDApp = GetObject(, "Word.Application")
Set WDdoc = WDApp.ActiveDocument




Const fee_tbl_row As Integer = 5
Const fee_forecast_tbl_row As Integer = 4
Application.ScreenUpdating = False

'Clear all the previous tables on word
WDApp.Activate
With WDApp


WDdoc.Tables(1).Rows(4).Delete
WDdoc.Tables(1).Rows(4).Delete


WDdoc.Tables(1).Rows.Add(BeforeRow:=WDdoc.Tables(1).Rows(4)).Select
WDdoc.Tables(1).Rows.Add(BeforeRow:=WDdoc.Tables(1).Rows(5).Select
End With



' Copying and pasting tables from Excel to Word
tbl1.Activate
With tbl1
.Range("output_tbl1").Select
Selection.Copy
WDApp.Activate
With WDApp
WDdoc.Tables(1).Rows(4).Select
.Selection.PasteExcelTable True, False, False
End With
End With




tbl2.Activate
With tbl2
.Range("output_tbl2").Select
Selection.Copy
WDApp.Activate
With WDApp
WDdoc.Tables(1).Rows(5).Select
.Selection.PasteExcelTable True, False, False
End With
End With


Application.ScreenUpdating = True




End Sub

 

[ulster0709]

I wanted to upload a word doc to demonstrate what I am looking for, however, it seems that I am not allowed to enclose any document.
See # 15

The table in row 6 under problem should have been in row 5, sorry for the confusion.

 

[Macropod]

I have got the VBA code for copying and pasting the tables from Excel to Word (basically, it is something that we would normally do: copying a range of cells from Excel and paste it on Word, and then center align it on Word) , however, instead of center-aligned the table using word, I would like to use Excel VBA to carry out this particular action.

You are not paying attention: Excel VBA cannot do anything with a Word document. Regardless of the fact you might be running the macro from Excel, the VBA that's doing the Word document manipulation is Word VBA.

I also see no evidence that you've implemented the code I've provided.

 

[ulster0709]

Sorry, I didnt quite get it. Does that mean the code I provided in #17 is actually ran by Word VBA not excel VBA, even though it was ran on Excel?

Because what I am trying to prevent is to use Word VBA, I wanted to do everything on Excel VBA if possible.