Visual Basic Q&A

Symptoms
This article discusses numerous methods for transferring data to Microsoft Excel from your Microsoft Visual Basic application. This article also presents the advantages and the disadvantages for each method so that you can choose the solution that works best for you.
Resolution
The approach most commonly used to transfer data to an Excel workbook is Automation. Automation gives you the greatest flexibility for specifying the location of your data in the workbook as well as the ability to format the workbook and make various settings at run time. With Automation, you can use several approaches for transferring your data: Transfer data cell by cellTransfer data in an array to a range of cellsTransfer data in an ADO recordset to a range of cells using the CopyFromRecordset methodCreate a QueryTable on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data sourceTransfer data to the clipboard and then paste the clipboard contents into an Excel worksheet There are also methods that you can use to transfer data to Excel that do not necessarily require Automation. If you are running an application server-side, this can be a good approach for taking the bulk of processing the data away from your clients. The following methods can be used to transfer your data without Automation: Transfer your data to a tab- or comma-delimited text file that Excel can later parse into cells on a worksheetTransfer your data to a worksheet using ADOTransfer data to Excel using Dynamic Data Exchange (DDE) The following sections provide more detail on each of these solutions.
Note When you use Microsoft Office Excel 2007, you can use the new Excel 2007 Workbook (*.xlsx) file format when you save the workbooks. To do this, locate the following line of code in the following code examples:

oBook.SaveAs “C:\Book1.xls”Replace this code withwith the following line of code:

oBook.SaveAs “C:\Book1.xlsx”Additionally, the Northwind database is not included in Office 2007 by default. However, you can download the Northwind database from Microsoft Office Online.Use Automation to transfer data cell by cell With Automation, you can transfer data to a worksheet one cell at a time:

