Export From Excel Cells To Word Table

[ioncila]

Hi
I hope I choosed the right section of the forum to ask help for thhis issue.
Here is is:

I have a small data base in excel where each line is filled from userform fields (Textboxes and comboboxes). This works fine.
That userform has a commandbutton that, when activated, would call a word template with a certain table and would populate each cell of that table with data from userform fields.

I know how to use VBA in Excel. Dont know how to work in Word.

I've searched here and in other foruns and did find some similar solutions, but still dont know how to change code to match my problem.

So, I would appreciate every help from you all.

Thank you very much in advance.

[Dave]

This will likely get you started (untested). The .dot table will have to be the same size as the XL range of cells. Adjust the "Rng" required to suit. HTH. Dave

Sub XLToWordTable()
Dim ObjWord As Object, Rng As Range
Dim wrdDoc As Object, Ocell As Variant, TC As Variant
Dim Lastrow As Integer, Lastcol As Integer, Cnt As Integer
'adds XL cell contents to Word .dot table(1)from XL
'no Word reference required

On Error GoTo Erfix
'open existing word .dot file ie. "D:\tabletest.dot"
'*** "D:\tabletest.dot" MUST exist ie. change address to suit
Set ObjWord = CreateObject("Word.Application")
Set wrdDoc = ObjWord.Documents.Open(Filename:="D:\tabletest.dot")

'set XL range to suit
'determine table sixe from Xl range (used range in this eg.)
Lastrow = Sheets("Sheet1").UsedRange.Rows.Count
Lastcol = Sheets("Sheet1").UsedRange.Columns.Count

'vba set XL range
With Sheets("Sheet1")
Set Rng = .Range(.Cells(1, 1), .Cells(Lastrow, Lastcol))
End With
'insert XL cell.value to table location
'table(1) in this example (ie change table to suit)
Cnt = 1
For Each Ocell In Rng
Set TC = ObjWord.ActiveDocument.Tables(1).Range.Cells(Cnt)
TC.Range.InsertAfter Ocell.Value
Cnt = Cnt + 1
Next Ocell

'fit table and add adjustments/margins
With ObjWord.ActiveDocument.Tables(1)
.Columns.AutoFit
'.Rows.SetLeftIndent LeftIndent:=-57.6, RulerStyle:=False
'.Columns(3).SetWidth ColumnWidth:=153.3, RulerStyle:=False
'.Columns(4).SetWidth ColumnWidth:=144, RulerStyle:=False
End With

wrdDoc.SaveAs "D:\TEST.DOC" 'change file name to suit
wrdDoc.Close savechanges:=False
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
MsgBox "Finished"
Exit Sub

Erfix:
On Error GoTo 0
MsgBox "error"
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
End Sub

[ioncila]

Hi Dave
Thank you very much fo reply.

Still, there's some aspects I dont understand:

What do you mean with "...dot table must have the same size as the lx range of cells"? - Must be really like that.

While waiting, I tried an approach to a code I found in a similar thread. It returns error in the bold line.


Private Sub BotãoModelo1_Click()

'copy range to table in word doc

Dim objWordApp As Object
Dim objWordDoc As Object
Dim rngData As Range
Dim ws As Worksheet

Set ws = Worksheets("CE")



Set rngData = Cells(ws.Cells(65356, 5).End(xlUp).Row + 1).EntireRow

' create instance of word and open doc
Set objWordApp = CreateObject("Word.application")
objWordApp.Visible = True
Set objWordDoc = objWordApp.Documents.Open("H:\Model1.doc")

' goto bookmark
objWordDoc.Bookmarks("TxRegistoCriado").Range.Select
Cells(rngData, 1).Copy
objWordDoc.Bookmarks("TxData").Range.Select
Cells(rngData, 3).Copy
objWordDoc.Bookmarks("TxRemetente").Range.Select
Cells(rngData, 4).Copy
objWordDoc.Bookmarks("TxDestinatário").Range.Select
Cells(rngData, 5).Copy
objWordDoc.Bookmarks("TxATT").Range.Select
Cells(rngData, 6).Copy
objWordDoc.Bookmarks("TxCC").Range.Select
Cells(rngData, 7).Copy
objWordDoc.Bookmarks("TxFax").Range.Select
Cells(rngData, 8).Copy
objWordDoc.Bookmarks("TxAnexos").Range.Select
Cells(rngData, 12).Copy
objWordDoc.Bookmarks("TxAssunto").Range.Select
Cells(rngData, 13).Copy
objWordDoc.Bookmarks("TxVRef").Range.Select
Cells(rngData, 14).Copy


