Microsoft KB Archive/200513

{|
 * width="100%"|

HOWTO: Send Excel Workbook Object Using MSMQ in Visual Basic

 * }

Q200513

-

The information in this article applies to:


 * Microsoft Message Queue Server (MSMQ) version 1.0
 * Microsoft Visual Basic Learning Edition for Windows, version 5.0
 * Microsoft Visual Basic Professional Edition for Windows, version 5.0
 * Microsoft Visual Basic Enterprise Edition 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
Create a new Standard EXE project.

From the Project menu, select References.

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


 * Microsoft Message Queue Object Library
 * Microsoft Excel 8.0 Object Library

Add two command buttons to Form1.

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 Save and run the project.