Excel to Word and delete certain tables

jon12355 · #1 10-15-2014, 06:04 AM

Hi

Looking for some help with my project, an excel multipage userform. I must state that I am very new to VBA and all the code I have got so far has been from Google searching.

The userform has a main page were users input basic details (name,date etc). There are then 10 identical multipage tabs which the user enters their objectives. Some users may have 1 objective, some may have 5,6 or 10 objectives. There is also a conclusion tab at the end. Once the form has been completed I have a command button which imports certain data into the excel spreadsheet and certain data goes into a word document using bookmarks for saving / printing. To my surprise I have got this all to work very well so far!

However, as stated above, if a user only has one objective then the word template looks ridiculous as the template needed to be created to hold up to 10 objectives.

What I would like to do if possible, is when the command button is clicked in excel and the word document is created, for the objective tables in the word document that are empty to be deleted.

The objectives start at table (8) in the word document and the cell which could be used to reference as a blank cell is Row 4, Column 2.

Before table 8 is a number of tables which bookmark the date and time etc and table 18 in the document is the conclusion. So the code needs to delete tables 8 to 17 if Row 4, Column 2 is blank.

Any help would be greatly appreciated.

I haven't put all the bookmarks as there are a lot but the word code I am currently using in excel is:

Dim AppWord As Word.Application
Dim WordDoc As Word.Document
Dim ExcSheet As Excel.Worksheet
Dim i As Integer

Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True
Set WordDoc = AppWord.Documents.Add("CWord doc path.docx")

With WordDoc
.Bookmarks("Name").Range.Text = txt_Name.Value
.Bookmarks("Date").Range.Text = txt_Date.Value

End With

gmayor · #2 10-15-2014, 07:24 AM

Rather than delete unwanted tables from the document, why not add them only when required? If you are populating cells of a table from your userform you don't need bookmarks to take the data you can address the table and cell(s) directly e.g.

Code:

Sub Example()
Dim AppWord As Object
Dim WordDoc As Object
Dim oTable As Object
Dim oRng As Object
Dim oCell As Object
Dim ExcSheet As Excel.Worksheet
Dim i As Integer

    On Error Resume Next
    Set AppWord = GetObject(, "Word.Application")
    If Err Then
        Set AppWord = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    AppWord.Visible = True
    'Set WordDoc = AppWord.Documents.Add("C:\Path\DocName.docx")
    Set WordDoc = AppWord.activedocument
    'Set a range to the table before the place to insert a new table
    Set oRng = WordDoc.Tables(7).Range
    'collapse the range to its end
    oRng.collapse 0
    'Add an empty paragraph to the range
    oRng.Text = vbCr
    'Collapse the range to the end of the empty paragraph
    oRng.collapse 0
    'and add a table at the range
    Set oTable = WordDoc.Tables.Add(Range:=oRng, NumRows:=2, NumColumns:=3)
    'Now fill the table
    Set oCell = oTable.Cell(1, 1).Range        'Row,Column
    oCell.End = oCell.End - 1
    oCell.Text = "Value for Cell 1,1"
    Set oCell = oTable.Cell(1, 2).Range
    oCell.End = oCell.End - 1
    oCell.Text = "Value for Cell 1,2"
    'Set oRng to oTable.Range
    'and repeat the above section for each new table depending on the coindition that determines whether a table is required.
End Sub

gmaxey · #3 10-15-2014, 09:27 AM

I agree with Graham. You don't need any bookmarks if you are writing data to table cell. However, as your objective tables (table 8-17) could be more than simply basic rows and columns and creating them with code can get gnarly very quickly, I would do it a little differently.

In your template create the first 7 tables and the first objective table (table 8). Select the objective table and create with it a building block. Call it "Objective Table" and save it to the template.

Then use code something like this:

Code:

