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



## ulster0709 (Aug 14, 2016)

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 (Aug 14, 2016)

```
Dim NewRow As Row
Set NewRow = wDdoc.tables(1).Rows.Add(BeforeRow:=wDdoc.tables(1).Rows(5))
```


----------



## ulster0709 (Aug 14, 2016)

Thank you! It worked! 

I have got three more questions if you don't mind 

1. How can I align the tables center on words using excel vba ?
2. how do I copy a chart from Excel and paste it into a place holder on PowerPoint using a excel vba? The code below works fine for copy and paste the chart from Excel to powerpoint, but it doesnt paste it into the placeholder, I am just wondering how should the code be amended in order to paste it into a placeholder. 


```
Option Explicit

Sub Marcro3()
Dim PPT As Object
Set PPT = CreateObject("PowerPoint.Application")
PPT.Visible = True
PPT.Presentations.Open Filename:="path"
copy_chart "report", 3, "Chart 1"  ' Name of the sheet to copy graph and slide number the graph is to be pasted in
'PPT.Save
'PPT.Close

End Sub

Public Function copy_chart(sheet, slide, name)


Dim PPApp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim nPlcHolder As Long

Set PPApp = CreateObject("Powerpoint.Application")
Set PPApp = GetObject(, "Powerpoint.Application")
Set PPPres = PPApp.ActivePresentation



PPApp.ActiveWindow.View.GotoSlide (slide)


    Worksheets(sheet).Activate
    ActiveSheet.ChartObjects(name).Chart.CopyPicture _
    Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture



Set PPSlide = PPPres.slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)


With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select

End With

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Function
```

Below is more information about the problem

 Excel
 Spreadsheet name: Report generator
 Spreadsheet location: (Already activate)
 Worksheet: Summary
 Chart name: Chart 1

 Powerpoint
 Powerpoint name: report
 powerpoint locationL (already activate)
 Slide: P.3
 Placeholder position: the one of the left, I am not sure whether there is a name for the placeholders on powerpoint (there are two placeholders on P.3, one on the right, the other one on the left) 


3. What book would you recommend me to read if I want to know more about using excel vba to copy and paste stuff on word/powerpoint? Because I have been using macro recorder and reading a vba book to help, however the book doesn't include anything related to word/powerpoint.

Much appreciate and thank you!


----------



## yky (Aug 15, 2016)

I'm afraid I cannot be of much help. It has been at least ten years since the last time I wrote a script for Excel to interact with Word. I don't have any MS books. If I don't know how to do something, I search Google. Ninety percent of the time, I can get answers to my question. If I cannot find an answer, I come here and ask. Often times, I need to go back to my old codes to find out how did I do this, how did I do that. If you get to my age, you'll know what this is like.

To center a table, try this (found by Google):

Selection.Tables(1).Rows.Alignment = wdAlignRowCenter


----------



## ulster0709 (Aug 23, 2016)

yky said:


> I'm afraid I cannot be of much help. It has been at least ten years since the last time I wrote a script for Excel to interact with Word. I don't have any MS books. If I don't know how to do something, I search Google. Ninety percent of the time, I can get answers to my question. If I cannot find an answer, I come here and ask. Often times, I need to go back to my old codes to find out how did I do this, how did I do that. If you get to my age, you'll know what this is like.
> 
> To center a table, try this (found by Google):
> 
> Selection.Tables(1).Rows.Alignment = wdAlignRowCenter









Thank you for your advice, google is always our best friend 
By the way I think the code doesnt apply to excel vba; it gives an error: variable not defined. I have lookup google to find excel vba code to center the table on word, but I still havent been able to found it D: 

Thanks


----------



## Macropod (Aug 24, 2016)

ulster0709 said:


> 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
> ...


You could use:

