Hi!
I have an Excel spreadsheet with several DDE links.
Every Time DDE link is updated i would like to send these updates into Microsoft Access database and trigger
several calculations. To the best of my knowledge, Microsoft Access database does not have any triggers. So
I need the code to raise event every Time DDE link is updated.
The following macro continuously monitors DDE link updates and makes a beep noise every time update received.
I tried rewriting this code in VB.net and attach it to a button click event. Then from VB.net I can write
another subroutine to retrieve data from Excel, place it in the Microsoft Access, and perform my
calculations.
However, in VB.net that the code does not run continuously.
I have looked on the Internet only to find out that.net architecture does not support DDE anymore. I cannot
find any events associated with DDE link update that I can use in my VB.net code, or I'd take even C#
solution.
Can anyone suggest about how to make this code run every Time DDE link is updated, but without using timers?
HERE IS VBA MACRO - WORKS EXCELLENT TO DETECT DDE UPDATES
--------------------------------------
Public Sub UPDATE1()
Dim Links As Variant
' Obtain an array for the links to Excel workbooks
' in the active workbook.
Links = ActiveWorkbook.LinkSources(xlOLELinks)
' If the Links array is not empty, then open each
' linked workbook. If the array is empty, then
' display an error message.
If Not IsEmpty(Links) Then
For I = 1 To UBound(Links)
ActiveWorkbook.SetLinkOnData Links(I), "LinkChange"
Next I
Else
MsgBox "This workbook does not contain any links " & _
"to other workbooks"
End If
End Sub
Sub LinkChange()
Beep
End Sub
------------------------------------------
Here is VB.NET code - it has subs to test whether Excel is already running, and if not, it starts Excel and
retrieves data.
------------------
Option Strict Off
Imports System.Data.OleDb
Imports System.IO
Imports System.IO.FileStream
Imports Microsoft.Office.Core
Imports Excel
Public Class Form1
Inherits System.Windows.Forms.Form
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long
Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.DataGrid1 = New System.Windows.Forms.DataGrid()
Me.Label1 = New System.Windows.Forms.Label()
Me.Button1 = New System.Windows.Forms.Button()
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(16, 24)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(264, 200)
Me.DataGrid1.TabIndex = 0
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(16, 224)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(100, 16)
Me.Label1.TabIndex = 1
Me.Label1.Text = "Label1"
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(184, 256)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(88, 24)
Me.Button1.TabIndex = 2
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 285)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button1, Me.Label1, Me.DataGrid1})
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
Button1.Click
Dim freeFile As Integer
Dim excelApp As Excel.Application
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myFile As Object
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
Dim Links As Object
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application.
' If the application is not running, an error occurs.
myFile = GetObject(, "Excel.Application")
If Err().Number <> 0 Then ExcelWasNotRunning = True
Err().Clear() ' Clear Err object in case error occurred.
' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel()
' Set the object variable to reference the file you want to see.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''THIS IS VBA ADAPTED CODE''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
myFile = GetObject("d:\myprograms\onupdate1macro.xls!workbooks(1)")
myFile.application.visible = True
myWB = myFile.Workbooks(1)
myWS = myWB.Worksheets(1)
Links = myWB.LinkSources(Excel.XlLinkType.xlLinkTypeOLELinks)
If Not Links.Array.Null Then
Dim i
For i = 1 To UBound(Links)
myWB.SetLinkOnData(Links(i))
linkChanged()
Next i
Else : MsgBox("no links found")
End If
UpdateLinks()
End Sub
Sub UpdateLinks()
Me.Label1.Text = myWS.Range("a2").Value
End Sub
Sub linkChanged()
Beep()
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''END OF VBA ADAPTED CODE''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage(hWnd, WM_USER + 18, 0, 0)
End If
End Sub
End Class
I have an Excel spreadsheet with several DDE links.
Every Time DDE link is updated i would like to send these updates into Microsoft Access database and trigger
several calculations. To the best of my knowledge, Microsoft Access database does not have any triggers. So
I need the code to raise event every Time DDE link is updated.
The following macro continuously monitors DDE link updates and makes a beep noise every time update received.
I tried rewriting this code in VB.net and attach it to a button click event. Then from VB.net I can write
another subroutine to retrieve data from Excel, place it in the Microsoft Access, and perform my
calculations.
However, in VB.net that the code does not run continuously.
I have looked on the Internet only to find out that.net architecture does not support DDE anymore. I cannot
find any events associated with DDE link update that I can use in my VB.net code, or I'd take even C#
solution.
Can anyone suggest about how to make this code run every Time DDE link is updated, but without using timers?
HERE IS VBA MACRO - WORKS EXCELLENT TO DETECT DDE UPDATES
--------------------------------------
Public Sub UPDATE1()
Dim Links As Variant
' Obtain an array for the links to Excel workbooks
' in the active workbook.
Links = ActiveWorkbook.LinkSources(xlOLELinks)
' If the Links array is not empty, then open each
' linked workbook. If the array is empty, then
' display an error message.
If Not IsEmpty(Links) Then
For I = 1 To UBound(Links)
ActiveWorkbook.SetLinkOnData Links(I), "LinkChange"
Next I
Else
MsgBox "This workbook does not contain any links " & _
"to other workbooks"
End If
End Sub
Sub LinkChange()
Beep
End Sub
------------------------------------------
Here is VB.NET code - it has subs to test whether Excel is already running, and if not, it starts Excel and
retrieves data.
------------------
Option Strict Off
Imports System.Data.OleDb
Imports System.IO
Imports System.IO.FileStream
Imports Microsoft.Office.Core
Imports Excel
Public Class Form1
Inherits System.Windows.Forms.Form
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long
Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.DataGrid1 = New System.Windows.Forms.DataGrid()
Me.Label1 = New System.Windows.Forms.Label()
Me.Button1 = New System.Windows.Forms.Button()
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(16, 24)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(264, 200)
Me.DataGrid1.TabIndex = 0
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(16, 224)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(100, 16)
Me.Label1.TabIndex = 1
Me.Label1.Text = "Label1"
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(184, 256)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(88, 24)
Me.Button1.TabIndex = 2
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 285)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button1, Me.Label1, Me.DataGrid1})
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
Button1.Click
Dim freeFile As Integer
Dim excelApp As Excel.Application
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myFile As Object
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
Dim Links As Object
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application.
' If the application is not running, an error occurs.
myFile = GetObject(, "Excel.Application")
If Err().Number <> 0 Then ExcelWasNotRunning = True
Err().Clear() ' Clear Err object in case error occurred.
' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel()
' Set the object variable to reference the file you want to see.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''THIS IS VBA ADAPTED CODE''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
myFile = GetObject("d:\myprograms\onupdate1macro.xls!workbooks(1)")
myFile.application.visible = True
myWB = myFile.Workbooks(1)
myWS = myWB.Worksheets(1)
Links = myWB.LinkSources(Excel.XlLinkType.xlLinkTypeOLELinks)
If Not Links.Array.Null Then
Dim i
For i = 1 To UBound(Links)
myWB.SetLinkOnData(Links(i))
linkChanged()
Next i
Else : MsgBox("no links found")
End If
UpdateLinks()
End Sub
Sub UpdateLinks()
Me.Label1.Text = myWS.Range("a2").Value
End Sub
Sub linkChanged()
Beep()
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''END OF VBA ADAPTED CODE''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage(hWnd, WM_USER + 18, 0, 0)
End If
End Sub
End Class
