Need Help With Creating An OLE Link

[Gordon]

Hello again,

Sorry to repost this request, but I'm under a bit of pressure to find a
quick solution.

All I basically want is an automatically updating link (OLE, not DDE)
between a control in my application and a cell in an Excel spreadsheet. My
control has to automatically receive updates from the spreadsheet cell.

I already know how to access the spreadsheet via the Excel.Application class
but it's the linking of fields I'm having trouble with.

Anybody ?

Thanks,
Gordon.

----------------------------------------------------------------------------
---------------------------------------------------

I am quite new to VB.NET and I would greatly appreciate if anybody could
tell me how to go about creating an OLE link from a label object or text box
object on my Windows Form Application to a cell in an active Excel
spreadsheet.

Many thanks in advance.

Gordon.

 

[Gordon]

Hello again,

Sorry to repost this request, but I'm under a bit of pressure to find a
quick solution.

All I basically want is an automatically updating link (OLE, not DDE)
between a control in my application and a cell in an Excel spreadsheet. My
control has to automatically receive updates from the spreadsheet cell.

I already know how to access the spreadsheet via the Excel.Application class
but it's the linking of fields I'm having trouble with.

Anybody ?

Thanks,
Gordon.

-------------------------------------------------------------------------- --
---------------------------------------------------

I am quite new to VB.NET and I would greatly appreciate if anybody could
tell me how to go about creating an OLE link from a label object or text box
object on my Windows Form Application to a cell in an active Excel
spreadsheet.

Many thanks in advance.

Gordon.

 

[Herfried K. Wagner [MVP]]

* "Gordon" <[email protected]> scripsit:
[Nothing new]

If someone would have an answer, he/she would post it...

 

[Gordon]

Thanks for that, Herfried

OK, let's put it another way - how can I set up an event handler for when my
cell in Excel changes ? Here's the code I have so far :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim xlCell As Excel.Range

xlApp = New Excel.Application

xlBook = xlApp.Workbooks.Add

xlSheet = xlBook.Worksheets.Add

xlCell = xlSheet.Cells(1, 1)

xlApp.Visible = True

xlSheet.Cells(1, 1).Value = "Anything at all"

'Label1.text = xlCell.Value

xlSheet = Nothing

xlBook = Nothing

xlApp = Nothing

End Sub



So, can I either :

(1) set up an event handler for when xlCell changes

or

(2) somehow map Label1.text to xlCell so that Label1.Text isautomatically
updated from xlCell ?

I know I must be missing something very fundamental here , so apologies if
it is to whoever takes up the challenge.



Gordon.

 

[Herfried K. Wagner [MVP]]

OK, let's put it another way - how can I set up an event handler for when my
cell in Excel changes ? Here's the code I have so far :

Maybe this article helps:

Microsoft Knowledge Base Article - 302814
HOW TO: Handle Events for Excel by Using Visual Basic .NET
<http://support.microsoft.com/default.aspx?scid=kb;en-us;302814>

 

[Gordon]

Thanks Hefrried, that works great. Only one thing left to do :

How can I reference my object Label1 (created as Public WithEvents in
form1.vb) within the event handler xlSheet_Change ? When I try to reference
it, I get a message saying it is not declared. Obviously my scoping of the
variable must be out somehow.

e.g.

Module Module1

'==================================================================

'Demonstrates Using WithEvents for Event Handling

'==================================================================

Private xlApp As Excel.Application

Private xlBook As Excel.Workbook

Private WithEvents xlSheet As Excel.Worksheet

Public Sub LoadExcel()

'Start Excel and create a new workbook.

xlApp = CreateObject("Excel.Application")

xlBook = xlApp.Workbooks.Add()

xlBook.Windows(1).Caption = "Uses WithEvents"

'Get references to the worksheet.

xlSheet = xlBook.Worksheets.Item(1)

xlSheet.Activate()

'Make Excel visible and give the user control.

xlApp.Visible = True

xlApp.UserControl = True

End Sub

Private Sub xlSheet_Change(ByVal Target As Excel.Range) Handles
xlSheet.Change

label1.text = xlSheet.Cells(1, 1)
<---------------------------------------------------------------------------
---------------- This is where I get the message 'Label1 is not declared'

End Sub

End Module



Thanks,
Gordon.

 

[Herfried K. Wagner [MVP]]

How can I reference my object Label1 (created as Public WithEvents in
form1.vb) within the event handler xlSheet_Change ? When I try to reference
it, I get a message saying it is not declared. Obviously my scoping of the
variable must be out somehow.

e.g.

Module Module1

'==================================================================

'Demonstrates Using WithEvents for Event Handling

'==================================================================

Private xlApp As Excel.Application

Private xlBook As Excel.Workbook

Private WithEvents xlSheet As Excel.Worksheet

Public Sub LoadExcel()

'Start Excel and create a new workbook.

xlApp = CreateObject("Excel.Application")

xlBook = xlApp.Workbooks.Add()

xlBook.Windows(1).Caption = "Uses WithEvents"

'Get references to the worksheet.

xlSheet = xlBook.Worksheets.Item(1)

xlSheet.Activate()

'Make Excel visible and give the user control.

xlApp.Visible = True

xlApp.UserControl = True

End Sub

Private Sub xlSheet_Change(ByVal Target As Excel.Range) Handles
xlSheet.Change

label1.text = xlSheet.Cells(1, 1)
<---------------------------------------------------------------------------
---------------- This is where I get the message 'Label1 is not declared'

End Sub

End Module

Quick and Dirty:

Add a public variable of type 'Label' to your module and assign the
label to it (in the form). Then you can change the label's text by
using the variable in the module.

 

[Gordon]

And how do I assign the label in the module to the label in the form,
Herfried ?

Sorry to be a pain in the neck, but a little quick and dirty example would
do thetrick for me

Gordon.

 

[Herfried K. Wagner [MVP]]

And how do I assign the label in the module to the label in the form,
Herfried ?

You will have to do that in the form, for exampe its 'Load' event
handler (or any other place inside the form):

\\\
Module1.ResultLabel = Me.Label1
///

Sorry to be a pain in the neck, but a little quick and dirty example would
do thetrick for me

Notice that the solution is not "best practice", but it works.

 

[Gordon]

But I need to do it in MODULE1 in the event handler xlsheet_change(),
something like

me.label1.text = xlsheet.cells(1.1)

But, of course, 'me' isn't defined in MODULE1, so we're back to square one.