' Paste the range
' objWordApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, _
Placement:=wdInLine, DisplayAsIcon:=False
objWordApp.Selection.PasteSpecial Link:=False, DataType:=1, _
Placement:=0, DisplayAsIcon:=False

End Sub

[fumei]

1. Care to tell us what error?

"It returns error in the bold line."

2. nothing is going to go into the Word document bookmarks. You select each...and then do nothing with it, and then select the next one...and do nothing with it.

BTW: selecting the bookmarks is NOT needed.

[Dave]

Set objWordDoc = objWordApp.Documents.Open("H:\Model1.doc")
I thought you asked about a template (.dot) file? The Word table has to have enough cells to hold the specified XL range. You could just transfer a single XL cell to a specified single table cell rather than setting an XL range. Fumei is much more able to provide assitance, so I will leave you to his capable assistance. Good luck. Dave

[fumei]

1. BTW: Cells(rngData, 1).Copy is probably getting an error because it is not qualified. Perhaps:
ws.Cells(rngData, 1).Copy


2. also, .Copy does NOT paste into Word, it just copies. You need to use a paste.

[Tinbendr]

... when activated, would call a word template with a certain table and

would populate each cell of that table with data from userform fields.
(I borrowed from Dave a little.)

Sub XLToWordTable_2()
Dim ObjWord As Object
Dim wrdDoc As Object

'Set the Word Object
Set ObjWord = CreateObject("Word.Application")

'Create a Document based on the template TableTest.dot
'You'll need to create the template ahead of time.
Set wrdDoc = ObjWord.Documents.Open(FileName:="D:\tabletest.dot")

'If there is more than one
'table, but sure to change it here.
With wrdDoc.Tables(1)
.Cell(1, 1) = userform1.textbox1
.Cell(1, 2) = userform1.textbox2
End With

Set wrdDoc = Nothing
Set ObjWord = Nothing

End Sub

[Dave]

Maybe just to add to Tinbendr's post (save .dot as .doc, Quit the Word app, and error control added). HTH. Dave

Sub XLToWordTable_2()
Dim ObjWord As Object
Dim wrdDoc As Object

On Error GoTo ErFix
'Set the Word Object
Set ObjWord = CreateObject("Word.Application")

'Create a Document based on the template TableTest.dot
'You'll need to create the template ahead of time.
Set wrdDoc = ObjWord.Documents.Open(Filename:="D:\test.dot")

'If there is more than one
'table, but sure to change it here.
With wrdDoc.Tables(1)
.Cell(1, 1) = UserForm1.TextBox1
.Cell(1, 2) = UserForm1.TextBox2
End With

'close and save .dot as .doc
wrdDoc.SaveAs "D:\TEST.DOC" 'change file name to suit
wrdDoc.Close savechanges:=False
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "error"
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
End Sub

[ioncila]

Hi
Been away for a few days. Before I went, I took all post suggestions (thank you very much guys) and changed my code to solve my issue.

@Dave:
Sorry, I only saw your post today, so I didnt test your code yet, but I will try it next Thursday (Im going out again in work) and see if there is some improvement to add to my code.

Up till now, it works very fine.

Here it is:

Private Sub BotãoModelo1_Click()

'copy range to table in word doc
Dim objWordApp As Object
Dim objWordDoc As Object

If CxFax.Value Then

' create instance of word and open doc
Set objWordApp = CreateObject("Word.application")
objWordApp.Visible = True
Set objWordDoc = objWordApp.Documents.Open(Filename:="H:\ModeloFax.doc")

'If there is more than one
'table, but sure to change it here.
With objWordDoc.Tables(1)
.Cell(2, 2) = UserForm1.TxData
.Cell(3, 2) = UserForm1.ComboRemetente
.Cell(4, 2) = UserForm1.TxRegistoCriado
.Cell(5, 2) = UserForm1.TxVREF
.Cell(6, 2) = UserForm1.TxAssunto
.Cell(7, 2) = UserForm1.TxAnexos
.Cell(2, 4) = UserForm1.TxFax
.Cell(3, 4) = UserForm1.TxDestino
.Cell(4, 4) = UserForm1.TxATT
.Cell(5, 4) = UserForm1.TxCC