Dim oExcel As ObjectDim oBook As ObjectDim oSheet As Object’Start a new workbook in ExcelSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.Add’Add data to cells of the first worksheet in the new workbookSet oSheet = oBook.Worksheets(1)oSheet.Range(“A1″).Value = “Last Name”oSheet.Range(“B1″).Value = “First Name”oSheet.Range(“A1:B1″).Font.Bold = TrueoSheet.Range(“A2″).Value = “Doe”oSheet.Range(“B2″).Value = “John”‘Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.Quit Transferring data cell by cell can be a perfectly acceptable approach if the amount of data is small. You have the flexibility to place data anywhere in the workbook and can format the cells conditionally at run time. However, this approach is not recommended if you have a large amount of data to transfer to an Excel workbook. Each Range object that you acquire at run time results in an interface request so that transferring data in this manner can be slow. Additionally, Microsoft Windows 95 and Windows 98 have a 64K limitation on interface requests. If you reach or exceed this 64k limit on interface requests, the Automation server (Excel) might stop responding or you might receive errors indicating low memory. This limitation for Windows 95 and Windows 98 is discussed in the following Knowledge Base article:
216400?(http://support.microsoft.com/kb/216400/) Cross-process COM automation can hang client application on Win 95/98 Once more, transferring data cell by cell is acceptable only for small amounts of data. If you need to transfer large data sets to Excel, you should consider one of the solutions presented later.
For more sample code for Automating Excel, please see the following article in the Microsoft Knowledge Base:
219151?(http://support.microsoft.com/kb/219151/) How to automate Microsoft Excel from Visual BasicUse automation to transfer an array of data to a range on a worksheet An array of data can be transferred to a range of multiple cells at once:

Dim oExcel As ObjectDim oBook As ObjectDim oSheet As Object’Start a new workbook in ExcelSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.Add’Create an array with 3 columns and 100 rowsDim DataArray(1 To 100, 1 To 3) As VariantDim r As IntegerFor r = 1 To 100DataArray(r, 1) = “ORD” & Format(r, “0000″)DataArray(r, 2) = Rnd() * 1000DataArray(r, 3) = DataArray(r, 2) * 0.7Next’Add headers to the worksheet on row 1Set oSheet = oBook.Worksheets(1)oSheet.Range(“A1:C1″).Value = Array(“Order ID”, “Amount”, “Tax”)’Transfer the array to the worksheet starting at cell A2oSheet.Range(“A2″).Resize(100, 3).Value = DataArray’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.Quit If you transfer your data using an array rather than cell by cell, you can realize an enormous performance gain with a large amount of data. Consider this line from the code above that transfers data to 300 cells in the worksheet:

oSheet.Range(“A2″).Resize(100, 3).Value = DataArray This line represents two interface requests (one for the Range object that the Range method returns and another for the Range object that the Resize method returns). On the other hand, transferring the data cell by cell would require requests for 300 interfaces to Range objects. Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.Use automation to transfer an ADO recordset to a worksheet range Excel 2000 introduced the CopyFromRecordset method that allows you to transfer an ADO (or DAO) recordset to a range on a worksheet. The following code illustrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 and transfer the contents of the Orders table in the Northwind Sample Database using the CopyFromRecordset method.

‘Create a Recordset from all the records in the Orders tableDim sNWind As StringDim conn As New ADODB.ConnectionDim rs As ADODB.RecordsetsNWind = _”C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”conn.CursorLocation = adUseClientSet rs = conn.Execute(“Orders”, , adCmdTable)’Create a new workbook in ExcelDim oExcel As ObjectDim oBook As ObjectDim oSheet As ObjectSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.AddSet oSheet = oBook.Worksheets(1)’Transfer the data to ExceloSheet.Range(“A1″).CopyFromRecordset rs’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.Quit’Close the connectionrs.Closeconn.CloseNoteIf you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:

conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _ sNWind & “;” Replace this line of code with the following line of code:

conn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & _ sNWind & “;” Excel 97 also provides a CopyFromRecordset method but you can use it only with a DAO recordset. CopyFromRecordset with Excel 97 does not support ADO.
For more information about using ADO and the CopyFromRecordset method, please see the following article in the Microsoft Knowledge Base:
246335?(http://support.microsoft.com/kb/246335/) How to transfer data from an ADO recordset to Excel with automationUse automation to create a QueryTable on a worksheet A QueryTable object represents a table built from data returned from an external data source. While automating Microsoft Excel, you can create a QueryTable by simply providing a connection string to an OLEDB or an ODBC data source along with an SQL string. Excel assumes the responsibility for generating the recordset and inserting it into the worksheet at the location you specify. Using QueryTables offers several advantages over the CopyFromRecordset method: Excel handles the creation of the recordset and its placement into the worksheet.The query can be saved with the QueryTable so that it can be refreshed at a later time to obtain an updated recordset.When a new QueryTable is added to your worksheet, you can specify that data already existing in cells on the worksheet be shifted to accommodate the new data (see the RefreshStyle property for details). The following code demonstrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 to create a new QueryTable in an Excel worksheet using data from the Northwind Sample Database:

‘Create a new workbook in ExcelDim oExcel As ObjectDim oBook As ObjectDim oSheet As ObjectSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.AddSet oSheet = oBook.Worksheets(1)’Create the QueryTableDim sNWind As StringsNWind = _”C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”Dim oQryTable As ObjectSet oQryTable = oSheet.QueryTables.Add( _”OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”, oSheet.Range(“A1″), “Select * from Orders”)oQryTable.RefreshStyle = xlInsertEntireRowsoQryTable.Refresh False’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.QuitUse the clipboard The Windows Clipboard can also be used as a mechanism for transferring data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string where columns are delimited by tab characters and rows are delimited by carriage returns. The following code illustrates how Visual Basic can use its Clipboard object to transfer data to Excel:

‘Copy a string to the clipboardDim sData As StringsData = “FirstName” & vbTab & “LastName” & vbTab & “Birthdate” & vbCr _& “Bill” & vbTab & “Brown” & vbTab & “2/5/85″ & vbCr _& “Joe” & vbTab & “Thomas” & vbTab & “1/1/91″Clipboard.ClearClipboard.SetText sData’Create a new workbook in ExcelDim oExcel As ObjectDim oBook As ObjectSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.Add’Paste the dataoBook.Worksheets(1).Range(“A1″).SelectoBook.Worksheets(1).Paste’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.QuitCreate a delimited text file that Excel can parse into rows and columns Excel can open tab- or comma-delimited files and correctly parse the data into cells. You can take advantage of this feature when you want to transfer a large amount of data to a worksheet while using little, if any, Automation. This might be a good approach for a client-server application because the text file can be generated server-side. You can then open the text file at the client, using Automation where it is appropriate.
The following code illustrates how you can create a comma-delimited text file from an ADO recordset:

‘Create a Recordset from all the records in the Orders tableDim sNWind As StringDim conn As New ADODB.ConnectionDim rs As ADODB.RecordsetDim sData As StringsNWind = _”C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”conn.CursorLocation = adUseClientSet rs = conn.Execute(“Orders”, , adCmdTable)’Save the recordset as a tab-delimited filesData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)Open “C:\Test.txt” For Output As #1Print #1, sDataClose #1′Close the connectionrs.Closeconn.Close’Open the new text file in ExcelShell “C:\Program Files\Microsoft Office\Office\Excel.exe ” & _Chr(34) & “C:\Test.txt” & Chr(34), vbMaximizedFocusNoteIf you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:

conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”Replace this line of code with the following line of code:

conn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & _sNWind & “;” If your text file has a .CSV extension, Excel opens the file without displaying the Text Import Wizard and automatically assumes that the file is comma-delimited. Similarly, if your file has a .TXT extension, Excel automatically parse the file using tab delimiters.
In the previous code sample, Excel was launched using the Shell statement and the name of the file was used as a command line argument. No Automation was used in the previous sample. However, if so desired, you could use a minimal amount of Automation to open the text file and save it in the Excel workbook format:

‘Create a new instance of ExcelDim oExcel As ObjectDim oBook As ObjectDim oSheet As ObjectSet oExcel = CreateObject(“Excel.Application”)’Open the text fileSet oBook = oExcel.Workbooks.Open(“C:\Test.txt”)’Save as Excel workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”, xlWorkbookNormaloExcel.Quit For more information about using File I/O from your Visual Basic application, please see the following article in the Microsoft Knowledge Base:
172267?(http://support.microsoft.com/kb/172267/) RECEDIT.VBP demonstrates file I/O in Visual BasicTransfer data to a worksheet by using ADO Using the Microsoft Jet OLE DB Provider, you can add records to a table in an existing Excel workbook. A “table” in Excel is merely a range with a defined name. The first row of the range must contain the headers (or field names) and all subsequent rows contain the records. The following steps illustrate how you can create a workbook with an empty table named MyTable.Excel 97, Excel 2000, and Excel 2003Start a new workbook in Excel.Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastNameFormat cell B1 as right-aligned.Select A1:B1.On the Insert menu, choose Names and then select Define. Enter the name MyTable and click OK.Save the new workbook as C:\Book1.xls and quit Excel. To add records to MyTable using ADO, you can use code similar to the following:

‘Create a new connection object for Book1.xlsDim conn As New ADODB.Connectionconn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Bill’, ‘Brown’)”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Joe’, ‘Thomas’)”conn.CloseExcel 2007In Excel 2007, start a new workbook.Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastNameFormat cell B1 as right-aligned.Select A1:B1.On the Ribbon, click the Formulas tab, and then click Define Name.Type the name MyTable, and then click OK.Save the new workbook as C:\Book1.xlsx, and then quit Excel. To add records to the MyTable table by using ADO, use code that resembles the following code example.

‘Create a new connection object for Book1.xlsDim conn As New ADODB.Connectionconn.Open “Provider=Microsoft.ACE.OLEDB.12.0;” & _”Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Scott’, ‘Brown’)”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Jane’, ‘Dow’)”conn.Close When you add records to the table in this manner, the formatting in the workbook is maintained. In the previous example, new fields added to column B are formatted with right alignment. Each record that is added to a row borrows the format from the row above it.
You should note that when a record is added to a cell or cells in the worksheet, it overwrites any data previously in those cells; in other words, rows in the worksheet are not “pushed down” when new records are added. You should keep this in mind when designing the layout of data on your worksheets.
Note The method to update data in an Excel worksheet by using ADO or by using DAO does not work in Visual Basic for Application environment within Access after you install Office 2003 Service Pack 2 (SP2) or after you install the update for Access 2002 that is included in Microsoft Knowledge Base article 904018. The method works well in Visual Basic for Application environment from other Office applications, such as Word, Excel, and Outlook. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
904953?(http://support.microsoft.com/kb/904953/) You cannot change, add, or delete data in tables that are linked to an Excel workbook in Office Access 2003 or in Access 2002
904018?(http://support.microsoft.com/kb/904018/) Description of the update for Access 2002: October 18, 2005
For additional information on using ADO to access an Excel workbook, please see the following articles in the Microsoft Knowledge Base:
195951?(http://support.microsoft.com/kb/195951/) How to query and update Excel data using ADO from ASPUse DDE to transfer data to Excel DDE is an alternative to Automation as a means for communicating with Excel and transferring data; however, with the advent of Automation and COM, DDE is no longer the preferred method for communicating with other applications and should only be used when there is no other solution available to you.
To transfer data to Excel using DDE, you can: Use the LinkPoke method to poke data to a specific range of cell(s),
-or-Use the LinkExecute method to send commands that Excel will execute. The following code example illustrates how to establish a DDE conversation with Excel so that you can poke data to cells on a worksheet and execute commands. Using this sample, for a DDE conversation to be successfully established to the LinkTopic Excel|MyBook.xls, a workbook with the name MyBook.xls must already be opened in a running instance of Excel.
Note When you use Excel 2007, you can use the new .xlsx file format to save the workbooks. Make sure that you update the file name in the following code example.
Note In this example, Text1 represents a Text Box control on a Visual Basic form:

‘Initiate a DDE communication with ExcelText1.LinkMode = 0Text1.LinkTopic = “Excel|MyBook.xls”Text1.LinkItem = “R1C1:R2C3″Text1.LinkMode = 1′Poke the text in Text1 to the R1C1:R2C3 in MyBook.xlsText1.Text = “one” & vbTab & “two” & vbTab & “three” & vbCr & _”four” & vbTab & “five” & vbTab & “six”Text1.LinkPoke’Execute commands to select cell A1 (same as R1C1) and change the font’formatText1.LinkExecute “[SELECT(""R1C1"")]“Text1.LinkExecute “[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]“‘Terminate the DDE communicationText1.LinkMode = 0 When using LinkPoke with Excel, you specify the range in row-column (R1C1) notation for the LinkItem. If you are poking data to multiple cells, you can use a string where the columns are delimited by tabs and rows are delimited by carriage returns.
When you use LinkExecute to ask Excel to carry out a command, you must give Excel the command in the syntax of the Excel Macro Language (XLM). The XLM documentation is not included with Excel versions 97 and later. For more information on how you can obtain the XLM documentation, please see the following article in the Microsoft Knowledge Base:
143466?(http://support.microsoft.com/kb/143466/) Macro97.exe file available on online services DDE is not a recommended solution for communicating with Excel. Automation provides the greatest flexibility and gives you more access to the new features that Excel has to offer.

Symptoms
This article describes how to use Dynamic Data Exchange (DDE) from a Visual Basic program. Although Visual Basic offers more powerful, robust and reliable intercommunication between programs by using OLE, it still supports the older DDE technology. You can use DDE to link to and share data between programs.
Using OLE to communicate with other programs is better than using DDE with Visual Basic. OLE offers you better performance than DDE does. If a program supports OLE automation, it exposes an “Object Model” you can use to manipulate the program (through code) to complete actions for you. DDE is more useful in communicating with older programs that might not support OLE but do support DDE, and many 16-bit programs fall into this category.
Microsoft recommends that you use OLE rather than DDEto communicate with another program when you are developing your Visual Basic program. However, because there are times when you may need or want to use DDE, this article describes how to use DDE from a Visual Basic program.
Resolution
Visual Basic can exchange data, such as information, commands or keystrokes, with any program that supports DDE. A DDE link requires the following three things:Program nameTopicItemThe program name is the executable (.exe) file name of the source program minus the .exe file extension. The topic describes the type of data that is being exchanged. The topic is specific to the DDE source program. However, “System” is the one universal topic that you can use to query what topics are available in a particular program. The item parameter is the actual data that is being transferred. In Visual Basic, a topic could be a form with a textbox as an item. In Microsoft Excel, a topic could be a spreadsheet with a cell as an item. The program name and topic cannot be modified once the DDE link has been established because any modifications break the connection. The item parameter can be modified to any valid item during the DDE conversation without breaking the DDE link.
Specific to Visual Basic, the LinkTopic property supplies a combination of the program name and topic parameters. The LinkTopic property is available for Label, PictureBox, or TextBox controls which can share data through a DDE connection. The program name and topic parameters are combined by using the pipe character (|) as the separator. For example, to create a DDE link with Microsoft Word as the source program and by using the System topic. the LinkTopic property of a textbox would be:
Text1.LinkTopic=”WinWord|System”When Visual Basic is the source program for the DDE link, the LinkTopic of the form which contains the control to be shared is set to a known name. This known name is used in the LinkTopic of the Visual Basic destination program as the topic information. For example, to access data from a form with a LinkTopic that is set to “DDELink”, a Visual Basic destination program would have a matching control with a LinkTopic set to “program name|DDELink”.
The LinkItem property corresponds to the item parameter in a DDE connection, and this property is set to a corresponding item in the source program, for example, the “Total” cell in an Excel spreadsheet. The LinkItem property is set for the control that is used as in a destination program. When a Visual Basic form is the source in a DDE connection, the name of the control on the form can be the item argument in the program|topic|item string that is used by the destination program. When Visual Basic is used as both the source and destination program, the LinkItem property of the destination program would be set to the name of the control in the Visual Basic source program. For example, with Visual Basic used as both the source and destination program and a text box that is named txtSource as the item in the source program, a corresponding txtData text box in the destination program would have its LinkItem property set to txtSource:
txtData.LinkItem = “txtSource”Before a connection can be established, the mechanism to update the information needs to be set. In a DDE connection, there are three types of links:AutomaticManualNotifyAn automatic link updates the destination information whenever the source is modified. A manual link requires that the data be requested by the destination program, and notify informs the destination program that the source data has been changed but doesn’t actually update the information in the destination program. The one exception to an automatic update is the Picturebox control. Visual Basic does not automatically notify a DDE destination program when the Picture property setting of a PictureBox on a source Form changes. Because the amount of data in a graphic can be very large and because it seldom makes sense to update a destination program as each pixel in the picture changes, Visual Basic requires that you use the LinkSend method to explicitly notify DDE destination programs when the contents of a PictureBox change.
Once a DDE link has been established, there are several methods and events in Visual Basic that can be used to control how data is sent between the source and the destination program.
MethodsLinkRequest: Asks the source program to update the contents of a destination item.
LinkExecute: Sends a command string to the source program.
LinkPoke: Transfers the contents of the item control to the source program. Typically, information in a DDE conversation flows from the source to the destination. However, LinkPoke provides a destination object with the ability to supply data to the source.
LinkSend: Transfers the contents of a PictureBox control to the destination program in a DDE conversation.
EventsLinkOpen: Occurs when a DDE link is being initiated.
LinkClose: Occurs when a DDE link terminates. Either program in a DDE conversation may terminate a link at any time.
LinkError: Occurs when there is an error during a DDE conversation. This event is recognized only as the result of a DDE-related error that occurs when no Visual Basic code is being executed. The error number is passed as an argument.
LinkExecute: Occurs when a command string is sent by a destination program in a DDE conversation. The destination program expects the source program to perform the operation described by the string.
LinkNotify: Occurs when the source has changed the data that is defined by the DDE link if the LinkMode property of the destination control is set to Notify.

Symptoms
This article discusses how to automate Word to create a mail merge for an external data source. This article also explains the code differences between accessing the data with OLEDB, ODBC, and dynamic data exchange (DDE).
Resolution
Data access methods To programmatically set up a data source for a Word mail merge document, you first call the OpenDataSource method of a MailMerge object. The syntax for the OpenDataSource method is as follows:
<MailMergeObject>.OpenDataSource(Name, [Format], [ConfirmConversions], [ReadOnly], [LinkToSource], [AddToRecentFiles], [PasswordDocument], [PasswordTemplate], [Revert],[WritePasswordDocument], [WritePasswordTemplate], [Connection], [SQLStatement], [SQLStatement1], [OpenExclusive], [SubType]) Note For a complete description of each argument, refer to the Microsoft Word Visual Basic online Help. Of primary interest for connecting to an external data source are the Name, Connection, and SubType arguments. Different combinations of these three arguments represent different data access methods for the mail merge.
Using OLEDB OLEDB is the recommended data access method. To specify OLEDB as the data access method with OpenDataSource, supply the Name argument with the path and the file name to either the database or an Office DataSource Connection (.odc). If you provide a database for the Name argument, Word will automatically use OLEDB if there is an OLEDB provider installed that supports the database format.
Example

<MailMergeObject>.OpenDataSource Name:=”C:\MyDB.mdb”, _SQLStatement:=”SELECT * FROM [MyTable]”
- or -

<MailMergeObject>.OpenDataSource Name:=”C:\MyDataSource.odc”, _SQLStatement:=”SELECT * FROM [MyTable]” Word and other Office XP applications use the Office DataSource Object (ODSO) for OLEDB access to external data sources. ODSO is the only mechanism by which Word can access data by using OLEDB for a mail merge. ODSO requires that the Name argument for OpenDataSource be either a complete path to a database or a complete path to a valid ODC file. ODSO ignores any information in the Connection argument.
Using ODBC You can use ODBC for your mail merge to access data for which a user data source name (DSN) has been set up on the system. To specify ODBC as the data access method with OpenDataSource, supply an empty string for the Name argument, an ODBC connection string for the Connection argument, and wdMergeSubTypeWord2000 for the SubType argument.
Example

<MailMergeObject>.OpenDataSource Name:= “”, _Connection:= “DSN=MySQLServerDSN;DATABASE=pubs;uid=sa;pwd=;”, _SQLStatement:= “Select au_id, au_lname, au_fname from authors”, _SubType:= wdMergeSubTypeWord2000
Using DDE You can use DDE to access data in Microsoft Access databases or Microsoft Excel workbooks. To specify DDE as the data access method with OpenDataSource, supply the path and the file name to the database or the workbook for the Name argument, and wdMergeSubTypeWord2000 for the SubType argument.
Example

<MailMergeObject>.OpenDataSource Name:=”C:\MyDB.mdb”, _SQLStatement:=”SELECT * FROM [MyTable]“, _SubType:=wdMergeSubTypeWord2000
Automation sample The following sample code creates and executes a mail merge for form letters by using OLEDB (by way of ODSO). The data source that is used is the sample Access database Northwind.mdb. If Northwind is not installed, start Microsoft Access 2002 or Microsoft Office Access 2003. On the Help menu, click Sample Databases, and then choose Northwind Sample Database to install this feature.
To run this sample, follow these steps: Start a new Standard EXE project in Visual Basic. By default, Form1 is created.On the Project menu, click References.Click Microsoft Word 2000 Object Library in the list of references, and then click OK.
Note: To use the Microsoft Office Word 2003 Object, add the Microsoft Word 11.0 Object Library in the list of references and then Click OK.Add a CommandButton control to Form1.Add the following code to the code module for Form1.
Note If it is necessary, modify the path to Northwind.mdb to match your installation for Office XP.

Dim WithEvents oApp As Word.ApplicationPrivate Sub Form_Load()’Start Word.Set oApp = CreateObject(“Word.Application“)End SubPrivate Sub Command1_Click()Dim oMainDoc As Word.DocumentDim oSel As Word.SelectionDim sDBPath as String’Start a new main document for the mail merge.Set oMainDoc = oApp.Documents.AddWith oMainDoc.MailMerge.MainDocumentType = wdFormLetters’Set up the mail merge data source to Northwind.mdb.sDBPath = “C:\Program Files\Microsoft Office\” & _”OfficeXP\Samples\Northwind.mdb”.OpenDataSource Name:=sDBPath, _SQLStatement:=”SELECT * FROM [Customers]“‘Add the field codes to the document to create the form letter.With .FieldsSet oSel = oApp.Selection.Add oSel.Range, “CompanyName”oSel.TypeParagraph.Add oSel.Range, “Address”oSel.TypeParagraph.Add oSel.Range, “City”oSel.TypeText “, “.Add oSel.Range, “Country”oSel.TypeParagraphoSel.TypeParagraphoSel.TypeText “Dear “.Add oSel.Range, “ContactName”oSel.TypeText “,”oSel.TypeParagraphoSel.TypeParagraphoSel.TypeText ” This letter is to inform you…”oSel.TypeParagraphoSel.TypeParagraphoSel.TypeText “Sincerely, [Your Name Here]“End WithEnd With’Perform the mail merge to a new document.With oMainDoc.MailMerge.Destination = wdSendToNewDocument.MailMerge.Execute Pause:=FalseEnd WithEnd SubPrivate Sub oApp_MailMergeAfterMerge(ByVal Doc As Word.Document, ByVal DocResult As Word.Document)’When the mail merge is complete, 1) make Word visible,’2) close the mail merge document leaving only the resulting document’open and 3) display a message.Doc.Close FalseoApp.Visible = TrueMsgBox “Mail Merge Complete: ” & oApp.ActiveDocument.NameEnd SubPrivate Sub Form_Unload(Cancel As Integer)Set oApp = NothingEnd Sub Press F5 to run the program.Click the CommandButton control on Form1 to perform the mail merge. When the code completes, Word is made visible with a new document open. The new document contains form letters that result from a mail merge containing data that is extracted from the Customers table in Northwind.mdb.