```
Dim wdCell As Word.Cell
With WDApp.WDdoc.Tables(1)
  For Each wdCell In .Rows(5).Range.Cells
    wdCell.Range.Text = ""
  Next
End With
```
There is no need to activate Word, to select anything, to delete or reinsert the row.
As for the alignment, assuming it's the table you want to centre on the page, you could do that with:

```
Dim wdCell As Word.Cell
With WDApp.WDdoc.Tables(1)
  For Each wdCell In .Rows(5).Range.Cells
    wdCell.Range.Text = ""
  Next
  .Rows.Alignment = wdAlignRowCenter
  .Rows.LeftIndent = 0
End With
```
I suggest you start a separate thread for the PowerPoint question.

I can't recommend any particular books on VBA programming, _per se_. That said, a book you might find useful is The Secret Life of Word: A Professional Writer's Guide to Microsoft Word Automation, by R Delwood, published by XML Press in 2011(The Secret Life of Word « XML Press). I contributed content for and did much of the technical review of this book. This isn't a programming book as such (though it does have some programming in it) and doesn't profess to teach you how to program. Rather, it shows how to combine Word's various tools to achieve whatever the desired result might be. Another book I contributed to (and has much more programming in it) is Word Hacks, by A Savikas, published by O'Reilly Media in 2005 (Word Hacks - O'Reilly Media). I contributed content for this book also. Although it pre-dates Office 2007, much of the content is still relevant.


----------



## yky (Aug 25, 2016)

ulster0709 said:


> Thank you for your advice, google is always our best friend
> By the way I think the code doesnt apply to excel vba; it gives an error: variable not defined. I have lookup google to find excel vba code to center the table on word, but I still havent been able to found it D: Thanks


Sorry for the late reply. I have been extremely busy lately.

```
wdDoc.Tables(1).Rows(5).Select
wdApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
```


----------



## ulster0709 (Aug 25, 2016)

Thank you for your reply. I will have a look of the books you recommended. 

However, I am wondering can the code used in Excel VBA? Because 'Dim wdCell As Word.Cell' doesnt work, it gives an error message: User defined type not define.


----------



## ulster0709 (Aug 25, 2016)

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


----------



## Macropod (Aug 25, 2016)

That suggests you're using late binding. In that case, try:

```
Dim wdCell As Object
Const wdAlignRowCenter = 1
With WDApp.WDdoc.Tables(1)
  For Each wdCell In .Rows(5).Range.Cells
    wdCell.Range.Text = ""
  Next
  .Rows.Alignment = wdAlignRowCenter
  .Rows.LeftIndent = 0
End With
```


----------



## ulster0709 (Aug 14, 2016)

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 (Aug 26, 2016)

ulster0709 said:


> 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 (Aug 26, 2016)

yky said:


> 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 (Aug 31, 2016)

```
[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 (Aug 31, 2016)

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 (Aug 31, 2016)

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:







Expected result after executing the code: 







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 (Sep 1, 2016)

ulster0709 said:


> ```
> [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)
> ...


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 (Sep 1, 2016)

Macropod said:


> 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 (Sep 1, 2016)

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 (Sep 2, 2016)

ulster0709 said:


> 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 (Sep 3, 2016)

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.


----------



## ulster0709 (Aug 14, 2016)

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


----------



## Macropod (Sep 3, 2016)

Everything in your code in post #17, from 'With WDApp' onwards is _Word _VBA.


----------



## ulster0709 (Sep 7, 2016)

Thanks for clarifying. Does that mean Word VBA would also work on Excel? 

If you were me, how would you adjust the code in #17 so that the table pasted would be in the centre on Word? 

Thanks


----------



## Macropod (Sep 7, 2016)

ulster0709 said:


> Does that mean Word VBA would also work on Excel?


If you're asking whether you can run Excel VBA code from Word, then answer is yes.


ulster0709 said:


> If you were me, how would you adjust the code in #17 so that the table pasted would be in the centre on Word?


I already posted code to do that way back in post #6.


----------