End With

ElseIf CxCarta.Value Then

' create instance of word and open doc
Set objWordApp = CreateObject("Word.application")
objWordApp.Visible = True
Set objWordDoc = objWordApp.Documents.Open(Filename:="H:\ModeloCarta.doc")

'If there is more than one
'table, but sure to change it here.
With objWordDoc.Tables(1)
.Cell(2, 3) = UserForm1.TxDestino
.Cell(4, 3) = UserForm1.TxATT
.Cell(5, 3) = UserForm1.TxEndereço
.Cell(6, 3) = UserForm1.TxCodPostal1 & "-" & UserForm1.TxCodPostal2 & " " & UserForm1.TxCodPostal3
.Cell(7, 2) = UserForm1.TxData
.Cell(7, 4) = UserForm1.TxRegistoCriado
.Cell(7, 6) = UserForm1.TxVREF
.Cell(8, 2) = UserForm1.TxAssunto
.Cell(9, 2) = UserForm1.TxAnexos
End With

End If

UserForm1.Hide

End Sub


So Thank you very much again for your help

Cheers
Ioncila

[fumei]

Just one comment from me. I would make your instance of Word outside your logic. Is there a situation where you would NOT make any instance?

[ioncila]

would you be more clear, please? I'm in difficult to understand your question.
Thanks

[fumei]

You have:
If CxFax.Value Then

' create instance of word and open doc
Set objWordApp = CreateObject("Word.application")
' other stuff

ElseIf CxCarta.Value Then

' create instance of word and open doc
Set objWordApp = CreateObject("Word.application")

This has TWO instructions for creating an instance of Word. True, only one is created depending on the logic. More simply this is:

If CxFax.Value Then
create instance of Word
execute instructions
ElseIf CxCarta.Value Then
create instance of Word
execute instructions
The reason I ask if there is any condition you would NOT create an instance of Word, then that should be explicitly part of the logic. Otherwise, this is better:
create instance of Word
If CxFax.Value Then
execute instructions
ElseIf CxCarta.Value Then
execute instructions

[ioncila]

Hi
Thank You for reply
I'm not quit sure if I understood your suggestion. Did you mean this?:
'copy range to table in word doc
Dim objWordApp As Object
Dim objWordDoc As Object

' create instance of word and open doc
Set objWordApp = CreateObject("Word.application")
objWordApp.Visible = True

If CxFax.Value Then

Set objWordDoc = objWordApp.Documents.Open(Filename:="H:\ModeloFax.doc")


With objWordDoc.Tables(1)
.Cell(2, 2) = UserForm1.TxData
.Cell(3, 2) = UserForm1.ComboRemetente
.Cell(4, 2) = UserForm1.TxRegistoCriado
.Cell(5, 2) = UserForm1.TxVREF
.Cell(6, 2) = UserForm1.TxAssunto
.Cell(7, 2) = UserForm1.TxAnexos
.Cell(2, 4) = UserForm1.TxFax
.Cell(3, 4) = UserForm1.TxDestino
.Cell(4, 4) = UserForm1.TxATT
.Cell(5, 4) = UserForm1.TxCC

End With

ElseIf CxCarta.Value Then

Set objWordDoc = objWordApp.Documents.Open(Filename:="H:\ModeloCarta.doc")

'If there is more than one
'table, but sure to change it here.
With objWordDoc.Tables(1)
.Cell(2, 3) = UserForm1.TxDestino
.Cell(4, 3) = UserForm1.TxATT
.Cell(5, 3) = UserForm1.TxEndereço
.Cell(6, 3) = UserForm1.TxCodPostal1 & "-" & UserForm1.TxCodPostal2 & " " & UserForm1.TxCodPostal3
.Cell(7, 2) = UserForm1.TxData
.Cell(7, 4) = UserForm1.TxRegistoCriado
.Cell(7, 6) = UserForm1.TxVREF
.Cell(8, 2) = UserForm1.TxAssunto
.Cell(9, 2) = UserForm1.TxAnexos
End With

