Visual Basic Q&A

Symptoms
This article demonstrates how to create an Automation client for Microsoft Excel by using Microsoft Visual Basic .NET.
Resolution
Automation is a process that allows applications that are written in languages such as Visual Basic to programmatically control other applications. Automation to Excel allows you to perform actions such as creating a new workbook, adding data to the workbook, or creating charts. With Excel and other Microsoft Office applications, virtually all of the actions that you can perform manually through the user interface can also be performed programmatically by using Automation.
Excel exposes this programmatic functionality through an object model. The object model is a collection of classes and methods that serve as counterparts to the logical components of Excel. For example, there is an Application object, a Workbook object, and a Worksheet object, each of which contain the functionality of those components of Excel. To access the object model from Visual Basic .NET, you can set a project reference to the type library.
This article demonstrates how to set the proper project reference to the Excel type library for Visual Basic .NET and provides sample code to automate Excel. Create an automation client for Microsoft ExcelStart Microsoft Visual Studio .NET.On the File menu, click New, and then click Project. Select Windows Application from the Visual Basic Project types. Form1 is created by default.Add a reference to Microsoft Excel Object Library. To do this, follow these steps: On the Project menu, click Add Reference.On the COM tab, locate Microsoft Excel Object Library, and then click Select.
Note Microsoft Office 2003 includes Primary Interop Assemblies (PIAs). Microsoft Office XP does not include PIAs, but they can be downloaded. For more information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base:
328912?(http://support.microsoft.com/kb/328912/) Microsoft Office XP primary interop assemblies (PIAs) are available for downloadClick OK in the Add References dialog box to accept your selections.On the View menu, select Toolbox to display the Toolbox, and then add a button to Form1.Double-click Button1. The code window for the form appears.In the code window, locate the following code:

Private Sub Button1_Click(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles Button1.ClickEnd Sub Replace the previous code with the following code:

Private Sub Button1_Click(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles Button1.ClickDim oXL As Excel.ApplicationDim oWB As Excel.WorkbookDim oSheet As Excel.WorksheetDim oRng As Excel.Range’ Start Excel and get Application object.oXL = CreateObject(“Excel.Application”)oXL.Visible = True’ Get a new workbook.oWB = oXL.Workbooks.AddoSheet = oWB.ActiveSheet’ Add table headers going cell by cell.oSheet.Cells(1, 1).Value = “First Name”oSheet.Cells(1, 2).Value = “Last Name”oSheet.Cells(1, 3).Value = “Full Name”oSheet.Cells(1, 4).Value = “Salary”‘ Format A1:D1 as bold, vertical alignment = center.With oSheet.Range(“A1″, “D1″).Font.Bold = True.VerticalAlignment = Excel.XlVAlign.xlVAlignCenterEnd With’ Create an array to set multiple values at once.Dim saNames(5, 2) As StringsaNames(0, 0) = “John”saNames(0, 1) = “Smith”saNames(1, 0) = “Tom”saNames(1, 1) = “Brown”saNames(2, 0) = “Sue”saNames(2, 1) = “Thomas”saNames(3, 0) = “Jane”saNames(3, 1) = “Jones”saNames(4, 0) = “Adam”saNames(4, 1) = “Johnson”‘ Fill A2:B6 with an array of values (First and Last Names).oSheet.Range(“A2″, “B6″).Value = saNames’ Fill C2:C6 with a relative formula (=A2 & ” ” & B2).oRng = oSheet.Range(“C2″, “C6″)oRng.Formula = “=A2 & “” “” & B2″‘ Fill D2:D6 with a formula(=RAND()*100000) and apply format.oRng = oSheet.Range(“D2″, “D6″)oRng.Formula = “=RAND()*100000″oRng.NumberFormat = “$0.00″‘ AutoFit columns A:D.oRng = oSheet.Range(“A1″, “D1″)oRng.EntireColumn.AutoFit()’ Manipulate a variable number of columns for Quarterly Sales Data.Call DisplayQuarterlySales(oSheet)’ Make sure Excel is visible and give the user control’ of Excel’s lifetime.oXL.Visible = TrueoXL.UserControl = True’ Make sure that you release object references.oRng = NothingoSheet = NothingoWB = NothingoXL.Quit()oXL = NothingExit SubErr_Handler:MsgBox(Err.Description, vbCritical, “Error: ” & Err.Number)End SubPrivate Sub DisplayQuarterlySales(ByVal oWS As Excel.Worksheet)Dim oResizeRange As Excel.RangeDim oChart As Excel.ChartDim oSeries As Excel.SeriesDim iNumQtrs As IntegerDim sMsg As StringDim iRet As Integer’ Determine how many quarters to display data for.For iNumQtrs = 4 To 2 Step -1sMsg = “Enter sales data for” & Str(iNumQtrs) & ” quarter(s)?”iRet = MsgBox(sMsg, vbYesNo Or vbQuestion _Or vbMsgBoxSetForeground, “Quarterly Sales”)If iRet = vbYes Then Exit ForNext iNumQtrs’ Starting at E1, fill headers for the number of columns selected.oResizeRange = oWS.Range(“E1″, “E1″).Resize(ColumnSize:=iNumQtrs)oResizeRange.Formula = “=”"Q”" & COLUMN()-4 & CHAR(10) & “”Sales”"”‘ Change the Orientation and WrapText properties for the headers.oResizeRange.Orientation = 38oResizeRange.WrapText = True’ Fill the interior color of the headers.oResizeRange.Interior.ColorIndex = 36′ Fill the columns with a formula and apply a number format.oResizeRange = oWS.Range(“E2″, “E6″).Resize(ColumnSize:=iNumQtrs)oResizeRange.Formula = “=RAND()*100″oResizeRange.NumberFormat = “$0.00″‘ Apply borders to the Sales data and headers.oResizeRange = oWS.Range(“E1″, “E6″).Resize(ColumnSize:=iNumQtrs)oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin’ Add a Totals formula for the sales data and apply a border.oResizeRange = oWS.Range(“E8″, “E8″).Resize(ColumnSize:=iNumQtrs)oResizeRange.Formula = “=SUM(E2:E6)”With oResizeRange.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlDouble.Weight = Excel.XlBorderWeight.xlThickEnd With’ Add a Chart for the selected data.oResizeRange = oWS.Range(“E2:E6″).Resize(ColumnSize:=iNumQtrs)oChart = oWS.Parent.Charts.AddWith oChart.ChartWizard(oResizeRange, Excel.XlChartType.xl3DColumn, , Excel.XlRowCol.xlColumns)oSeries = .SeriesCollection(1)oSeries.XValues = oWS.Range(“A2″, “A6″)For iRet = 1 To iNumQtrs.SeriesCollection(iRet).Name = “=”"Q” & Str(iRet) & “”"”Next iRet.Location(Excel.XlChartLocation.xlLocationAsObject, oWS.Name)End With’ Move the chart so as not to cover your data.With oWS.Shapes.Item(“Chart 1″).Top = oWS.Rows(10).Top.Left = oWS.Columns(2).LeftEnd With’ Free any references.oChart = NothingoResizeRange = NothingEnd Sub Add the following code to the top of Form1.vb:

Imports Microsoft.Office.Core Test the automation clientPress F5 to build and to run the program.On the form, click Button1. The program starts Excel and populates data on a new worksheet.When you are prompted to enter quarterly sales data, click Yes. A chart that is linked to quarterly data is added to the worksheet.

Symptoms
This article demonstrates how to automate Microsoft Excel and how to fill a multi-cell range with an array of values. This article also illustrates how to retrieve a multi-cell range as an array by using Automation.
Resolution
To fill a multi-cell range without populating cells one at a time, you can set the Value property of a Range object to a two-dimensional array. Likewise, a two-dimensional array of values can be retrieved for multiple cells at once by using the Value property. The following steps demonstrate this process for both setting and retrieving data using two-dimensional arrays. Build the Automation Client for Microsoft ExcelStart Microsoft Visual Studio .NET.On the File menu, click New, and then click Project. Select Windows Application from the Visual Basic Project types. By default, Form1 is created.Add a reference to Microsoft Excel Object Library. To do this, follow these steps: On the Project menu, click Add Reference.On the COM tab, locate Microsoft ExcelObject Library, and then click Select.
Note Microsoft Office 2007 and Microsoft Office 2003 include Primary Interop Assemblies (PIAs). Microsoft Office XP does not include PIAs, but they can be downloaded.
For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:
328912?(http://support.microsoft.com/kb/328912/EN-US/) INFO: Microsoft Office XP PIAs Are Available for DownloadClick OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click Yes.On the View menu, select Toolbox to display the Toolbox. Add two buttons and a check box to Form1.Set the Name property for the check box to FillWithStrings.Double-click Button1. The code window for the Form appears.Add the following to the top of Form1.vb:

Imports Microsoft.Office.Interop In the code window, replace the following code

Private Sub Button1_Click(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles Button1.ClickEnd Sub with:

‘Keep the application object and the workbook object global, so you can’retrieve the data in Button2_Click that was set in Button1_Click.Dim objApp As Excel.ApplicationDim objBook As Excel._WorkbookPrivate Sub Button1_Click(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles Button1.ClickDim objBooks As Excel.WorkbooksDim objSheets As Excel.SheetsDim objSheet As Excel._WorksheetDim range As Excel.Range’ Create a new instance of Excel and start a new workbook.objApp = New Excel.Application()objBooks = objApp.WorkbooksobjBook = objBooks.AddobjSheets = objBook.WorksheetsobjSheet = objSheets(1)’Get the range where the starting cell has the address’m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.range = objSheet.Range(“A1″, Reflection.Missing.Value)range = range.Resize(5, 5)If (Me.FillWithStrings.Checked = False) Then’Create an array.Dim saRet(5, 5) As Double’Fill the array.Dim iRow As LongDim iCol As LongFor iRow = 0 To 5For iCol = 0 To 5′Put a counter in the cell.saRet(iRow, iCol) = iRow * iColNext iColNext iRow’Set the range value to the array.range.Value = saRetElse’Create an array.Dim saRet(5, 5) As String‘Fill the array.Dim iRow As LongDim iCol As LongFor iRow = 0 To 5For iCol = 0 To 5′Put the row and column address in the cell.saRet(iRow, iCol) = iRow.ToString() + “|” + iCol.ToString()Next iColNext iRow’Set the range value to the array.range.Value = saRetEnd If’Return control of Excel to the user.objApp.Visible = TrueobjApp.UserControl = True’Clean up a little.range = NothingobjSheet = NothingobjSheets = NothingobjBooks = NothingEnd Sub Return to the design view for Form1, and then double-click Button2.In the code window, replace the following code

Private Sub Button2_Click(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles Button2.ClickEnd Sub with:

Private Sub Button2_Click(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles Button2.ClickDim objSheets As Excel.SheetsDim objSheet As Excel._WorksheetDim range As Excel.Range’Get a reference to the first sheet of the workbook.On Error Goto ExcelNotRunningobjSheets = objBook.WorksheetsobjSheet = objSheets(1)ExcelNotRunning:If (Not (Err.Number = 0)) ThenMessageBox.Show(“Cannot find the Excel workbook.Try clicking Button1 to ” + _”create an Excel workbook with data before running Button2.”, _”Missing Workbook?”)’We cannot automate Excel if we cannot find the data we created,’so leave the subroutine.Exit SubEnd If’Get a range of data.range = objSheet.Range(“A1″, “E5″)’Retrieve the data from the range.Dim saRet(,) As ObjectsaRet = range.Value’Determine the dimensions of the array.Dim iRows As LongDim iCols As LongiRows = saRet.GetUpperBound(0)iCols = saRet.GetUpperBound(1)’Build a string that contains the data of the array.Dim valueString As StringvalueString = “Array Data” + vbCrLfDim rowCounter As LongDim colCounter As LongFor rowCounter = 1 To iRowsFor colCounter = 1 To iCols’Write the next value into the string.valueString = String.Concat(valueString, _saRet(rowCounter, colCounter).ToString() + “, “)Next colCounter’Write in a new line.valueString = String.Concat(valueString, vbCrLf)Next rowCounter’Report the value of the array.MessageBox.Show(valueString, “Array Values”)’Clean up a little.range = NothingobjSheet = NothingobjSheets = NothingEnd Sub Test the Automation ClientPress F5 to build and to run the sample program.Click Button1. Microsoft Excel is started with a new workbook, and cells A1:E5 of the first worksheet are populated with numeric data from an array.Click Button2. The program retrieves the data in cells A1:E5 into a new array and displays the results in a message box.Select FillWithStrings, and then click Button1 to fill cells A1:E5 with the string data.

Symptoms
In an MFC application, AfxThrowOleDispatchException is used to throw anexception from within an OLE Automation function. When Microsoft VisualBasic is used as the automation client application, it will not be able tocorrectly interpret the error code passed to AfxThrowOleDispatchException.
Resolution
AfxThrowOleDispatchException constructs and throws a COleDispatchExceptionobject, which is used to handle exceptions specific to the OLE IDispatchinterface. The constructor of COleDispatchException incorrectly initializesthe COleDispatchException object, resulting in the Visual Basic clientbeing unable to interpret the error code of the exception.