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
Consider the following scenario. A Microsoft Visual Basic 6.0 project contains at least one code module thathas nine or more Case Is statements. You try to upgrade the Visual Basic 6.0 project by using the Visual Basic Upgrade Wizard in Microsoft Visual Basic 2005. In this scenario, the Visual Basic Upgrade Wizard stops responding (hangs).
Resolution
To work around this problem, follow these steps:Open the Visual Basic 6.0 project in Microsoft Visual Studio 6.0.Comment out anyCase Is statements.Save the code module.Start Visual Basic 2005.On the File menu, click Open Project.In the Open Project dialog box, locate the Visual Basic 6.0 project folder, click the project file name, and then click Open. The Visual Basic Upgrade Wizard starts.Click Next.Under Choose a Project Type, click EXE or DLL, and then click Next.Type a location for the project, and then click Next.If the folder does not exist, click Yes to create the folder.Click Next to start the upgrade.After the upgrade is complete, remove the comments from the Case Is statements.

Symptoms
When you create a command object for Oracle to run a stored procedure by using the OracleCommand object from the Toolbox, you may receive the following error message after you specify the stored procedure name in the CommandText property:

Invalid Property Data
The stored procedure “OraclePackageName.OracleProcedureName” could not be found in the database.
Resolution
To work around this problem, use one of the following methods: In the CommandText property of OracleCommand1, type the name of the stored procedure exactly as it appears (this property is case-sensitive) in the stored procedure list in Server Explorer.Click OK to ignore the error message, and then manually add the code to call the Oracle stored procedure in your class. You can view the name of the stored procedure in Server Explorer. To do this, follow these steps: On the View menu, click Server Explorer.Right-click Data Connection, and then click Add Connection.On the Provider tab, click to select the Microsoft OLE DB Provider for Oracle check box. Click the Connection tab. Type the server name, the user name and the password, and then click Test Connection. Click OK to close the Test connection succeeded dialog box. Click OK to close the Data Link Properties dialog box. Expand Oracle database. Expand Stored Procedure to view the list of existing stored procedures.

Symptoms
This article describes how to use the Microsoft Outlook 2002 Object Library or the Microsoft Office Outlook 2003 Object Library to force a Send/Receive action by using Microsoft Visual Basic .NET.
Resolution
To use the Outlook 2002 Object Library or the Outlook 2003 Object Library to force a Send/Receive action in a Visual Basic .NET project, follow these steps: Start Microsoft Visual Studio .NET. On the File menu, point to New, and then click Project. Under Project Types, click Visual Basic Projects.Under Templates, click Console Application, and then click OK.
By default, Module1.vb is created.Add a reference to either the Outlook 2002 Object Library or the Outlook 2003 Object Library: On the Project menu, click Add Reference.On the COM tab, click Microsoft Outlook 11.0 Object Library if you are using Outlook 2003, or click Microsoft Outlook 10.0 Object Library if you are using Outlook 2002, and then click Select. In the Add References dialog box, click OK to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.In the code window, replace all the code with:

‘TO DO: If you use the Microsoft Outlook 11.0 Object Library, uncomment the following line.’Imports Outlook = Microsoft.Office.Interop.OutlookModule Module1Sub Main()’ Create an Outlook application.Dim oApp As Outlook._Application = New Outlook.Application’ Create the name space.Dim oNS As Outlook._NameSpace = oApp.GetNamespace(“mapi”)Dim oSyncs As Outlook.SyncObjectsDim oSync As Outlook.SyncObjectTry’ Reference SyncObjects.oSyncs = oNS.SyncObjectsoSync = oSyncs.Item(“All Accounts”)’ Send and receive.oSync.Start()Catch ex As ExceptionConsole.WriteLine(ex.Message)End Try’ Clean up.oSync = NothingoSyncs = NothingoNS = NothingoApp = NothingEnd SubEnd ModulePress F5 to build and run the program.

Symptoms
This article describes how to use ActiveX Data Objects Extensibility (ADOX) to create an OLE Object field in a Microsoft Access Database (.mdb file). You must use the adLongVarBinary constant to create the field. You do not have to specify a field size in the field definition.
Resolution
Step-by-Step ExampleCreate a new Standard EXE project in Visual Basic. Form1 is created by default.From the Project menu, click References. From the list of available components, click Microsoft ADO Ext. 2.1 for DDL and Security.Add a CommandButton control to Form1.Paste the following code onto the Declarations section of Form1:

Private Sub Command2_Click()Set cat = New ADOX.CatalogSet tbl = New ADOX.Tablecat.ActiveConnection = _”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\nwind2.mdb;”tbl.Name = “OleObjTable”tbl.Columns.Append “Column1″, adIntegertbl.Columns.Append “Column2″, adIntegertbl.Columns.Append “Column3″, adVarWChar, 50′ Please note adLongVarBinary = 205tbl.Columns.Append “MyOleObject”, adLongVarBinarycat.Tables.Append tblEnd Sub Modify the cat.ActiveConnection assignment to point to a valid Microsoft Access Database file.Run the project, and click Command1. Notice that a table named OleObjTable is created in the database. When you view the table in Design Mode, the Column3 field definition is displayed as OLE Object.

Symptoms
This article demonstrates how to programmatically use Data Links feature of the Microsoft Data Access Components in order to generate a connection string at run-time.
Resolution
In version 2.0 of the Microsoft Data Access Components, Data Links were introduced. Data Link files are similar to ODBC DSN files, but allow you to select an OLE DB provider to connect to your database. With the OLE DB Provider for ODBC drivers, you can also connect to an ODBC data source.
Double-clicking on a Data Link file displays a set of property pages that allow you to build a connection string to connect to your database.
You can use this same functionality in your Visual Basic applications by following the steps listed below: Launch Visual Basic and open a new Standard Exe project. Form1 is created by default.Select References from the Project menu, and then select Microsoft OLE DB Service Component 1.0 Type Library from the list of available references.Add a CommandButton to your form.Add the following code to the Click event of your CommandButton:

Private Sub Command1_Click()Dim objDataLink As New DataLinksDim strConn As StringstrConn = objDataLink.PromptNewMsgBox “The connection string you created is:” & _vbCrLf & strConnEnd Sub Run the project. When you click the CommandButton, you will see the Data Links property pages. Once you have specified how you want to connect to your database and click the OK button, you’ll see the connection string in a dialog box.