creating table in Word via Excel

[Quinn]

I'm creating a 2 columns x N rows table in Word from an Excel spreadsheet.
Everything works OK , except if there is a missing value in an Excel cell.
It will then created a table with 1 column instead of two. This makes it impossible to format the table using the Columns(1).width method
my code is below:
......
Selection.Copy
Range("B40:B61").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
'formatting
Range("A41:B61").Font.Size = 8
Range("A40:B40").Font.Size = 10
Range("A40:B40").Font.Bold = True
Range("A40:B40").HorizontalAlignment = xlCenter
Range("A41:B61").HorizontalAlignment = xlLeft


'copy from Excel
Range("A40:B61").Copy

'paste into Word
Appword.Selection.Paste
'then some formatting for the table

[mdmackillop]

Hi Quinn,
I can't duplicate your problem. Can you post your worksheet and code? (Use Manage Attachments in the Go Advanced section) Delete any sensitive data.
Regards
MD

[austenr]

Could you check your range for a missing value replace it with something then after the table is built in word replace the place holder with spaces? Seems to me that you would have a space either way you go in the final product.

[Quinn]

MD,
thanks for your time, I'm attatching an exmple of the spreadsheet.
I've altered the data that is proprietary, and included only several rows. I need to create 2 X 22 tables (the 22 rows might change, but not the 2 columns)
from data in an Excel spreadsheet. The first row of the spreasheet will be the first column in every table. The 2nd, 3rd,.....,50th rows will be the
2nd column in table 2, 2nd column in table 3, ....2nd column in table 50 respectively.
However, there will be some cells in the 2nd, 3rd, 4th rows in the Excel sheet that will be blank, when this happens, though , my code will only create 1 column instead of two for the table.
(AustenR...yes, I might try your suggestion as a last resort, although since other researches will be looking at the data, I would rather have something that is not so Ad Hoc )

[mdmackillop]

Hi Quinn
Still don't see the problem.
I've revised the formatting only in the following code, which should make things clearer


Option Explicit
Sub Tables()
'This code requires a referece to the Word object model
Dim Appword As New Word.Application
Dim wdDoc As Word.Document
Dim Array1, Array2, X As Long, i As Long
Dim LastRow As Integer
Set Appword = CreateObject("Word.Application")
Appword.Documents.Add
'Count the number of rows in Excel
LastRow = Range("B2").End(xlDown).Row
'the first row is in every table as the first column
' Array1 = Array(Range("A1:V1").Value)
Array1 = Array(Range("A1:V1").Value)
'transpose row 1 to column 1
Range("a40:a61").Value = Application.WorksheetFunction.Transpose(Array1)

'create (Number of Rows -1) tables
For X = 1 To (LastRow - 1)
' For X = 1 To 3
'the next row will become the 2nd column in Word
Range("A1:V1").Offset(X, 0).Select
Selection.Copy
Range("B40:B61").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'formatting
Range("A41:B61").Font.Size = 8
Range("A40:B40").Font.Size = 10
Range("A40:B40").Font.Bold = True
Range("A40:B40").HorizontalAlignment = xlCenter
Range("A41:B61").HorizontalAlignment = xlLeft
Range("A41:B61").WrapText = True

'copy from Excel
Range("A40:B61").Copy
'paste into Word
Appword.Selection.PasteSpecial
Appword.Visible = True
'format table
With Appword.Selection.Tables(1)
.Columns(1).Width = InchesToPoints(2)
.Columns(2).Width = InchesToPoints(2)
.Rows.Height = InchesToPoints(0.25)
End With
For i = -1 To -6 Step -1
With Appword.Selection.Tables(1).Borders(i)
.LineStyle = Options.DefaultBorderLineStyle
.LineWidth = Options.DefaultBorderLineWidth
.Color = Options.DefaultBorderColor
End With
Next
Appword.Selection.Tables(1).Rows(1).Cells.Merge
'insert page break between tables
Appword.Selection.InsertBreak
Next
' Range("A40:B62").Delete
Appword.Visible = True
End Sub