End If

[fumei]

Yes, exactly. As stated, your previous code was:
If CxFax.Value Then
create instance of Word
execute instructions
ElseIf CxCarta.Value Then
create instance of Word
execute instructions
Which has two separate instructions to make the instance of Word. You new code is:
create instance of Word
If CxFax.Value Then
execute instructions
ElseIf CxCarta.Value Then
execute instructions
which is one. The point being is that if you ALWAYS require an instance of Word (regardless of the result of your IF statement), then make that instance and then do the IF logic.

If there is a possibility that you do NOT need any instance of Word at all, then fine, make the creation of the instance part of the IF logic.

[Dave]

I don't follow the logic of your code? Usually something.value represents some number ("If CxFax.Value Then"). Using an "If", something needs to provide logic (unless this is a boolean?). Further, if there's a chance of these conditions(if they exist) not being met, then the whole point is not to make the Word application, and if you do, you only have 1 file to open, why not do it only once? HTH. Dave

[fumei]

1. "Usually something.value represents some number ("If CxFax.Value Then")."

Oh no it does NOT represent some number (other than 0 and -1).
If CxFax.Value Then
is a boolean expression, with True numerically = -1 and False = 0...or is it the other way around?

This is why I recommend never actually using .Value for anything. .Value is always whatever is the DEFAULT property of the object. It is better to be explicit.

Checkbox.Value is either "True" or "False"
TextBox.Value is the text in the textbox...but so is Textbox.Text. So if you are testing against the text, why not use .Text?

Commandbutton do not have a .Value.

What is CxFax? A checkbox? A textbox? If it is a checkbox, then yes it has a .Value, and
If Cxfax.Value Then
is either "True" or "False".

2. "Using an "If", something needs to provide logic (unless this is a boolean?). "

All IF statement are boolean...and ONLY boolean.

Nevertheless, I agree...and I have been trying to get this across.

The reason I ask if there is any condition you would NOT create an instance of Word, then there is no need for a Word instance. In which case, sure put the instance creation portion into the IF logic.

Say...
IF CxFax.Value = True Then ' which is what the code does
' create Word instance
' do this
ELSEIF CxCarta.Value = True Then
' create Word instance
' do this
End IF
Logically you are only testing TWO conditions:

If Cxfax is true, do this.
If CxFax is false, but CxCarta is true, do this.

Nothing else is tested.

1. If Cxfax is true, then Cxcarta is NOT - repeat NOT - tested.

2. So with:
IF CxFax.Value = True Then ' which is what the code does
' create Word instance
' do this
ELSEIF CxCarta.Value = True Then
' create Word instance
' do this
End IF
If CxFax is true, OR CxFax is False and CxCarta is true, then an instance of Word is created. Otherwise (as it stands) NO instance of Word is created.

' create Word instance
IF CxFax.Value = True Then ' which is what the code does
' do this
ELSEIF CxCarta.Value = True Then
' do this
End IF
creates an instance regardless of whether cxFax is true or false. Which brings meback to what I posted.
Just one comment from me. I would make your instance of Word outside your logic. Is there a situation where you would NOT make any instance?I ask because so much of the thread has interaction with Word, all that table stuff. SO....it sure looks to me that an instance of Word is being used regardless of the result of IF cxFax is true...or not.

In which case, IMHO, creation an instance of Word is NOT - logically - part of whether CxFax is true (or not).

[Dave]

Thanks Fumei. That's some pretty valuable learning re. ".value" I'm hoping that this is also helpful for ioncila. Dave

[ioncila]

Thanks Fumei. That's some pretty valuable learning re. ".value" I'm hoping that this is also helpful for ioncila. Dave

Sure it is. Forums are much better than any other course.
And too much better when we have the fortune to find expert guys like you all.

Thank You very much.

By the way, CxFax and CxCarta are checkboxes. Sorry if I didnt mentioned that before.

Now I'm going to study your suggetions and correct my code.

Ioncila

[fumei]

I suspected they were checkboxes, and yes - technically

If cxFax.Value Then
is valid syntax (it will work). That is because it REALLY is parsed as:

If CxFax.Value = True Then
and Cxfax.Value IS a Boolean (true or false) property.

