assigning selection.paste to variable

[richATrichardjones]

Hello all,
Thanks for your help on my last post!

I have opened a particular workbook, copied a selection from it and am able to paste it into word.
However can I assign the value to a vba variable?
Ideally in the end I want five cell values in excel to be in a word combo box. But if anyone could help me with how to assign a selection.paste to a variable it would be great!

I have searched this forum and cant really find the answer to this. I might be looking for the wrong things so apologies if this question is a repeat!

Thanks
Richard

 

[fumei]

Dim mySelection As String

I seems to recall your post, but you didn't put the thread number, so duh...I can't remember the details.

But something like

someCell.Select
mySelection = Selection.Range.Text
combobox1.additem mySelection

then loop back to your cells, and reselect. Again, I can't remember the details, but it will be something like the above.

Gerry

 

[richATrichardjones]

Hi Gerry,
thanks for the help.
D'oh! Sorry about not linking my old thread. It is here:

http://www.tek-tips.com/viewthread.cfm?qid=928516

I'll let you know how I get on

Thanks again

Richard

 

[fumei]

Hi Richard,

Could you post the code you are now using?

I suspect that you could likely change from making selections/pastes to using ranges/paste (this is better). And absolutely yes, the cell contents can be put into a variable and used to add to a combobox.

Gerry

 

[richATrichardjones]

HI Gerry
I have tried what you said and it doesnt quite work.
My code is

=========================================
Private Sub CommandButton1_Click()

Dim xlApp As Object
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True

xlApp.workbooks.Open "C:\richard.xls"

xlApp.Range("B5").Select
'xlApp.Selection.Copy

mySelection = Selection.Range.Text

MsgBox (mySelection)

xlApp.Quit
Set xlApp = Nothing

End Sub
=========================================

I have tried the selection.copy commented and included and it doesnt work. I seem to be able to get something into the message box but it is just a square. it is not the value of the cell B5 in the excel sheet
I presume there is a compatability problem

Any help/advice is appreciated

Cheers
Richard

 

[fumei]

Try using Selection.Value, not Text.

Gerry

 

[richATrichardjones]

Hi Gerry
I tried selection.value but value is not a member of selection.
Richard

 

[TonyJollans]

Hi Richard,

I think you are using the Word Selection instead of the Excel Selection. Try ...

:
:
xlApp.Range("B5").Select
'xlApp.Selection.Copy

mySelection = xlApp.Selection

MsgBox (mySelection)
:
:

Of course it would beter not to bother Selecting at all, but one step at a time.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.