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]

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

 

[ulster0709]

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]

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]

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]

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

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]

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]

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]

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

 

[Macropod]

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