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
When you attempt to disconnect an ADO Recordset by setting its ActiveConnection Object property to Nothing, the following error message might appear:

Run-time error ‘3705′: Operation is not allowed when the object is open.
Resolution
Only client-side ADO recordsets can be disconnected. The specified error occurs only when you attempt to disconnect a server-side ADO recordset.

Symptoms
When you try to create an instance of a PropertyItem object in a project that you try to compile, you receive one of the following error messages:
Visual Basic .NET Error

‘System.Drawing.Imaging.PropertyItem.Private Overloads Sub New()’ is not accessible in this context because it is ‘Private’.Visual Basic 2005 Error

error BC30251: Type ‘System.Drawing.Imaging.PropertyItem’ has no constructors.Visual C# .NET Error

‘System.Drawing.Imaging.PropertyItem.PropertyItem()’ is inaccessible due to its protection level.Visual C# 2005 Error

error CS0143: The type ‘System.Drawing.Imaging.PropertyItem’ has no constructors defined
Resolution
A PropertyItem object encapsulates a metadata property to be included in an image file. A PropertyItem object is not intended to be used a stand-alone object. A PropertyItem object is intended to be used by classes that are derived from System.Drawing.Image. A PropertyItem object is used to retrieve and change the metadata of existing image files, not to create the metadata. Therefore, the PropertyItem class does not have a defined Public constructor, and you cannot create an instance of a PropertyItem object.

Symptoms
To make your data appear on a form, you bind a Microsoft Windows Forms DataGrid control to a DataTable object that is included in a DataSet object.
After your data has appeared in the DataGrid control, you use the Tables.Clear method to remove the DataTable object from the Tables collection of the bound DataSet object. However, although the bound DataSet object no longer contains any data, your original data continues to appear in the DataGrid control.
Resolution
In a Microsoft Windows-based application, the CurrencyManager object manages a list of Binding objects. When you use a DataSet object together with a DataTable object for your data source, and you bind a DataGrid control to the DataSet object and the DataTable object, the CurrencyManager object manages this data source.
However, if you remove the DataTable object from the DataSet object, the related the CurrencyManager object that corresponds to the DataSet object is not updated. Therefore, the DataGrid control does not have this new information and the original data continues to appear in the DataGrid control.

Symptoms
When you try to place a user control that was created using the Data ObjectWizard onto a form, Error 424 (“Object Required”) occurs.
Resolution
When you place a user control on a form, that control’s Resize event isexecuted. If you create a list box or combo box type of user control usingthe Data Object Wizard, that control’s Resize event refers to the containedlist box or combo box incorrectly and causes error 424, “Object required.”

