Kevin Southworth

Send a file from Excel to ASP.NET web service using VBA macro

Wednesday, January 24, 2007 @ 1:52 AM :: 1483 Views :: 2 Comments ::
Categories: .NET, Software Development

The other I had the challenge of figuring out how to add a toolbar button to a MS Excel file that would send the current file to an ASP.NET web service.  The web service could then take some action on the file, such as send it as an email attachment, or log it to a database.

You''ll need the following tools/downloads (in addition to MS Excel and Visual Studio 2003/2005)

Microsoft Office 2003 Web Services Toolkit

1. Create the ASP.NET Web Service to receive the file. I used VS 2005 to create my webservice, but VS 2003 should work fine also.  The key is to have the web method receive a byte[] array as a parameter, since that is what we will be passing from our client-side VBA code.
   

           [WebMethod]
           public void ReceiveExcelFileBytes(byte[] data)
           {
               Console.Write(data);
               Stream output = new FileStream(
                @"C:\WINDOWS\TEMP\data.xls",FileMode.Create);
               BinaryWriter outputWriter = new BinaryWriter(output);
               outputWriter.Write(data);
               outputWriter.Close();
               output.Close();
           }

2. The next step is to write our VBA code inside MS Excel. Create a new Excel file and open it. Open the Visual Basic Editor by pressing Alt-F11. Add a new "Module" and type in the code below:

   Dim sMenu As String
   
   Sub AddCustomButton()
   
       Dim oCB As CommandBar
       Dim oCtl As CommandBarControl
       Dim newMenu As Object ''CommandBarControl
       Dim ctrlButton As Object ''CommandBarControl
   
       sMenu = "Send to Webservice"
   
       On Error Resume Next
       Application.CommandBars("Formatting").Controls(sMenu).Delete
       On Error GoTo 0
   
       Set oCB = Application.CommandBars("Formatting")
       Set oCtl = oCB.Controls.Add(
             Type:=msoControlButton, temporary:=True)
   
       With oCtl
           .BeginGroup = True
           .Caption = sMenu
           .FaceId = 197
           .Style = msoButtonIconAndCaption
           .OnAction = "SaveToWebservice"
       End With
   
   End Sub
   

3. Next we need to hook-up our function to the Workbook_Open event. Double-click on "ThisWorkbook" in the VBA Editor and type in the following for the Workbook_Open event handler:

   Private Sub Workbook_Open()
       AddCustomButton
   End Sub
   

4. Make sure have installed the Office 2003 Web Service Toolkit, we''ll be using it to generate a VBA proxy for our ASP.NET webservice. Inside the VBA editor, go to "Tools -> Web Service References" menu and add a reference to the URL of your ASP.NET webservice that was created in step # 1 above. This will generate some VBA code that we can use to call our ASP.NET webservice from VBA.
   
   
5. Now we need to connect our custom toolbar button to some code that actually sends the file across the wire. Jump back to the VBA module (where we have our AddCustomButton() method, and add the following code:

   Sub SaveToWebservice()
   
   On Error GoTo Error_Handler
   
       Dim backupFile As String
       Dim fhandle As Long
       Dim webservice As New clsws_ExcelWebService
   
       backupFile = "C:\WINDOWS\TEMP\file_backup.xls"
   
       '' Save a backup copy to retain our VBA code
       ActiveWorkbook.SaveCopyAs backupFile
   
       Dim TypeBinary
       Dim oStream As ADODB.Stream
    
       '' Create the object
       Set oStream = CreateObject("ADODB.Stream")
    
       '' Open our file
       oStream.Open
       oStream.Type = adTypeBinary
       oStream.LoadFromFile backupFile
   
       '' Retreive binary data from the file
       Dim dataLong As Long
       Dim dataBytes As Variant
       Dim dataString As String
   
       dataBytes = oStream.Read
   
       '' call the webservice
       webservice.wsm_ReceiveExcelFileBytes (dataBytes)
   
       oStream.Close
   
       MsgBox ("Successfully sent")
   
       Exit Sub
   
   Error_Handler:
       MsgBox Err.Description &
             " (" & Err.Number & ")",
              vbOKOnly + vbCritical
       Exit Sub
   
   End Sub