Visual Basic Q&A

Symptoms
There are many ways to achieve inter-process communication using VisualBasic. Unless you establish an OLE Automation client server relationship,string data is difficult to handle cleanly. The main reason is that 32-bitapplications run in a separate address space, so the address of a string inone application is not meaningful to another application in a differentaddress space. Using the SendMessage() API function to pass a WM_COPYDATAmessage avoids this problem.
This article demonstrates how to pass string data from one application toanother by using the SendMessage API function with the WM_COPYDATA message.
Resolution
WARNING: One or more of the following functions are discussed in this article; VarPtr, VarPtrArray, VarPtrStringArray, StrPtr, ObjPtr. These functions are not supported by Microsoft Technical Support. They are not documented in the Visual Basic documentation and are provided in this Knowledge Base article “as is.” Microsoft does not guarantee that they will be available in future releases of Visual Basic.
Visual Basic does not support pointers and castings in the manner of VisualC++. In order to pass string data from one Visual Basic application toanother, the Unicode string must be converted to ASCII prior to passing itto the other application. The other application must then convert the ASCIIstring back to Unicode.
The following summarizes how to pass string data from one application toanother.
Step-by-Step Example Convert the string to a byte array using the CopyMemory() API.Obtain the address of the byte array using the VarPtr() intrinsicfunction and copy the address and length of the byte array into a COPYDATASTRUCT structure.Pass the COPYDATASTRUCT to another application using the WM_COPYDATAmessage, setting up the other application to receive the message.Unpack the structure on the target system using CopyMemory(), andconvert the byte array back to a string using the StrConv() intrinsicfunction.The next section shows you how to create a sample program that demonstratespassing string data from one application to another.
Steps to Create the SampleTo create this sample, you create two separate projects; a sendingproject and a target project.
Create the target application:Start a new Standard EXE project in Visual Basic. Form1 is created by default. This project will be your target application.Add a Label control to Form1.Copy the following code to the Code window of Form1:

Private Sub Form_Load()gHW = Me.hWndHookMe.Caption = “Target”Me.ShowLabel1.Caption = Hex$(gHW)End SubPrivate Sub Form_Unload(Cancel As Integer)UnhookEnd Sub Add a module to the project and paste the following code in the Module1 code window:

Type COPYDATASTRUCTdwData As LongcbData As LonglpData As LongEnd TypePublic Const GWL_WNDPROC = (-4)Public Const WM_COPYDATA = &H4AGlobal lpPrevWndProc As LongGlobal gHW As Long’Copies a block of memory from one location to another.Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _(hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)Declare Function CallWindowProc Lib “user32″ Alias _”CallWindowProcA” (ByVal lpPrevWndFunc As Long, ByVal hwnd As _Long, ByVal Msg As Long, ByVal wParam As Long, ByVal lParam As _Long) As LongDeclare Function SetWindowLong Lib “user32″ Alias “SetWindowLongA” _(ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As _Long) As LongPublic Sub Hook()lpPrevWndProc = SetWindowLong(gHW, GWL_WNDPROC, _AddressOf WindowProc)Debug.Print lpPrevWndProcEnd SubPublic Sub Unhook()Dim temp As Longtemp = SetWindowLong(gHW, GWL_WNDPROC, lpPrevWndProc)End SubFunction WindowProc(ByVal hw As Long, ByVal uMsg As Long, _ByVal wParam As Long, ByVal lParam As Long) As LongIf uMsg = WM_COPYDATA ThenCall mySub(lParam)End IfWindowProc = CallWindowProc(lpPrevWndProc, hw, uMsg, wParam, _lParam)End FunctionSub mySub(lParam As Long)Dim cds As COPYDATASTRUCTDim buf(1 To 255) As ByteCall CopyMemory(cds, ByVal lParam, Len(cds))Select Case cds.dwDataCase 1Debug.Print “got a 1″Case 2Debug.Print “got a 2″Case 3Call CopyMemory(buf(1), ByVal cds.lpData, cds.cbData)a$ = StrConv(buf, vbUnicode)a$ = Left$(a$, InStr(1, a$, Chr$(0)) – 1)Form1.Print a$End SelectEnd Sub Save the project and minimize the Visual Basic IDE.
Create the Sending ApplicationStart a second instance of the Visual Basic IDE and create a new Standard EXE project in Visual Basic. Form1 is created by default.Add a CommandButton to Form1.Copy the following code to the Code window of Form1:

Private Type COPYDATASTRUCTdwData As LongcbData As LonglpData As LongEnd TypePrivate Const WM_COPYDATA = &H4APrivate Declare Function FindWindow Lib “user32″ Alias _”FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName _As String) As LongPrivate Declare Function SendMessage Lib “user32″ Alias _”SendMessageA” (ByVal hwnd As Long, ByVal wMsg As Long, ByVal _wParam As Long, lParam As Any) As Long’Copies a block of memory from one location to another.Private Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _(hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)Private Sub Command1_Click()Dim cds As COPYDATASTRUCTDim ThWnd As LongDim buf(1 To 255) As Byte’ Get the hWnd of the target applicationThWnd = FindWindow(vbNullString, “Target”)a$ = “It Works!”‘ Copy the string into a byte array, converting it to ASCIICall CopyMemory(buf(1), ByVal a$, Len(a$))cds.dwData = 3cds.cbData = Len(a$) + 1cds.lpData = VarPtr(buf(1))i = SendMessage(ThWnd, WM_COPYDATA, Me.hwnd, cds)End SubPrivate Sub Form_Load()’ This gives you visibility that the target app is running’ and you are pointing to the correct hWndMe.Caption = Hex$(FindWindow(vbNullString, “Target”))End Sub Save the project.
Running the SampleRestore the target application and press the F5 key to run the project. Note that the value of the hWnd displayed in the label.Restore the sending application and press the F5 key to run the project.Verify that the hWnd in the form caption matches the hWnd in the labelon the target application. Click the CommandButton and the text messageshould be displayed on the form of the target application.

Symptoms
This article demonstrates how to programmatically control Microsoft Outlook using Automation from Visual Basic. The example demonstrates creating contacts, creating appointments, and sending messages by using Microsoft Outlook’s object-model.
Resolution
Follow the steps below to create and run the example. To run the sample, you need an early-bound reference to a Microsoft Outlook type library. The following table lists the file names of the type libraries for the different versions of Microsoft Outlook:
Collapse this tableExpand this table
Outlook versionHow type library appears in references listFilenameOutlook 97″Microsoft Outlook 8.0 Object Library”msoutl8.olbmsoutl8.olb”Microsoft Outlook 98 Object Library”msoutl85.olbOutlook 2000″Microsoft Outlook 9.0 Object Library”msoutl9.olbOutlook 2002″Microsoft Outlook 10.0 Object Library”msoutl.olbOffice Outlook 2003″Microsoft Outlook 11.0 Object Library”msoutl.olb
Building the automation sampleStart Visual Basic, and create a new Standard EXE project.From the Project menu, choose References and select Microsoft Outlook.Add a button to your form.Double-click the button, and then add the following code:

‘ Start Outlook. ‘ If it is already running, you’ll use the same instance…Dim olApp As Outlook.ApplicationSet olApp = CreateObject(“Outlook.Application”)’ Logon. Doesn’t hurt if you are already running and logged on…Dim olNs As Outlook.NameSpaceSet olNs = olApp.GetNamespace(“MAPI”)olNs.Logon ‘ Create and Open a new contact.Dim olItem As Outlook.ContactItemSet olItem = olApp.CreateItem(olContactItem) ‘ Setup Contact information…With olItem.FullName = “James Smith”.Birthday = “9/15/1975″.CompanyName = “Microsoft”.HomeTelephoneNumber = “704-555-8888″.Email1Address = “someone@microsoft.com”.JobTitle = “Developer”.HomeAddress = “111 Main St.” & vbCr & “Charlotte, NC 28226″End With’ Save Contact…olItem.Save’ Create a new appointment.Dim olAppt As Outlook.AppointmentItemSet olAppt = olApp.CreateItem(olAppointmentItem)’ Set start time for 2-minutes from now…olAppt.Start = Now() + (2# / 24# / 60#)’ Setup other appointment information…With olAppt.Duration = 60.Subject = “Meeting to discuss plans…”.Body = “Meeting with ” & olItem.FullName & ” to discuss plans.”.Location = “Home Office”.ReminderMinutesBeforeStart = 1.ReminderSet = TrueEnd With’ Save Appointment…olAppt.Save’ Send a message to your new contact.Dim olMail As Outlook.MailItemSet olMail = olApp.CreateItem(olMailItem) ‘ Fill out & send message…olMail.To = olItem.Email1AddressolMail.Subject = “About our meeting…”olMail.Body = _”Dear ” & olItem.FirstName & “, ” & vbCr & vbCr & vbTab & _”I’ll see you in 2 minutes for our meeting!” & vbCr & vbCr & _”Btw: I’ve added you to my contact list.”olMail.Send’ Clean up…MsgBox “All done…”, vbMsgBoxSetForegroundolNS.LogoffSet olNs = NothingSet olMail = NothingSet olAppt = NothingSet olItem = NothingSet olApp = Nothing Run the project, and click the button to run the code. Once the code runs, you should have a new contact named “James Smith,” an appointment scheduled in two minutes with a reminder to appear in one minute, and have sent a message to someone@microsoft.com. Also, because you added a birthday for your contact (9/15), a recurring event was added for your Outlook Calendar to remind you of the day.
New to Outlook 2002 are the two dialog boxes: one warning you that a program is trying to access e-mail addresses you have stored in Outlook and asking if you want to allow this, and another message to the effect that a program is trying to send e-mail. This feature will protect you from unknowingly being used by a virus that sends e-mail from your system.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290500?(http://support.microsoft.com/kb/290500/) Description of the developer-related e-mail security features in Outlook 2002

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 create and manipulate Excel by using Automation from Visual Basic.
Resolution
There are two ways to control an Automation server: by using either late binding or early binding. With late binding, methods are not bound until run-time and the Automation server is declared as Object. With early binding, your application knows at design-time the exact type of object it will be communicating with, and can declare its objects as a specific type. This sample uses early binding, which is considered better in most cases because it affords greater performance and better type safety.
To early bind to an Automation server, you need to set a reference to that server’s type library. In Visual Basic, this is done through the References dialog box found under the Project | References menu. For this sample, you will need to add a reference to the type library for Excel before you can run the code. See the steps below on how to add the reference.

Building the Automation SampleStart Visual Basic and create a new Standard EXE project. Form1 is created by default.ClickProject and then click References. The References dialog box appears. Scroll down the list until you find Microsoft Excel object library, and then select the item to add a reference to Excel. If the correct object library for your version of Excel does not appear in the list, make sure that you have your version of Excel properly installed.
NotesIf you are automating Microsoft Office Excel 2007, the type library appears as Microsoft Excel 12.0 Object Library in the References list.If you are automating Microsoft Office Excel 2003, the type library appears as Microsoft Excel 11.0 Object Library in the References list.If you are automating Microsoft Excel 2002, the type library appears as Microsoft Excel 10.0 Object Library in the References listIf you are automating Microsoft Excel 2000, the type library appears as Microsoft Excel 9.0 Object Library in the References list.If you are automating Microsoft Excel 97, the type library appears as Microsoft Excel 8.0 Object Library in the References listClick OK to close the References dialog box.Add a CommandButton to Form1.In the code window for Form1, insert the following code:

Option ExplicitPrivate Sub Command1_Click()Dim oXL As Excel.ApplicationDim oWB As Excel.WorkbookDim oSheet As Excel.WorksheetDim oRng As Excel.Range’On Error GoTo Err_Handler’ Start Excel and get Application object.Set oXL = CreateObject(“Excel.Application”)oXL.Visible = True’ Get a new workbook.Set oWB = oXL.Workbooks.AddSet oSheet = 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 = 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).Set oRng = oSheet.Range(“C2″, “C6″)oRng.Formula = “=A2 & “” “” & B2″‘ Fill D2:D6 with a formula(=RAND()*100000) and apply format.Set oRng = oSheet.Range(“D2″, “D6″)oRng.Formula = “=RAND()*100000″oRng.NumberFormat = “$0.00″‘ AutoFit columns A:D.Set 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 Microsoft Excel’s lifetime.oXL.Visible = TrueoXL.UserControl = True’ Make sure you release object references.Set oRng = NothingSet oSheet = NothingSet oWB = NothingSet oXL = NothingExit SubErr_Handler:MsgBox Err.Description, vbCritical, “Error: ” & Err.NumberEnd SubPrivate Sub DisplayQuarterlySales(oWS As Excel.Worksheet)Dim oResizeRange As Excel.RangeDim oChart As Excel.ChartDim 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 iNumQtrssMsg = “Displaying data for” & Str(iNumQtrs) & ” quarter(s).”MsgBox sMsg, vbMsgBoxSetForeground, “Quarterly Sales”‘ Starting at E1, fill headers for the number of columns selected.Set 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.Set oResizeRange = oWS.Range(“E2″, “E6″).Resize(ColumnSize:=iNumQtrs)oResizeRange.Formula = “=RAND()*100″oResizeRange.NumberFormat = “$0.00″‘ Apply borders to the Sales data and headers.Set oResizeRange = oWS.Range(“E1″, “E6″).Resize(ColumnSize:=iNumQtrs)oResizeRange.Borders.Weight = xlThin’ Add a Totals formula for the sales data and apply a border.Set oResizeRange = oWS.Range(“E8″, “E8″).Resize(ColumnSize:=iNumQtrs)oResizeRange.Formula = “=SUM(E2:E6)”With oResizeRange.Borders(xlEdgeBottom).LineStyle = xlDouble.Weight = xlThickEnd With’ Add a Chart for the selected dataSet oResizeRange = oWS.Range(“E2:E6″).Resize(ColumnSize:=iNumQtrs)Set oChart = oWS.Parent.Charts.AddWith oChart.ChartWizard oResizeRange, xl3DColumn, , xlColumns.SeriesCollection(1).XValues = oWS.Range(“A2″, “A6″)For iRet = 1 To iNumQtrs.SeriesCollection(iRet).Name = “=”"Q” & Str(iRet) & “”"”Next iRet.Location xlLocationAsObject, oWS.NameEnd With’ Move the chart so as not to cover your data.With oWS.Shapes(“Chart 1″).Top = oWS.Rows(10).Top.Left = oWS.Columns(2).LeftEnd With’ Free any references.Set oChart = NothingSet oResizeRange = NothingEnd Sub Press F5 to run the project.

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
ActiveX Data Objects (ADO) offers several ways to open both the Connection and Recordset objects. This article presents sample code for several common techniques for each object.
Resolution
There are several ways to open a Connection Object within ADO:
By Setting the ConnectionString property to a valid Connect string and then calling the Open() method. This connection string is provider- dependent.By passing a valid Connect string to the first argument of the Open() method.By passing a valid Command object into the first argument of a Recordset’s Open method.By passing the ODBC Data source name and optionally user-id and password to the Connection Object’s Open() method. There are three ways to open a Recordset Object within ADO:
By opening the Recordset off the Connection.Execute() method.By opening the Recordset off the Command.Execute() method.By opening the Recordset object without a Connection or Command object, and passing an valid Connect string to the second argument of the Recordset.Open() method. This code assumes that Nwind.mdb is installed with Visual Basic, and is located in the C:\Program Files\DevStudio\VB directory:

Option ExplicitPrivate Sub cmdOpen_Click()Dim Conn1 As New adodb.ConnectionDim Cmd1 As New adodb.CommandDim Errs1 As ErrorsDim Rs1 As New adodb.RecordsetDim i As IntegerDim AccessConnect As String’ Error Handling VariablesDim errLoop As ErrorDim strTmp As StringAccessConnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=nwind.mdb;” & _”DefaultDir=C:\program files\devstudio\vb;” & _”Uid=Admin;Pwd=;”‘—————————’ Connection Object Methods’—————————On Error GoTo AdoError’ Full Error Handling which traverses’ Connection object’ Connection Open method #1:Open via ConnectionString PropertyConn1.ConnectionString = AccessConnectConn1.OpenConn1.CloseConn1.ConnectionString = “”‘ Connection Open method #2:Open(“[ODBC Connect String]“,”",”")Conn1.Open AccessConnectConn1.Close’ Connection Open method #3:Open(“DSN”,”Uid”,”Pwd”)Conn1.Open “Driver={Microsoft Access Driver (*.mdb)};” & _”DBQ=nwind.mdb;” & _”DefaultDir=C:\program files\devstudio\vb;” & _”Uid=Admin;Pwd=;”Conn1.Close’————————–’ Recordset Object Methods’————————–’ Don‘t assume that we have a connection object.On Error GoTo AdoErrorLite’ Recordset Open Method #1:Open via Connection.Execute(…)Conn1.Open AccessConnectSet Rs1 = Conn1.Execute(“SELECT * FROM Employees”)Rs1.CloseConn1.Close’ Recordset Open Method #2:Open via Command.Execute(…)Conn1.ConnectionString = AccessConnectConn1.OpenCmd1.ActiveConnection = Conn1Cmd1.CommandText = “SELECT * FROM Employees”Set Rs1 = Cmd1.ExecuteRs1.CloseConn1.CloseConn1.ConnectionString = “”‘ Recordset Open Method #3:Open via Command.Execute(…)Conn1.ConnectionString = AccessConnectConn1.OpenCmd1.ActiveConnection = Conn1Cmd1.CommandText = “SELECT * FROM Employees”Rs1.Open Cmd1Rs1.CloseConn1.CloseConn1.ConnectionString = “”‘ Recordset Open Method #4:Open w/o Connection & w/Connect StringRs1.Open “SELECT * FROM Employees”, AccessConnect, adOpenForwardOnlyRs1.CloseDone:Set Rs1 = NothingSet Cmd1 = NothingSet Conn1 = NothingExit SubAdoError:i = 1On Error Resume Next’ Enumerate Errors collection and display properties of’ each Error object (if Errors Collection is filled out)Set Errs1 = Conn1.ErrorsFor Each errLoop In Errs1With errLoopstrTmp = strTmp & vbCrLf & “ADO Error # ” & i & “:”strTmp = strTmp & vbCrLf & “ADO Error# ” & .NumberstrTmp = strTmp & vbCrLf & “Description” & .DescriptionstrTmp = strTmp & vbCrLf & “Source” & .Sourcei = i + 1End WithNextAdoErrorLite:’ Get VB Error Object’s informationstrTmp = strTmp & vbCrLf & “VB Error # ” & Str(Err.Number)strTmp = strTmp & vbCrLf & “Generated by ” & Err.SourcestrTmp = strTmp & vbCrLf & “Description” & Err.DescriptionMsgBox strTmp’ Clean up gracefully without risking infinite loop in error handlerOn Error GoTo 0GoTo DoneEnd Sub
ERROR NOTES Only the ADO Connection object has an errors collection. The observant reader will notice that a lightweight error handler is in effect for the RecordSet.Open examples. In the event of an error opening a RecordSet object, ADO should return the most explicit error from the OLEDB provider. Some common errors that can be encountered with the preceding code follow.
If you omit (or there is an error in) the DefaultDir parameter in the connect string, you may receive the following error:

ADO Error # -2147467259
Description [Microsoft][ODBC Microsoft Access 97 Driver] ‘(unknown)’
isn’t a valid path. Make sure that the path name is
spelled correctly and that you are connected to the server
on which the file resides.
Source Microsoft OLE DB Provider for ODBC Drivers
If there is an error in the Dbq parameter in the connect string, you may receive the following error:

ADO Error # -2147467259 Description [Microsoft][ODBC Microsoft Access 97 Driver] Couldn’t find
file ‘(unknown)’.
Source Microsoft OLE DB Provider for ODBC Drivers
The preceding errors also populate the Connection.Errors collection with the following errors:

ADO Error # -2147467259
Description [Microsoft][ODBC Driver Manager] Driver’s
SQLSetConnectAttr failed
Source Microsoft OLE DB Provider for ODBC Drivers

ADO Error # -2147467259
Description Login Failed
Source Microsoft OLE DB Provider for ODBC Drivers Note that for each error, the ADO Error number is the same, in this case translating to 0×80004005, which is the generic E_FAIL error message. The underlying Component did not have a specific error number for the condition encountered, but useful information was never-the-less raised to ADO.