Symptoms
The Microsoft Data Object Libraries may be referenced in your Visual Basic project. When the Package and Deployment Wizard (PDW) is used to build the installation, the PDW scans the project files and determines which dependency files need to be distributed.
This article provides the general information you need to distribute any of the following Data Object Libraries with your Visual Basic project: Microsoft Data Access Objects (DAO) 3.51 Object LibraryMicrosoft Data Access Objects (DAO) 3.6 Object LibraryMicrosoft Remote Data Object (RDO) 2.0Microsoft ActiveX Data Objects (ADO) 2.0, 2.1, 2.5, or 2.6 Library
Resolution
The files are listed in the Setup’s file that is created by the PDW for distribution.
For additional information on how each section in the Setup.lst file is used, click the following article number to view the article in the Microsoft Knowledge Base:
189743?(http://support.microsoft.com/kb/189743/) Description of Setup.lst sections The following sections describe points to be aware of when you distribute any of the Data Object Libraries.
Microsoft Data Access Objects (DAO) 3.51 Object LibraryIntrinsic data control incompatibility with Microsoft Access 2000 (Jet 4.0) If you use the intrinsic data control in your project and you attempt to connect to a Microsoft Access 2000 database, you may receive the following error message:

Unrecognized Database Format This error message occurs because the DAO generic data control is based on Jet 3.51 and does not recognize Jet 4.0 database formats. Access 2000 is a Jet 4.0 format database. Prior to Microsoft Visual Studio 6.0 Service Pack 4 (SP4), the workaround for this problem was to open a recordset with DAO code, and then assign it to the recordset property of a data control. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
238401?(http://support.microsoft.com/kb/238401/) PRB: Unrecognized database format error message when upgrading to Access 2000 This problem does not exist with the data control that ships with Microsoft Visual Studio 6.0 Service Pack 6.
Visual Studio 6.0 Service Pack 6 can be obtained at the Visual Studio 6.0 Service Pack Web site:
http://msdn2.microsoft.com/en-us/vstudio/aa718364.aspx(http://msdn2.microsoft.com/en-us/vstudio/aa718364.aspx)Jet 3.51 OLE DB Provider is not included in MDAC 2.1 or later The Jet OLE DB Provider requires the version number of the provider in order to connect to an Access database. If your application specifies Version 3.51 of the Jet OLE DB provider in a connection string or a UDL, and you install ADO by redistributing MDAC 2.1, you are likely to receive the following error message:

3706: ADO could not find the specified provider This error message occurs because MDAC 2.0 installs version 3.51 of the Jet OLE DB provider while MDAC 2.1 installs version 4.0 of the Jet OLE DB Provider, but does not install version 3.51. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
197902?(http://support.microsoft.com/kb/197902/) PRB: Jet 3.51 OLE DB Provider is not installed with MDAC 2.1 or later
Microsoft Data Access Objects (DAO) 3.6 Object Library DAO version 3.6 is required for applications that use DAO to read and write to Access 2000 databases. If Access 2000 (or DAO 3.6) is already installed on the destination computer, then no additional steps are required. If it is not, it is necessary to distribute DAO 3.6. However, there is currently no redistributable for DAO 3.6. In order to redistribute DAO 3.6, it is necessary to install DCOM, redistribute MDAC 2.1(GA) or later, and ensure that the DAO DLL is also distributed and registered on the destination computer.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
233002?(http://support.microsoft.com/kb/233002/) How to redistribute DAO 3.6
Microsoft Remote Data Object (RDO) 2.0 In order for RDO to be properly distributed and installed, ODBC must already be installed on the destination computer. ODBC can be installed through the odbcst32.exe file, which is located in the ODBC Folder under the SQL/i386 directory on Visual Basic 6.0 Disk 2. ODBC can also be installed by including the MDAC redistributable in the distribution that is outlined in the “References” section covering distributing ADO/MDAC. To determine all of the files needed to distribute for RDO to work successfully on a computer that does not have Visual Basic installed, you can generate a dependency (.dep) file with the Package and Deployment Wizard. To generate a dependency file that shows the files needed for RDO when running the PDW, complete the following steps: Create a Standard EXE project in Visual Basic. Form1 is created by default.Add a reference to Microsoft Remote Data Object 2.0.Save this project and run the Package and Deployment Wizard on it.Select the Package option.On the Package Type, choose Dependency File, and then proceed through the wizard to completion. When you are finished, you have a file with a .dep extension that can be opened with Microsoft Notepad. MSRDO20.dll and its dependencies are shown in this file.
You want to ensure that these files are included with the distribution. If these files are not listed on the Included Files dialog box when you run the Package and Deployment Wizard, include them by clicking Add in the dialog box.
Microsoft ActiveX Data Objects (ADO) 2.0, 2.1 or 2.5 Library The PDW does not distribute mdac_typ unless there is a specific reference to an ADO Library (any version) in the project.
You can also add mdac_typ.exe manually by clicking Add in the Included Files dialog box when you run the Package and Deployment Wizard. The wizard uses the MDAC_Typ.exe file in the …\Wizards\PDWizard\Redist folder. Obtain the MDAC Components at the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/data/aa937695.aspx(http://msdn2.microsoft.com/en-us/data/aa937695.aspx) For additional information how the PDW distributes MDAC, click the following article number to view the article in the Microsoft Knowledge Base:
217754?(http://support.microsoft.com/kb/217754/) How to control which MDAC version the Package and Deployment Wizard (PDW) distributes The installation of Microsoft Data Access Components (MDAC) requires that DCOM be already installed on the destination computer: For Windows 95 computers, install DCOM95.For Windows 98 computers, install DCOM98.Download the latest version of DCOM from the following Microsoft Web page:
http://www.microsoft.com/downloads/details.aspx?familyid=08B1AC1B-7A11-43E8-B59D-0867F9BDDA66&displaylang=en(http://www.microsoft.com/downloads/details.aspx?familyid=08B1AC1B-7A11-43E8-B59D-0867F9BDDA66&displaylang=en) If DCOM is not already installed on the destination computer, you may receive one of the following error messages:

Unable to load file ‘msdadc.dll’ to register it
-or-

DLL registration failed For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
191704?(http://support.microsoft.com/kb/191704/) PRB: Unable to load file to register it during setup