Amending table after data copied in from Excel

[paddysheeran]

Hi All,

I have written the following code which copies a table over from excel into word and amends the columnwidth and aligns the table in the centre of the page.

I want to apply some further formatting to the borders of the table but cant seen to get this to work. Here is the code:

Sub WR_Service_Management_Summary()
Set wsSource = ActiveWorkbook.Sheets("Totals")

Set t = wdDoc.Bookmarks("Totals").Range
wsSource.Activate
'Reporting Period dates
Set Rng = Range("B6:C14")
Rng.Copy
t.Paste

wdApp.Visible = True

With t
.Font.Size = "8"
.Tables(1).Rows.Alignment = wdAlignRowCenter
.Tables(1).Rows(1).HeadingFormat = True
.Tables(1).Columns(1).SetWidth ColumnWidth:=330, RulerStyle:= _
wdAdjustNone
.Tables(1).Columns(2).SetWidth ColumnWidth:=30, RulerStyle:= _
wdAdjustNone
End With

t.Tables(1).Rows(1).Select

'Problem code....

Options.DefaultBorderLineWidth = wdLineWidth150pt
With Selection.Borders(wdBorderBottom)
.LineStyle = Options.DefaultBorderLineStyle
.LineWidth = Options.DefaultBorderLineWidth
.Color = Options.DefaultBorderColor
End With


End Sub


It get the error message "Application-defined or object defined error" when the With Selection.Borders(wdBorderBottom) runs.

thanks in advance.

Paddy.

[fumei]

Let me give some advice about posting here.

1. Let us know if you are using Option Explicit. You have variables that do not appear to be declared.

2. Where is the Word application declared and created?

Because it is not declared, and it seems you are executing this from Excel, then Selection.Borders(wdBorderBottom) means the Selection in Excel, NOT Word.

You need to either fully qualify this instruction, or have the proper reference to Word (early-binding). Basically, you seem to have no Word application. Although that can not really be the case as it seems that:

Set t = wdDoc.Bookmarks("Totals").Range

is not giving you an error. Although where wdDoc is declared and set is a mystery.


You need to give more details.

[paddysheeran]

Sorry - here is the full code:

Option Explicit
Public Templatepath, Report_Date, WordReportTemplate As String
Public Rng As Range
Public wsSource As Worksheet
Public t As Word.Range 'the new table in Word as a range
Public wdApp As New Word.Application
Public wdDoc As Word.Document 'our new Word document
Public myWordFile As String 'path to Word template
Public oRow As Row
Sub WR_Update()


Report_Date = Range("B8")

Templatepath = ThisWorkbook.Path
WordReportTemplate = Templatepath & "\NR Template Monthly.dotx"
'Backup = Templatepath & "\Backup\"

Set wdDoc = wdApp.Documents.Add(WordReportTemplate)

WR_Title_Page
WR_Service_Management_Summary


wdDoc.TablesOfContents(1).UpdatePageNumbers
wsSource.Activate
Set t = wdDoc.Bookmarks("Report_Date").Range
t.Select
wdApp.Visible = True
End Sub
Sub WR_Title_Page()
Set wsSource = ActiveWorkbook.Sheets("Report")
Set t = wdDoc.Bookmarks("Report_Date").Range
wsSource.Activate
'Reporting Period dates
Set Rng = Range("B8")
Rng.Copy
t.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False
'Report_Date_Style
End Sub
Sub WR_Service_Management_Summary()
Set wsSource = ActiveWorkbook.Sheets("Totals")

Set t = wdDoc.Bookmarks("Totals").Range
wsSource.Activate
'Reporting Period dates
Set Rng = Range("B6:C14")
Rng.Copy
t.Paste

wdApp.Visible = True

With t
.Font.Size = "8"
.Tables(1).Rows.Alignment = wdAlignRowCenter
.Tables(1).Rows(1).HeadingFormat = True
.Tables(1).Columns(1).SetWidth ColumnWidth:=330, RulerStyle:= _
wdAdjustNone
.Tables(1).Columns(2).SetWidth ColumnWidth:=30, RulerStyle:= _
wdAdjustNone
End With

'Borders

t.Tables(1).Rows(1).Select

Options.DefaultBorderLineWidth = wdLineWidth150pt
With Selection.Borders(wdBorderBottom)
.LineStyle = Options.DefaultBorderLineStyle
.LineWidth = Options.DefaultBorderLineWidth
.Color = Options.DefaultBorderColor
End With


'Monthly ESLG Service Results

Set wsSource = ActiveWorkbook.Sheets("ESLG Service Results")

Set t = wdDoc.Bookmarks("ESLG_Orders").Range
wsSource.Activate
ActiveSheet.ChartObjects("212 orders").Select
ActiveChart.ChartArea.Copy
t.PasteAndFormat (wdPasteDefault)

Set t = wdDoc.Bookmarks("ESLG_Incidents").Range
wsSource.Activate
ActiveSheet.ChartObjects("212 incidents").Select
ActiveChart.ChartArea.Copy
t.PasteAndFormat (wdPasteDefault)

'Monthly ESLG Service Trend Results

Set wsSource = ActiveWorkbook.Sheets("ESLG Trend Chart")

Set t = wdDoc.Bookmarks("ESLG_Orders_Trend").Range
wsSource.Activate
ActiveSheet.ChartObjects("213 orders").Select
ActiveChart.ChartArea.Copy
t.PasteAndFormat (wdPasteDefault)

Set t = wdDoc.Bookmarks("ESLG_Incidents_Trend").Range
wsSource.Activate
ActiveSheet.ChartObjects("213 incidents").Select
ActiveChart.ChartArea.Copy
t.PasteAndFormat (wdPasteDefault)


End Sub

[binko]

Hi Paddy,

I have tried this code and it works for me

Sub WR_Service_Management_Summary()

Dim t As Word.Range

Set t = ThisDocument.Range
t.Tables(1).Rows(1).Select

'Problem code....

Options.DefaultBorderLineWidth = wdLineWidth150pt

With Selection.Borders(wdBorderBottom)
.LineStyle = Options.DefaultBorderLineStyle
.LineWidth = Options.DefaultBorderLineWidth
.Color = Options.DefaultBorderColor
End With
End Sub

do you have any experience with embedded graphs?

binko

[paddysheeran]

the code breaks at the line:

With Selection.Borders(wdBorderBottom)

with the error message:

Run-Time error '1004':

Application-defined or object-defined error

[binko]

it seems your problem is with the selection.

set a break point in the with selection line, and see whether the table row is highlighted in the document.

though as far as I can see, the macro is not in the word document at all?

[binko]

and why do you need the selection at all?

you can set the borders directly for the row, without selecting it

[paddysheeran]

ok can you show me how?

[binko]

With t.Tables(1).Rows(1).Borders(wdBorderBottom) .LineStyle = Options.DefaultBorderLineStyle .LineWidth = Options.DefaultBorderLineWidth .Color = Options.DefaultBorderColor End With

just replace selection with t.tables(1).Rows(1)

[paddysheeran]

thanks.

[binko]

do you have experience with embedded graphs?