HOWTO: Send Excel Workbook Object Using MSMQ in Visual Basic

• Microsoft Message Queue Server version 1.0
• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 5.0

SUMMARY

This article describes how to send and receive persistent objects, such as a Microsoft Excel workbook, with a Microsoft Message Queue Server (MSMQ) message.

MORE INFORMATION

The MSMQMessage.body property can be any intrinsic Variant, including string, date, currency, number as well as byte array and persistent object.

The ActiveX implementation in MSMQ supports sending and receiving serialized objects that support IPersistStream and IPersistStorage. There are many persistent objects, such as Microsoft Excel sheets, that you can send as MSMQ messages.

The MSMQ support for "persistent objects" means that the MSMQ ActiveX components seamlessly invoke the object's IPersist interface when sending that object as the message body. That is, the persistent state of the object is serialized into the message body, using the object's supplied IPersist interface. An implementation of the object's interface is assumed to be installed on the receiving end. For example, you can specify an Excel workbook as the message body, but you need to have Excel installed on the receiving end to use the workbook.

The following Visual Basic 5.0 sample code opens Excel, adds a new Excel workbook, inserts some values in the workbook, and then sends the workbook as a message. The receiver is able to get the workbook and read the data in it.

Step-by-Step Example

1. Create a new Standard EXE project.



2. From the Project menu, select References.



3. In the References dialog box, select the following references and then click OK:



• Microsoft Message Queue Object Library
• Microsoft Excel 8.0 Object Library


4. Add two command buttons to Form1.



5. Add the following code to Form1:




    Dim oQueueInfo As MSMQQueueInfo
    Dim oMessage As MSMQMessage
    Dim oQueue As MSMQQueue
    Dim oExcel As Excel.Application
    Dim oWorkBook As Excel.Workbook

    Private Sub Command1_Click()
      ' Send a message.
      Dim i As Integer
      Dim sRange As String
      Set oExcel = New Excel.Application
      Set oQueueInfo = New MSMQQueueInfo
      Set oMessage = New MSMQMessage
      Set oQueue = New MSMQQueue
      ' Add a WorkBook.
      oExcel.Workbooks.Add
      ' Place some data in the workbook.
      With oExcel.ActiveWorkbook.Worksheets("Sheet1")
         .Activate
         For i = 1 To 5 'Add data to the first 5 Cells
             sRange = "A" & Trim(Str(i))
            .Range(sRange).Value = i + 5
         Next i
      End With
      oQueueInfo.PathName = ".\WorkBook"
      oQueueInfo.Label = "Test queue"
      oQueueInfo.Create
      Set oQueue = oQueueInfo.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
      ' You do not need a SET to assign an Object to the message body.
      ' If you do use a SET, an "Object Required" error appears. 

      oMessage.Body = oExcel.ActiveWorkbook
      oMessage.Label = "message"
      oMessage.Send oQueue
      oQueue.Close
      ' Set the workbook Saved property to True so you can quit Excel
      ' without having to use a save dialog box.
      oExcel.ActiveWorkbook.Saved = True
      oExcel.Quit
      Set oExcel = Nothing
      Set oQueue = Nothing
      Set oQueueInfo = Nothing
      Set oMessage = Nothing
      MsgBox "Message Sent"
    End Sub

    Private Sub Command2_Click()
      ' Receive the message.
      Set oQueueInfo = New MSMQQueueInfo
      Set oMessage = New MSMQMessage
      Set oQueue = New MSMQQueue

      oQueueInfo.PathName = ".\WorkBook"
      Set oQueue = oQueueInfo.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)
      Set oMessage = oQueue.Receive(WantBody:=True, ReceiveTimeout:=1000)
      Set oWorkBook = oMessage.Body
      ' Excel should start when you get the message body.
      ' So you get a reference to the session of Excel that is running.
      Set oExcel = GetObject(, "Excel.Application")
      ' Make both Excel and the workbook visible.
      oExcel.Visible = True
      oWorkBook.Windows(1).Visible = True
      ' Delete the queue.
      oQueueInfo.Delete
    End Sub

    Private Sub Form_Load()
      Command1.Caption = "Send"
      Command2.Caption = "Receive"
    End Sub 

6. Save and run the project.

REFERENCES

MSMQ SDK Help; search for "Body" property.

Additional query words:

Keywords : kbole kbMSMQ100 kbVBp500
Version : WINDOWS:5.0; winnt:1.0
Platform : WINDOWS winnt
Issue type : kbhowto