.NET Framework 3.5 and MS Office 2007 VBA

kucingblue

ok, here's the thing. Aku dah lebih 8 bulan takde keje kat office. so bosan bosan, aku start programming.

aku nak design satu program yg hantar short message to Excel file aku. The Excel file has a built-in VBA codes that automatically run when the workbook is opened. I wrote the VBA so I can change it, I just don't want to rebuild it.

So, untuk IPC, aku decided on using Memory Mapped File.

External program tu aku buat pakai VB.net 3.5. Terpaksa pakai 3.5 sebab tak sume orang dalam company aku ade .net 4 and tak leh nak install sendiri.

Bile aku buat external program tu pakai .net 4, no problem. I can communicate with my Excel VBA. .Net 4 memang dah ade built-in functions utk MMF. But .Net 3.5 kena import kernel32 manually.

So, ade sesape kat sini penah pakai .Net 3.5 utk buat MMF? Kalo ade tolong tunjuk ajar. I have no problem creating the MMF, but when it comes to CopyMemory (RtlMoveMemory) aku pening sket. Puas aku search online.

Or kalo ade suggestion protocol lain selain MMF, please share. Thanks.

p/s: I'm a Mechanical Engineer. So my programming knowledge is quite limited.

kucingblue

nvm. I've figured it out.

webxcrawler

nvm. I've figured it out.
kucingblue Post at 14-8-2010 06:16

berminat nak tau. leh berkongsi? huhuhu

kucingblue

Reply 3# webxcrawler

sure.

so basically cammana nak external program to kasi message kat excel vba punya code.

download example file kat sini.
http://steekr.com/n/50-17/share/LNK53424c6f5a9353394/
dalam tu ade transmitter.exe and receiverc.xlsm.

open excel file tu. enable macro.
run transmitter.exe. pastu tulis short message kat textbox, and tekan button .net4 or .net3.5 or both.
back to excel file tu, tekan one of the buttons. you'll see a message box showing the message received from the transmitter.exe.

that's a short demonstration of how it works.

code dia plak camni.

untuk transmitter.exe
aku compile pakai Visual Basic Express (it's a free program by Microsoft, light version of Visual Basic Studio 10 kot.).

Public Class Form1

    Dim mmf As MemoryMappedFile = MemoryMappedFile.CreateNew("Peta4", 16)   'untuk .Net4 method. Peta4 ni nama memory-mapped file untuk method ni.




'/////////////////////////declaration untuk .net3.5 method//////////////////////////////////////


    Const PAGE_READONLY As Integer = &H2
    Const PAGE_READWRITE As Integer = &H4
    Const PAGE_WRITECOPY As Integer = &H8

    Const FILE_MAP_COPY As Integer = 1
    Const FILE_MAP_WRITE As Integer = 2
    Const FILE_MAP_READ As Integer = 4
    Const FILE_MAP_ALL_ACCESS As Integer = FILE_MAP_WRITE

    Const INVALID_HANDLE_VALUE As Integer = -1



    Private Declare Function OpenFileMapping Lib "kernel32" Alias "OpenFileMappingA" ( _
        ByVal dwDesiredAccess As Integer, _
        ByVal bInheritHandle As Integer, _
        ByVal lpName As String) As Integer

    Private Declare Function CreateFileMapping Lib "kernel32" Alias "CreateFileMappingA" ( _
        ByVal hFileMapTable As Integer, _
        ByVal lpFileMappingAttributes As Integer, _
        ByVal flProtect As Integer, _
        ByVal dwMaximumSizeHigh As Integer, _
        ByVal dwMaximumSizeLow As Integer, _
        ByVal lpName As String) As Integer

    Private Declare Function MapViewOfFile Lib "kernel32" ( _
        ByVal hFileMapTableMappingObject As Integer, _
        ByVal dwDesiredAccess As Integer, _
        ByVal dwFileOffsetHigh As Integer, _
        ByVal dwFileOffsetLow As Integer, _
        ByVal dwNumberOfBytesToMap As Integer) As Integer

    Private Declare Function UnmapViewOfFile Lib "kernel32" ( _
        ByVal lpBaseAddress As Integer) As Integer

    Private Declare Function CloseHandle Lib "kernel32" ( _
        ByVal hObject As Integer) As Integer

    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
    ByVal Dst As Integer, _
    ByVal pSrc As Byte(), _
    ByVal ByteLen As Integer)

