Can you get excel range object off the clipboard?

[Brady]

I am working in Visual Studio 2005 VB. I have a windows.forms application
where I want to retrieve data from excel via the clipboard and insert it into
my application. I was trying to find out if there was away to get a range
object off of the clipboard so that you could use the range object to
retrieve the data and insert it into the application. At this point I am
simply pulling a string off the clipboard which delimits rows with newlines
and fields with tabs. The problem I have is that it's not unlikely that
some of the cells will have a tab character in them which screws up the
tokenization. I was hoping to get a range object which would alivate this
problem.

 

[NickHK]

Not being a .Net person, correct me if I'm wrong, but I understood you use
VSTO etc to automate Office apps.
As such you have access to Excel's object directly.
In VB I'd do something like:
Dim XL as Excel.application
dim WB as Excel.workbook
dim MyRange as Excel.range

set xlapp=new excel.application
set wb=xlapp.workbooks.add
set myrange=wb.worksheets(1).range("A1")
msgbox myrange.value

Unless I'm missing something (which is quite likely), why do you need the
clipboard ?

NickHK

 

[LFCFan]

Hi Brady,

Could you not pass the cell's address to the clipboard?

Set cData = New DataObject
cData.SetText (ActiveCell.Address)
cData.PutInClipboard

Not sure if this is what you mean?

HTH
Joe

 

[Brady]

I believe that I need the clipboard because I don't know what cells in the
excell spreadsheet the user is going to want to paste into my application.
The user needs to be able to select a range of cells. I then grab that range
and insert into my grid. I can attach to excel as you say below but I would
need a way for the user to select cells. However, you may be on to
something here. I might be able to connect to excel as you have specified
and get the currently selected range. I'll try that. It seems like it
would be more natual to do this through the clibboard. But I'll give this a
try.

 

[NickHK]

In VB I'd do something like:

MsgBox "User: Select a Range"
'User select a range of cells
If TypeName(XLApp.Selection)="Range" Then
MsgBox "You selected " & XLApp.Selection.Address & " on worksheet " &
XLApp.Selection.Parent.Name
End If

NickHK

 

[Brady]

No I don't think so because I am not control/writing the excel piece just the
windows form piece.

 

[Brady]

The automation worked. The code below is what I ended up using and it seems
to work fine. The only problem is that it takes a while for the getobject
method to respond. If I could do this through the clipboard the response
would be much faster I believe.

Brady
Sub PasteViaExcelAutomation()
Dim e As Microsoft.Office.Interop.Excel.Application
Dim r, c As Microsoft.Office.Interop.Excel.Range



Dim i, j, fcount As Integer
Dim x As New System.Collections.Specialized.NameValueCollection


Try
e = GetObject(, "Excel.Application")
r = e.Selection
......

 

[NickHK]

That does seems of the problem generally for .Net and I would imagine the
extra layers involved with all that Interop doesn't help.
But once you get your reference to Excel, further calls should be faster,
allowing for all the marshalling.

NickHK