Solved: Getting formatted text fom Excel to Word via a ComboBox

[Damageplan]

Hi there,

I need some help in VBA / WordXp:

In Word i have a ComboBox on a UserForm that gets its data from a Excelfile.
The data in the Excelfile is formatted text!

So my question is:
Is it possible to "copy" the selected ComboBox-Text from Excel to Word when user clicks on it at the ComboBox without loosing its formatting?

Thanks for any advices and greetings from germany!

Matthias

[MOS MASTER]

Gutentag Willkomen Matthias!

It's unclear to me right now how you get the data form Excel in to your Word combo and what type of combo that is.

Could you perhaps post a sample of excel data and your word document (Zipped) so we can examine it and help you?

HTH,

[Damageplan]

Hi,

i post some of my code....hope thats ok for you :-)

[VBA]
'Here i insert the text from the ComboBox into the word doc:
'CommandButton1 is on the same UserForm as ComboBox1

Private Sub CommandButton1_Click()
Selection.TypeText Text:=Me.ComboBox1.Value
UserForm1.Hide
End Sub

'Here i load data from the Excelfile to the ComboBox
Private Sub UserForm_Initialize()
Dim ObjExcel As New Excel.Application
Dim wb As Excel.Workbook
Dim FName As Variant
Dim x As Long
Dim LastRow As Long

FName = "<PathToExcelFile>"

Set wb = ObjExcel.Workbooks.Open(FName)

With wb.Sheets(1)
LastRow = .Range("A100:B100").End(xlUp).Row
For x = 1 To LastRow
ComboBox1.AddItem (.Range("A" & x).Text)
ComboBox1.List(x - 1, 1) = .Range("B" & x).Text
Next x
End With
End Sub
[/VBA]

The Text in the Excelfile is formatted and i want to insert the text with all its formatting into the worddoc.

Greetings,

Matthias

[Damageplan]

Hi there again,

does anyone of you have an idea?
I don't know what to do

Greets

Matthias

[TonyJollans]

Hi Matthias,

Welcome to VBAX!

I don't think you can do it using the combobox - it only holds the text, not the formatting, but what about copying and pasting the appropriate cell from Excel after a choice has been made from the combo?

[Damageplan]

...but what about copying and pasting the appropriate cell from Excel after a choice has been made from the combo?

Hm...sounds good!
But I'm very new to VBA :-/

So could you please give me an example or maybe you could modify my code which I posted earlier?

Would be sooo great :-D

Thanks and greets!

Matthias

[TonyJollans]

Hi Matthias,

Your code doesn't show how you close Excel. For this to work you need to keep it open while the form is running so ...

Move these two lines ..
[vba]Dim ObjExcel As New Excel.Application
Dim wb As Excel.Workbook[/vba]
to the top of the userform's module - before the first procedure.

Then close the workbook and excel in the userform terminate event (if you have this somewhere else already then remove it)..
[vba]Private Sub UserForm_Terminate()
wb.Close
ObjExcel.Quit
Set wb = Nothing
Set ObjExcel = Nothing
End Sub[/vba]

When you've done that you'll be able to access the worksheet in the button click event, and can do something like this
[vba]Private Sub CommandButton1_Click()
wb.Sheets(1).Cells(Me.ComboBox1.ListIndex + 1, "A").Copy
Selection.PasteAndFormat wdPasteDefault
End Sub[/vba]

Hopefully, that will get you going somewhere, but do come back if you have any problems with it.

[MOS MASTER]

Hi Matthias & Tony,

Sorry it took me so long to respond.

I've put something (Quickly) together with some of Tony's thoughts and some of mine.

I used a combo as requested with two colums (One shows) and I think it works how you want it.

Perhaps we can take it from there see the attachment.

[lucas]

Thats a nice one Joost

[Damageplan]

Oh my god, you guys are great!!
It works
Thank you for your work and time

Thanks again!!! :-)

Matthias

[MOS MASTER]

Gutentag Matthias!

I'm very glad to see you've found your sollution.

A special thanx to Sir Tony and I was glad I could assist you.

Would you be so kind to mark your thread solved?

[Damageplan]

Hi guys,

i got a new problem regarding to the above mentioned problem :-(
This time i try to insert the copied and formatted Text into a table in a word document...
But each time i do it, there will be a line feet before the text is inserted...that's

I have a small excerpt of my document as attachment!

Please do a right-click into the shown table in my doc and choose "Insert Text"...then the UserForm with the ComboBox to choose from will apear!
When you choose something, then you can see, how the pastefunction do a line feet before inserting the text!

I hope, that someone of you can help me again!
Thanks!

Greetings,

Matthias

[MOS MASTER]

Hi Matthias,

I get a Line feed after the paste action. (It moves to the next line)
This is because the line feed character is in the excel cells format.

It gone when I change the code to:[vba]
Private Sub ComboBox1_Change()
ExcelBook.Sheets(1).Range(ComboBox1.Column(1)).Copy
With Selection
.PasteAndFormat wdPasteDefault
.TypeBackspace
End With
End Sub
[/vba]

HTH,

[Damageplan]

Hi Joost,

ahhhh..... [VBA].TypeBackspace[/VBA] is the key! Thanks again man!

Greetings from Germany,

Matthias

[MOS MASTER]

Hehehehe...your welcome!