'/////////////////////////////////////////////////////////////////////////////////////////////////////////




    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'label button-button
        Button1.Text = "Transmit" & vbNewLine & ".Net 4"
        Button3.Text = "Transmit" & vbNewLine & ".Net 3.5"

    End Sub



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

        '.Net 4 method


        Dim stream As MemoryMappedViewStream = mmf.CreateViewStream

        Dim writer As BinaryWriter = New BinaryWriter(stream) 'first 4 bytes binarywriter contains the length of the string

        writer.Write(TextBox1.Text)

        stream = mmf.CreateViewStream



    End Sub






    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        '.Net 3.5 method


        Dim handle As Integer, hPtr As Integer

        handle = CreateFileMapping(INVALID_HANDLE_VALUE, 0, PAGE_READWRITE, 0, 4096, "Peta3")  'Peta3 ni nama memory-mapped file untuk method .net 3.5

        hPtr = MapViewOfFile(handle, FILE_MAP_ALL_ACCESS, 0, 0, 0)



        Dim utusan(3) As Byte
        Dim i As Integer
        i = Len(TextBox1.Text)            'kira panjang message ni
        utusan = BitConverter.GetBytes(i)         'convert to bytes

        Dim bytearray() As Byte

        bytearray = StrToByteArray(TextBox1.Text)         'convert message to byte array

        ReDim Preserve utusan(UBound(bytearray) + 4)        'resize array utusan

        Array.Copy(bytearray, 0, utusan, 4, UBound(bytearray) + 1)   'combine message dari bytearray into utusan

        CopyMemory(hPtr, utusan, UBound(utusan) + 1)


    End Sub



    Public Shared Function StrToByteArray(ByVal str As String) As Byte()
        Dim encoding As New System.Text.UTF8Encoding()
        Return encoding.GetBytes(str)
    End Function



End Class

bersambung....

kucingblue

code untuk receiver. code ni untuk terima message dari .Net 4 punya method.
code ni aku tulis dalam excel punya macro module.

unlike .Net 4, Excel 2007 VBA takde built-in support for Memory-Mapped Files. So kena import kernel32 punya functions manually.

Option Explicit

Private Const PAGE_READONLY As Long = &H2
Private Const PAGE_READWRITE As Long = &H4
Private Const PAGE_WRITECOPY As Long = &H8

Private Const FILE_MAP_COPY As Long = 1
Private Const FILE_MAP_WRITE As Long = 2
Private Const FILE_MAP_READ As Long = 4
Private Const FILE_MAP_ALL_ACCESS As Long = FILE_MAP_WRITE

Private Const INVALID_HANDLE_VALUE As Long = -1

'//////////////////////////////////////////////////

Private Declare Function OpenFileMapping Lib "kernel32" Alias "OpenFileMappingA" ( _
    ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal lpName As String) As Long

Private Declare Function MapViewOfFile Lib "kernel32" ( _
    ByVal hFileMapTableMappingObject As Long, _
    ByVal dwDesiredAccess As Long, _
    ByVal dwFileOffsetHigh As Long, _
    ByVal dwFileOffsetLow As Long, _
    ByVal dwNumberOfBytesToMap As Long) As Long

Private Declare Function UnmapViewOfFile Lib "kernel32" ( _
    lpBaseAddress As Any) As Long

Private Declare Function CloseHandle Lib "kernel32" ( _
    ByVal hObject As Long) As Long


Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef dest As Any, _
     ByVal source As Long, ByVal bytes As Long)





Sub dotNet4()


    Dim handle As Long
    Dim hptr As Long
   
   
    handle = OpenFileMapping(FILE_MAP_READ, False, "Peta4")
    hptr = MapViewOfFile(handle, FILE_MAP_READ, 0, 0, 0)

    Dim sLen As Integer

    Dim barray() As Byte
   

    If hptr <> 0 Then
        Dim tali As String
        
        CopyMemory sLen, hptr, 1 'read string length

        ReDim barray(0 To sLen - 1)

        CopyMemory ByVal VarPtr(barray(0)), hptr + 1, sLen
            
        tali = ByteArrayToString(barray)
            
    Else      'kalau hptr=0 maknanya memory-mapped file tu tak jumpe
        MsgBox ("No transmission received")
   
    End If
   
   
    UnmapViewOfFile (hptr)
   
    CloseHandle (handle)
   


    MsgBox ("Message received from .Net 4 transmission: " & vbNewLine & tali)