Sub Example()
Dim AppWord As Object
Dim oDoc As Object
Dim oTbl As Object
Dim oRng As Object
Dim lngIndex As Long, lngObjectives As Long
  On Error Resume Next
  Set AppWord = GetObject(, "Word.Application")
  If Err Then
      Set AppWord = CreateObject("Word.Application")
  End If
  On Error GoTo 0
  Set oDoc = AppWord.Documents.Add("D:\Demo Template.dotm")
  'Write the first objective.
  Set oTbl = oDoc.Tables(8)
  With oTbl
    .Cell(2, 2).Range.Text = "Some text"
    .Cell(4, 1).Range.Text = "Some other text"
    'etc.
  End With
  'Are there other objectives? Yes in this demo 5
  lngObjectives = 5 'Replace with whatever you have now that determines the number of objectives required.
  For lngIndex = 2 To lngObjectives
    Set oRng = oTbl.Range
    oRng.Collapse wdCollapseEnd
    oRng.MoveEnd wdCharacter, 1
    oRng.InsertBefore vbCr
    oRng.MoveStart wdCharacter, 1
    ActiveDocument.AttachedTemplate.BuildingBlockEntries("Objective Table").Insert Where:=oRng
    'Write data to new objective as required.
    Set oTbl = oRng.Tables(1)
    With oTbl
      .Cell(2, 2).Range.Text = "Some text"
      .Cell(4, 1).Range.Text = "Some other text"
      'etc.
    End With
  Next lngIndex
End Sub

jon12355 · #4 10-15-2014, 11:14 AM

Thank you for your replies.

gmayor, I used your code, I can get it to work however it is still creating the tables regardless of if the excel data is blank. The way I have the tables set up is:

Objective 1
Heading: l Excel Data enter here
Heading: l Excel Data enter here
Heading: l Excel Data enter here

So with your code I have changed it to:

Code:

Sub Example()
Dim AppWord As Object
Dim WordDoc As Object
Dim oTable As Object
Dim oRng As Object
Dim oCell As Object
Dim ExcSheet As Excel.Worksheet
Dim i As Integer

    On Error Resume Next
    Set AppWord = GetObject(, "Word.Application")
    If Err Then
        Set AppWord = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    AppWord.Visible = True
    'Set WordDoc = AppWord.Documents.Add("C:\Path\DocName.docx")
    Set WordDoc = AppWord.activedocument
    'Set a range to the table before the place to insert a new table
    Set oRng = WordDoc.Tables(7).Range
    'collapse the range to its end
    oRng.collapse 0
    'Add an empty paragraph to the range
    oRng.Text = vbCr
    'Collapse the range to the end of the empty paragraph
    oRng.collapse 0
    'and add a table at the range
    Set oTable = WordDoc.Tables.Add(Range:=oRng, NumRows:=10, NumColumns:=2)
    'Now fill the table
    Set oCell = oTable.Cell(1, 1).Range        'Row,Column
    oCell.End = oCell.End - 1
    oCell.Text = "Objective 1"
    Set oCell = oTable.Cell(1, 2).Range
    oCell.End = oCell.End - 1
    oCell.Text = ""
Set oCell = oTable.Cell(2, 1).Range        'Row,Column
    oCell.End = oCell.End - 1
    oCell.Text = "Heading:"
    Set oCell = oTable.Cell(2, 2).Range
    oCell.End = oCell.End - 1
    oCell.Text = Excel Textbox.Value
    'Set oRng to oTable.Rang
    'and repeat the above section for each new table depending on the coindition that determines whether a table is required.
End Sub

When I press the command button it still creates the tables regardless of if the boxes in excel are blank or have text in. Am i doing something wrong?

gmaxey · #5 10-15-2014, 11:26 AM

How many tables are in "C:\Path\DocName.docx?" The code you have posted only adds one table so it impossible for it to still create "the tables" (plural) as you indicate. Did you delete the 10 objective tables from the file above.

BTW why are you using a document file instead of a template file (.dotx)?

jon12355 · #6 10-15-2014, 11:48 AM

Quote:

Originally Posted by gmaxey