Take a look at the attached doc file. Click "Show Userform" on the top toolbar. This display a simple userform with one textbox and one commandbutton. The commandbutton executes:
Private Sub CommandButton1_Click()
If TextBox1.Value Then
MsgBox ".Value is TRUE"
Else
MsgBox ".Value is FALSE"
End If
Unload Me
End Sub
Notice it is using .Value for the textbox. It will always fail. Why? Because although .Value is the same value as .Text, it is NOT boolean. And:
If TextBox1.Value Then
IS boolean. It is the same as:
If TextBox1.Value = True Then
The same as a checkbox...but in this case .Value is not boolean.

Checkbox1.Value is boolean
Textbox1.Value is not boolean.

Perhaps of more interest is testing the attached demo with, and without, text in the textbox.

So try it with no text. Click "Show userform" and then click the OK button. It will fail on run-time error 5 - Invalid procedure call or argument.

Stop it by clicking the End button on the error message.

Now click "Show Userform" again, but this time put some text into the textbox. Click the OK button. It will fail again, but this time on the dreaded error 13 - Type mismatch.

Why is there a difference?

Because with nothing in the textbox, its .Value = "" - a null string. Therefore it is neither true nor false. However, this is an invalid procedure call as VBA can not evaluate it as true or false (the IF statement).

In the second case - there is text in the textbox - .Value does have a string value. It is not a null string. Therefore VBA can evaluate it as true or false...EXCEPT...it is not boolean (True or False) and therefore there is a "mis-match" between the expression (Textbox1.Value) and the logic operation (the IF statement).

[geekgirlau]

By the way, CxFax and CxCarta are checkboxes. Sorry if I didnt mentioned that before.

I suspect that your form is basically offering a choice between 2 different documents - fax and carta. If you are only going to create 1 document, tather than using checkboxes I would suggest you change these controls from checkboxes to an option group. That way there is no possibility of a user selecting both document types.

[fumei]

" I would suggest you change these controls from checkboxes to an option group. "

Good advice. Use radio buttons within a frame. Only one can be True.

[SWE321]

Thank you for providing such detail code. I modified it slightly and it worked perfectly for me. Chears!!!


This will likely get you started (untested). The .dot table will have to be the same size as the XL range of cells. Adjust the "Rng" required to suit. HTH. Dave

Sub XLToWordTable()
Dim ObjWord As Object, Rng As Range
Dim wrdDoc As Object, Ocell As Variant, TC As Variant
Dim Lastrow As Integer, Lastcol As Integer, Cnt As Integer
'adds XL cell contents to Word .dot table(1)from XL
'no Word reference required

On Error GoTo Erfix
'open existing word .dot file ie. "D:\tabletest.dot"
'*** "D:\tabletest.dot" MUST exist ie. change address to suit
Set ObjWord = CreateObject("Word.Application")
Set wrdDoc = ObjWord.Documents.Open(Filename:="D:\tabletest.dot")

'set XL range to suit
'determine table sixe from Xl range (used range in this eg.)
Lastrow = Sheets("Sheet1").UsedRange.Rows.Count
Lastcol = Sheets("Sheet1").UsedRange.Columns.Count

'vba set XL range
With Sheets("Sheet1")
Set Rng = .Range(.Cells(1, 1), .Cells(Lastrow, Lastcol))
End With
'insert XL cell.value to table location
'table(1) in this example (ie change table to suit)
Cnt = 1
For Each Ocell In Rng
Set TC = ObjWord.ActiveDocument.Tables(1).Range.Cells(Cnt)
TC.Range.InsertAfter Ocell.Value
Cnt = Cnt + 1
Next Ocell

'fit table and add adjustments/margins
With ObjWord.ActiveDocument.Tables(1)
.Columns.AutoFit
'.Rows.SetLeftIndent LeftIndent:=-57.6, RulerStyle:=False
'.Columns(3).SetWidth ColumnWidth:=153.3, RulerStyle:=False
'.Columns(4).SetWidth ColumnWidth:=144, RulerStyle:=False
End With

wrdDoc.SaveAs "D:\TEST.DOC" 'change file name to suit
wrdDoc.Close savechanges:=False
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
MsgBox "Finished"
Exit Sub

Erfix:
On Error GoTo 0
MsgBox "error"
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
End Sub