End Sub


Public Function ByteArrayToString(bytArray() As Byte) As String
    Dim sAns As String
    Dim iPos As String
   
    sAns = StrConv(bytArray, vbUnicode)
    iPos = InStr(sAns, Chr(0))
    If iPos > 0 Then sAns = Left(sAns, iPos - 1)
   
    ByteArrayToString = sAns

End Function

kucingblue

ni code untuk terima message dari .Net 3.5 punya method.

Option Explicit

Private Const PAGE_READONLY As Long = &H2
Private Const PAGE_READWRITE As Long = &H4
Private Const PAGE_WRITECOPY As Long = &H8

Private Const FILE_MAP_COPY As Long = 1
Private Const FILE_MAP_WRITE As Long = 2
Private Const FILE_MAP_READ As Long = 4
Private Const FILE_MAP_ALL_ACCESS As Long = FILE_MAP_WRITE

Private Const INVALID_HANDLE_VALUE As Long = -1

'//////////////////////////////////////////////////

Private Declare Function OpenFileMapping Lib "kernel32" Alias "OpenFileMappingA" ( _
    ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal lpName As String) As Long


Private Declare Function MapViewOfFile Lib "kernel32" ( _
    ByVal hFileMapTableMappingObject As Long, _
    ByVal dwDesiredAccess As Long, _
    ByVal dwFileOffsetHigh As Long, _
    ByVal dwFileOffsetLow As Long, _
    ByVal dwNumberOfBytesToMap As Long) As Long

Private Declare Function UnmapViewOfFile Lib "kernel32" ( _
    lpBaseAddress As Any) As Long

Private Declare Function CloseHandle Lib "kernel32" ( _
    ByVal hObject As Long) As Long


Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef dest As Any, _
     ByVal source As Any, ByVal bytes As Long)
     


Sub dotNet3point5()


    Dim handle As Long
    Dim hptr As Long
   
   
    handle = OpenFileMapping(FILE_MAP_READ, False, "Peta3")
    hptr = MapViewOfFile(handle, FILE_MAP_READ, 0, 0, 0)


    Dim sLen As Integer
    Dim slen2(3) As Byte
   
    Dim barray() As Byte
   
   
    If hptr <> 0 Then
        Dim tali As String
        
        CopyMemory ByVal VarPtr(slen2(0)), hptr, 4 'read string length
        sLen = slen2(0) + (slen2(1) * 16 ^ 2) + (slen2(2) * 16 ^ 3 + (slen2(3) * 16 ^ 4))
        
        
        ReDim barray(0 To 2 * sLen)

        

            CopyMemory ByVal VarPtr(barray(0)), hptr + 4, sLen
            
            tali = ByteArrayToString(barray)
            
    Else
        MsgBox ("No transmission received")
   
    End If
   
    UnmapViewOfFile (hptr)
   
    CloseHandle (handle)
   


MsgBox ("Message received from .Net 3.5 transmission: " & vbNewLine & tali)
   

   
End Sub






Public Function ByteArrayToString(bytArray() As Byte) As String
    Dim sAns As String
    Dim iPos As String
   
    sAns = StrConv(bytArray, vbUnicode)
    iPos = InStr(sAns, Chr(0))
    If iPos > 0 Then sAns = Left(sAns, iPos - 1)
   
    ByteArrayToString = sAns

End Function

kucingblue

so there. I managed to make my external program talk to my excel macro by sharing a memory.
it took me a couple of weeks to figure this out. the problem is Excel punya VBA kalo silap sket jek pakai function CopyMemory tu trus crash. tu pasal la makan masa. lagi satu i think VBA 2007 is like the old version of Visual Basic. Visual Basic .Net cam lain sket. so confused gak la.