How many tables are in "C:\Path\DocName.docx?" The code you have posted only adds one table so it impossible for it to still create "the tables" (plural) as you indicate. Did you delete the 10 objective tables from the file above.

BTW why are you using a document file instead of a template file (.dotx)?

There are 7 tables before the objectives, then up to 10 objective tables, and a conclusion table. So in total 18.

I deleted the objective tables from the word document. In excel I input two objectives and tested it as I was going along. It was creating the tables with headings although the excel textbox was blank. What I was hoping it would do is only create the table if the textboxes had text in.

gmaxey · #7 10-15-2014, 11:58 AM

So your word document when you start this processes now has 8 tables. The seven leading tables, and the final conclusion table.

Then with your code you might create from 1 to 10 additional objective tables. However, the code you posted only adds 1 table. Resulting in a maximum of 9 table in the finished document. So either a) you posted the wrong code, or b) your original document that you are using with a template has 19 tables.

What is the code you are using that creates up to 10 tables? In other words, there is nothing in the code you posted that:

For lngIndex = 1 to SomeNumberUpTo10
oWordDoc.Tables.Add
Next lngIndex

gmaxey · #8 10-15-2014, 12:46 PM

Use whichever method you prefer, however to show that mine will work, I've uploaded two files you can use to illustrate.

Save the template in the root "D" drive or change the code as required.

jon12355 · #9 10-15-2014, 01:05 PM

Quote:

Originally Posted by gmaxey

Use whichever method you prefer, however to show that mine will work, I've uploaded two files you can use to illustrate.

Save the template in the root "D" drive or change the code as required.

Thank you for showing me your example, I will will have a play around with it

jon12355 · #10 10-15-2014, 02:06 PM

I have tried to use your code on my spreadsheet but I cant get it to work

For the first part of the code I have put

Code:

Dim AppWord As Object
Dim oDoc As Object
Dim oTbl As Object
Dim oRng As Object
Dim lngIndex As Long, lngObjectives As Long
  On Error Resume Next
  Set AppWord = GetObject(, "Word.Application")
  If Err Then
      Set AppWord = CreateObject("Word.Application")
  End If
  AppWord.Visible = True
  On Error GoTo 0
  Set oDoc = AppWord.Documents.Add("Pathway to template document")

  Set oTbl = oDoc.Tables(8)
  With oTbl
    .Cell(2, 2).Range.Text = combo_Obj1_Reg.Text
    .Cell(3, 2).Range.Text = combo_Obj1_RegC.Text
    .Cell(4, 2).Range.Text = txt_Obj1Nar.Text
    .Cell(5, 2).Range.Text = txt_Obj1Sum.Text
    .Cell(6, 2).Range.Text = txt_Obj1_irr.Text
    .Cell(7, 2).Range.Text = txt_Obj1_Post.Text
    .Cell(8, 2).Range.Text = txt_Obj1_Att1.Text
    .Cell(9, 2).Range.Text = txt_Obj1_Att2.Text
    .Cell(10, 2).Range.Text = txt_Obj1_Att3.Text
  End With

In your example it looks like the next part of the code refers to coloumns, how to I write it for text and comboboxes? In the above code all the naming is the same expect obj1 is obj2.

gmaxey · #11 10-15-2014, 03:01 PM

Well yes, my code assumed that it was some value in the worksheet itself that determines if a second and subsequent objective is defined. If it is something in the userform then you might use something like:

For lngIndex = 2 to 10
If Me.Controls("Objective" & lngIndex).Value <> vbNullString then

End if
Next lngIndex

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Blury Excel Tables in Word	zachg18	Word	1	07-15-2013 11:10 PM
how to copy all ms word tables into excel	rehan129	Word	0	01-28-2012 10:17 AM
Excel Tables to MS Word	ripcurlksm	Word Tables	2	09-09-2011 04:59 AM
using checkboxes to delete tables	atfresh	Word Tables	1	06-19-2011 09:13 PM
working with excel tables in MS word	radman154	Word Tables	1	03-25-2011 12:04 AM