Visual Basic Q&A

Symptoms
When you use the New operator or CreateObject function in Microsoft Visual Basic to create an instance of a Microsoft Office application, you may get the following error message:

Run-time error ‘429′: ActiveX component can’t create objectThis error occurs when the requested Automation object could not be created by COM, and is therefore unavailable to Visual Basic. The error is typically seen on certain computers but not others.
This article provides some troubleshooting tips to help you diagnose and resolve common problems that are known to cause this error.
Resolution
Unlike some errors in Visual Basic, there is no one cause to an error 429. The problem happens because of an error in the application or system configuration, or a missing or damaged component. Finding the exact cause is a matter of eliminating possibilities. If you encounter this error on a client computer, there are a number of things you will need to check to isolate and resolve the error.
The items later give you some practical suggestions for troubleshooting this error when you work with Office Applications. Some of this information may also apply to non-Office COM servers as well, but this article assumes you are trying to automate Microsoft Office.
Checking the CodeThe first place to begin looking for the problem is in the code. Before you can troubleshoot the error, you need to know where the error occurs. Try to narrow it down to a single line of code.
When you find the code that is failing, try to do the following:
Make sure the code uses explicit object creation. Any problem is easier to spot and identify if the problem is narrowed to a single action. For example, do not do the following:

Application.Documents.Add ‘DON’T USE THIS!!
or:

Dim oWordApp As New Word.Application ‘DON’T USE THIS!!’… some other codeoWordApp.Documents.Add Both of these methods use implicit object creation. Microsoft Word is not started until the variable is called at least once. Since the variable could be called in different parts of the program, this could make the problem hard to localize. Also, it is not clear whether the problem is with creating the Application object or the Document object.
Instead, make explicit calls to create each object separately:

Dim oWordApp As Word.ApplicationDim oDoc As Word.DocumentSet oWordApp = CreateObject(“Word.Application”)’… some other codeSet oDoc = oWordApp.Documents.Add This makes the problem easier to isolate and makes the code more readable.When creating an instance of an Microsoft Office application, use CreateObject instead of New. CreateObject more closely maps to the creation process used by most Visual C++ clients, and allows for possible changes in the server’s CLSID between versions. CreateObject can be used with both early-bound and late-bound objects.Verify that the ProgID string passed to CreateObject is correct and that it is version independent (that is, use “Excel.Application” rather than “Excel.Application.8″). It could be that the system that is failing has an older or newer version of Microsoft Office than the version you specified in the ProgID.To aid in debugging applications that cannot be run in the IDE, use the Erl command to report the line number of the line that fails. For example, the following code will tell you which Automation object cannot be created (Word or Excel):

Dim oWord As Word.ApplicationDim oExcel As Excel.ApplicationOn Error Goto err_handler1: Set oWord = CreateObject(“Word.Application”)2: Set oExcel = CreateObject(“Excel.Application”)’ … some other codeerr_handler:MsgBox “The code failed at line ” & Erl, vbCritical Use a combination of message boxes and line numbers to track down the error.Try using late binding (that is, Dim oWordApp As Object). Early bound objects require their custom interfaces to be marshaled across process boundaries. If there is a problem marshaling a custom interface during CreateObject or New, you will get an error 429. A late bound object uses a system-defined interface (IDispatch) that does not require a custom proxy in order to be marshaled. Try using a late bound object to see if this makes a difference.
If the problem occurs only when the object is early-bound, the problem is with the server application, and can typically be corrected by reinstalling the application (see later).If you are automating from ASP or an MTS component, use CreateObject instead of Server.CreateObject(). Using Server.CreateObject will instantiate the Office application under the identity of an MTS package which is known to cause problems with Microsoft Office.
Checking the Automation ServerThe most common reasons for an error with CreateObject or New are problems with the server application itself. Typically, these problems are with the configuration or setup of the application. Here are some items to check:
Verify the Microsoft Office application you want to Automate is installed on the local computer, and make sure that you can start the application from the Start and then Run dialog box. If the program cannot be started manually, it will not work through automation.Re-register the application by typing the path to the server in the Start and then Run dialog box, and then append /RegServer to the end of the line. Press OK. This should silently run the application and re-register it as a COM server. If the problem is with a missing registry key, this will typically correct it.Check the LocalServer32 key under the CLSID for the application you want to Automate. Make sure it points to the correct location for the application, and make sure the path name is in a short path (DOS 8.3) format. While it is not a requirement that a server be registered using a short path name, long path names that include embedded spaces have been known to cause problems on some systems (see later).
To check the path key stored for the server, start the Windows Registry Editor by typing regedit in the Start and then Run dialog box. Navigate to the HKEY_CLASSES_ROOT\Clsid key. Under this key you will find the CLSIDs for the registered automation servers on the system. Using the values later, find the key that represents the Office application you want to Automate and check its LocalServer32 key for the path.

+========================+=========================================+| Office Server| CLSID Key|+========================+=========================================+| Access.Application| {73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9}|+————————+—————————————–+| Excel.Application| {00024500-0000-0000-C000-000000000046}|+————————+—————————————–+| FrontPage.Application| {04DF1015-7007-11D1-83BC-006097ABE675}|+————————+—————————————–+| Outlook.Application| {0006F03A-0000-0000-C000-000000000046}|+————————+—————————————–+| PowerPoint.Application | {91493441-5A91-11CF-8700-00AA0060263B}|+————————+—————————————–+| Word.Application| {000209FF-0000-0000-C000-000000000046}|+————————+—————————————–+ Does the path match the actual location of the file? Be aware that short path names can give you the impression that a path is correct when it may not be. For example, both Microsoft Office and Microsoft Internet Explorer (if installed in their default locations) will have a short path similar to “C:\PROGRA~1\MICROS~X\” where X is some number. It is not immediately obvious that you are looking at from a short path name.
You can test that the path is indeed correct by copying the value from the registry and pasting it into the Start and then Run dialog box (remove the /Automation switch before running the application). Does the application start when you select OK? If yes, then the server is registered correctly. If not, you should replace the value of the LocalServer32 key with the correct path (use a short path name if possible).Problems have been known to occur when automating Word or Excel if either the Normal.dot template (Word) or the Excel.xlb resource file (Excel) has become corrupt. To test if a corruption has occurred, search the local hard drives to find all instances of Normal.dot or *.xlb. (Please note that if you are running Windows 2000, Windows NT, or Windows 95/98 with profiles enabled, you may find multiple copies of these files, one for each user profile on the system.) Temporarily rename the Normal.dot file(s) or the *.xlb file(s), and re-run your Automation test (Word and Excel will create these files if they cannot find them). Does the code now work? If yes, then the files you renamed should be deleted since they are corrupt. If not, you should rename them back to their original names so any custom settings saved in these files won’t be lost.If you are on a Windows NT, Windows 2000, Windows XP, or Windows Server 2003 system, run the application under the Administrator account. Office servers require read/write access to the registry and disk drive, and may not properly load if your current security settings deny this privilege.
Checking the SystemSystem configuration can also cause problems with the creation of out-of-process COM servers. The following are some things to check on systems where the error occurs: Does the problem happen with any out-of-process server? If you have an application that just uses a particular COM server (for example, Word), you’ll want to test a different out-of-process server to make sure the problem is not with COM layer itself. If no out-of-process COM server can be created on that system, then a reinstallation of the OLE system files (see below) or a reinstallation of the operating system will be required to resolve the issue.Check the version numbers for the OLE system files that manage Automation. These files are typically installed as a set, and should match build numbers. An improperly configured setup utility can mistakenly install the files separately, causing them to become mismatched. To avoid problems with Automation, you should check the files to make sure the files match builds.
You will find the Automation files in the Windows\System or Winnt\System32 directory. The following is the list of files to check:

+—————+————-+—————-+| File Name|Version| Date Modified|+—————+————-+—————-+| Asycfilt.dll|2.40.4275| March 08, 1999 || Oleaut32.dll|2.40.4275| March 08, 1999 || Olepro32.dll|5.0.4275| March 08, 1999 || Stdole2.tlb|2.40.4275| March 08, 1999 |+—————+————-+—————-+ Check the file version by right-clicking on the file in Explorer and selecting Properties from the popup menu. The values most important are the last four digits of the file version (the build number) and the date last modified. You want to make sure these values are the same for all the Automation files.
Please note that the version numbers and dates given above are for example purposes only. Your values may differ. The important thing is that these values match each other, rather than this table.
If the files don’t match build numbers or modified dates, you can download a self-extracting utility that will update your Automation files. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290887?(http://support.microsoft.com/kb/290887/) VBRun60sp6.exe installs Visual Basic 6.0 SP6 run-time filesWindows NT 4.0 has a known problem with starting Automation servers that live in a folder that contains an embedded space in the name, and/or resembles another folder whose first 8 characters are identical. For example, a server living in C:\Program Files\SomeFolder may fail to start during a call to CreateObject if there is another folder on the system called C:\Program Stuff\SomeFolder. For more information, see the following Knowledge Base article:For additional information about this problem and steps to workaround it, click the article number below to view the article in the Microsoft Knowledge Base:
185126?(http://support.microsoft.com/kb/185126/EN-US/) BUG: COM/OLE Server Fails to Start on Windows NT 4.0
Reinstalling Microsoft OfficeIf none of the preceding steps helps to resolve the problem, consider uninstalling and reinstalling Microsoft Office. Microsoft recommends that you uninstall the existing version first, and then reinstall from the original installation disks.
For a complete list of the items to be removed, please see the following Knowledge Base articles:
219423?(http://support.microsoft.com/kb/219423/EN-US/) OFF2000: How to Completely Remove Microsoft Office 2000
158658?(http://support.microsoft.com/kb/158658/EN-US/) OFF97: How to Completely Remove Microsoft Office 97

Symptoms
This article discusses the use of ActiveX Data Objects (ADO) with Microsoft Excel spreadsheets as a data source. The article also highlights syntax issues and limitations specific to Excel. This article does not discuss OLAP or PivotTable technologies or other specialized uses of Excel data.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
303814?(http://support.microsoft.com/kb/303814/EN-US/) How To Use ADOX with Excel Data from Visual Basic or VBA
Resolution
INTRODUCTION The rows and columns of a Microsoft Excel spreadsheet closely resemble the rows and columns of a database table. As long as users keep in mind that Microsoft Excel is not a relational database management system, and recognize the limitations that this fact imposes, it often makes sense to take advantage of Excel and its tools to store and analyze data.
Microsoft ActiveX Data Objects makes it possible to treat an Excel workbook as if it were a database. This article discusses how to accomplish this in the following sections: Connect to Excel with ADORetrieve and Edit Excel Data with ADORetrieve Data Source Structure (Metadata) from ExcelNOTE: The testing for this article was conducted with Microsoft Data Access Components (MDAC) 2.5 on Microsoft Windows 2000 with Visual Basic 6.0 Service Pack 3 and Excel 2000. This article may not acknowledge or discuss differences in behavior that users may observe with different versions of MDAC, Microsoft Windows, Visual Basic, or Excel.
Connect to Excel with ADO ADO can connect to an Excel data file with either one of two OLE DB Providers included in MDAC: Microsoft Jet OLE DB Provider -or-
Microsoft OLE DB Provider for ODBC DriversHow to Use the Microsoft Jet OLE DB Provider The Jet Provider requires only two pieces of information in order to connect to an Excel data source: the path, including the file name, and the Excel file version.
Jet Provider Using a Connection String

Dim cn as ADODB.ConnectionSet cn = New ADODB.ConnectionWith cn .Provider = “Microsoft.Jet.OLEDB.4.0″ .ConnectionString = “Data Source=C:\MyFolder\MyWorkbook.xls;” & _”Extended Properties=Excel 8.0;” .OpenEnd With Provider Version: It is necessary to use the Jet 4.0 Provider; the Jet 3.51 Provider does not support the Jet ISAM drivers. If you specify the Jet 3.51 Provider, at run time you receive the following error message:

Couldn’t find installable ISAM. Excel Version: Specify Excel 5.0 for an Excel 95 workbook (version 7.0 of Excel), and Excel 8.0 for an Excel 97, Excel 2000, or Excel 2002 (XP) workbook (versions 8.0, 9.0, and 10.0 of Excel).
Jet Provider Using the Data Link Properties Dialog Box
If you use the ADO Data Control or the Data Environment in your application, then the Data Link Properties dialog box is displayed to gather the necessary connection settings.
On the Provider tab, select the Jet 4.0 Provider; the Jet 3.51 Provider does not support the Jet ISAM drivers. If you specify the Jet 3.51 Provider, at run time you receive the following error message:

Couldn’t find installable ISAM. On the Connection tab, browse to your workbook file. Ignore the “User ID” and “Password” entries, because these do not apply to an Excel connection. (You cannot open a password-protected Excel file as a data source. There is more information on this topic later in this article.)On the All tab, select Extended Properties in the list, and then click Edit Value. Enter Excel 8.0; separating it from any other existing entries with a semicolon (;). If you omit this step, you receive an error message when you test your connection, because the Jet Provider expects a Microsoft Access database unless you specify otherwise.Return to the Connection tab and click Test Connection. Note that a message box appears informing you that the process has succeeded. Other Jet Provider Connection Settings
Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data “disappears” to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes. If you do not have column headings, you need to specify HDR=No; the provider names your fields F1, F2, etc. Because the Extended Properties string now contains multiple values, it must be enclosed in double quotes itself, plus an additional pair of double quotes to tell Visual Basic to treat the first set of quotes as literal values, as in the following example (where extra spaces have been added for visual clarity).

.ConnectionString = “Data Source=C:\MyFolder\MyWorkbook.xls;” & _”Extended Properties=” ” Excel 8.0; HDR=No;” ” ” Using Microsoft OLE DB Provider for ODBC Drivers The provider for ODBC drivers (which this article refers to as the “ODBC Provider” for the sake of brevity) also requires only two (2) pieces of information in order to connect to an Excel data source: the driver name, and the workbook path and filename.
IMPORTANT: An ODBC connection to Excel is read-only by default. Your ADO Recordset LockType property setting does not override this connection-level setting. You must set ReadOnly to False in your connection string or your DSN configuration if you want to edit your data. Otherwise, you receive the following error message:

Operation must use an updateable query. ODBC Provider Using a DSN-Less Connection String

Dim cn as ADODB.ConnectionSet cn = New ADODB.ConnectionWith cn .Provider = “MSDASQL” .ConnectionString = “Driver={Microsoft Excel Driver (*.xls)};” & _”DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;” .OpenEnd With ODBC Provider Using a Connection String with a DSN

Dim cn as ADODB.ConnectionSet cn = New ADODB.ConnectionWith cn .Provider = “MSDASQL” .ConnectionString = “DSN=MyExcelDSN;” .OpenEnd With ODBC Provider Using the Data Link Properties Dialog Box
If you use the ADO Data Control or the Data Environment in your application, then the Data Link Properties dialog box is displayed to gather the necessary connection settings. On the Provider tab, select Microsoft OLE DB Provider for ODBC Drivers.On the Connection tab, select the existing DSN that you want to use, or choose Use connection string. This brings up the standard DSN configuration dialog box to gather the necessary connection settings. Remember to deselect the default read-only setting if desired, as mentioned previously.Return to the Connection tab, and click Test Connection. Note that a message box appears informing you that the process has succeeded.Other ODBC Provider Connection Settings
Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings, which can be used as field names. If this is not the case, you must turn this setting off, or your first row of data “disappears” to be used as field names. This is done by adding the optional FirstRowHasNames= setting to the connection string. The default, which does not need to be specified, is FirstRowHasNames=1, where 1 = True. If you do not have column headings, you need to specify FirstRowHasNames=0, where 0 = False; the driver names your fields F1, F2, and so forth. This option is not available in the DSN configuration dialog box.
However, due to a bug in the ODBC driver, specifying the FirstRowHasNames setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always treats the first row in the specified data source as field names. For additional informationon the Column Heading bug, click the article number below to view the article in the Microsoft Knowledge Base:
288343?(http://support.microsoft.com/kb/288343/EN-US/) BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header SettingRows to Scan: Excel does not provide ADO with detailed schema information about the data it contains, as a relational database would. Therefore, the driver must scan through at least a few rows of the existing data in order to make an educated guess at the data type of each column. The default for “Rows to Scan” is eight (8) rows. You can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. This is done by adding the optional MaxScanRows= setting to the connection string, or by changing the Rows to Scan setting in the DSN configuration dialog box.
However, due to a bug in the ODBC driver, specifying the Rows to Scan (MaxScanRows) setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always scans the first 8 rows in the specified data source in order to determine each column’s datatype.
For additional information about the Rows to Scan bug, including a simple workaround, click the article number below to view the article in the Microsoft Knowledge Base:
189897?(http://support.microsoft.com/kb/189897/EN-US/) XL97: Data Truncated to 255 Characters with Excel ODBC DriverOther Settings: If you construct your connection string by using the Data Link Properties dialog box, you may notice some other Extended Properties settings added to the connection string that are not absolutely necessary, such as:

… DefaultDir=C:\WorkbookPath;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5; “Collating Sequence” Error Message in the Visual Basic Editor
In the Visual Basic design environment with certain versions of MDAC, you may see the following error message the first time your program connects to an Excel data source at design time:

Selected collating sequence not supported by the operating system. This message appears only in the IDE and will not appear in the compiled version of the program. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
246167?(http://support.microsoft.com/kb/246167/EN-US/) PRB: Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLSConsiderations That Apply to Both OLE DB ProvidersA Caution about Mixed Data Types
As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.
For example: In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values. In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values. As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.
To work around this problem for read-only data, enable Import Mode by using the setting “IMEX=1″ in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
194124?(http://support.microsoft.com/kb/194124/EN-US/) PRB: Excel Values Returned as NULL Using DAO OpenRecordsetYou Cannot Open a Password-Protected Workbook
If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection settings, unless the workbook file is already open in the Microsoft Excel application. If you try, you receive the following error message:

Could not decrypt file. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
211378?(http://support.microsoft.com/kb/211378/EN-US/) XL2000: “Could Not Decrypt File” Error with Password Protected File
Retrieve and Edit Excel Data with ADO This section discusses two aspects of working with your Excel data: How to select data -and-
How to change dataHow to Select Data There are several ways to select data. You can:
Select Excel data with code.Select Excel data with the ADO Data control.Select Excel data with Data Environment commands.Select Excel Data with Code Your Excel data may be contained in your workbook in one of the following:
An entire worksheet.A named range of cells on a worksheet.An unnamed range of cells on a worksheet.Specify a Worksheet
To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:

strQuery = “SELECT * FROM [Sheet1$]” You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example:

strQuery = “SELECT * FROM `Sheet1$`” Microsoft prefers the square brackets, which are the standing convention for problematic database object names.
If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message:

… the Jet database engine could not find the specified object If you use the dollar sign but omit the brackets, you will see the following error message:

Syntax error in FROM clause. If you try to use ordinary single quotes, you receive the following error message:

Syntax error in query. Incomplete query clause. Specify a Named Range
To specify a named range of cells as your recordsource, simply use the defined name. For example:

strQuery = “SELECT * FROM MyRange” Specify an Unnamed Range
To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

strQuery = “SELECT * FROM [Sheet1$A1:B10]” A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.
A caution about specifying ranges: When you specify a worksheet as your recordsource, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.
With MDAC versions prior to 2.5, when you specify a named range, you cannot add new records beyond the defined limits of the range, or you receive the following error message:

Cannot expand named range. Select Excel Data with the ADO Data Control After you specify the connection settings for your Excel data source on the General tab of the ADODC Properties dialog box, click on the Recordsource tab. If you choose a CommandType of adCmdText, you can enter a SELECT query in the Command Text dialog box with the syntax described previously. If you choose a CommandType of adCmdTable, and you are using the Jet Provider, the drop-down list displays both the named ranges and worksheet names that are available in the selected workbook, with named ranges listed first.
This dialog box properly appends the dollar sign to worksheet names, but does not add the necessary square brackets. As a result, if you simply select a worksheet name and click OK, you receive the following error message later:

Syntax error in FROM clause. You must manually add the square brackets around the worksheet name. (This combo box does allow editing.) If you are using the ODBC Provider, you see only named ranges listed in this drop-down list. However, you can manually enter a worksheet name with the appropriate delimiters. Select Excel Data with Data Environment Commands After setting up the Data Environment Connection for your Excel data source, create a new Command object. If you choose a Source of Data of SQL Statement, you can enter a query in the textbox using the syntax described previously. If you choose a Source of Data of Database Object, select Table in the first drop-down list, and you are using the Jet Provider, the drop-down list displays both named ranges and worksheet names available in the selected workbook, with named ranges listed first. (If you choose a worksheet name in this location, you do not need to add square brackets around the worksheet name manually as you do for the ADO Data Control.) If you are using the ODBC Provider, you see only named ranges listed in this drop-down list. However, you can manually enter a worksheet name. How to Change Excel Data: Edit, Add, and DeleteEdit
You can edit Excel data with the normal ADO methods. Recordset fields which correspond to cells in the Excel worksheet containing Excel formulas (beginning with “=”) are read-only and cannot be edited. Remember that an ODBC connection to Excel is read-only by default, unless you specify otherwise in your connection settings. See earlier under “Using the Microsoft OLE DB Provider for ODBC Drivers.”
Add
You can add records to your Excel recordsource as space allows. However, if you add new records outside the range that you originally specified, these records are not visible if you requery on the original range specification. See earlier under “A caution about specifying ranges.”
In certain circumstances, when you use the AddNew and Update methods of the ADO Recordset object to insert new rows of data into an Excel table, ADO may insert the data values into the wrong columns in Excel. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
314763?(http://support.microsoft.com/kb/314763/EN-US/) FIX: ADO Inserts Data into Wrong Columns in ExcelDelete
You are more restricted in deleting Excel data than data from a relational data source. In a relational database, “row” has no meaning or existence apart from “record”; in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot: Delete an entire record at once or you receive the following error message:

Deleting data in a linked table is not supported by this ISAM. You can only delete a record by blanking out the contents of each individual field.Delete the value in a cell containing an Excel formula or you receive the following error message:

Operation is not allowed in this context. You cannot delete the empty spreadsheet row(s) in which the deleted data was located, and your recordset will continue to display empty records corresponding to these empty rows.A caution about editing Excel data with ADO: When you insert text data into Excel with ADO, the text value is preceded with a single quote. This may cause problems later in working with the new data.
Retrieve Data Source Structure (Metadata) from Excel You can retrieve data about the structure of your Excel data source (tables and fields) with ADO. Results differ slightly between the two OLE DB Providers, although both return at least the same small number of useful fields of information. This metadata can be retrieved with the OpenSchema method of the ADO Connection object, which returns an ADO Recordset object. You can also use the more powerful Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) library for this purpose. In the case of an Excel data source however, where a “table” is either a worksheet or a named range, and a “field” is one of a limited number of generic datatypes, this additional power is not useful. Query Table Information Of the various objects available in a relational database (tables, views, stored procedures, and so forth), an Excel data source exposes only table equivalents, consisting of the worksheets and the named ranges defined in the specified workbook. Named ranges are treated as “Tables” and worksheets are treated as “System Tables,” and there is not much useful table information you can retrieve beyond this “table_type” property. You request a list of the available tables in the workbook with the following code:

Set rs = cn.OpenSchema(adSchemaTables) The Jet Provider returns a recordset with nine (9) fields, of which it populates only four (4):
table_nametable_type (“Table” or “System Table”)date_created date_modified The two date fields for a given table always show the same value, which appears to be the “date last modified.” In other words, “date_created” is not reliable.
The ODBC Provider also returns a recordset with nine (9) fields, of which it populates only three (3):
table_catalog, the folder in which the workbook is located.table_name.table_type, as noted earlier. According to the ADO documentation, it is possible to retrieve a list of worksheets only, for example, by specifying the following additional criteria to the OpenSchema method:

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, “System Table”)) Unfortunately, this does not work against an Excel data source with MDAC versions later than 2.0, using either provider. Query Field Information Every field (column) in an Excel data source is one of the following datatypes:
numeric (ADO datatype 5, adDouble)currency (ADO datatype 6, adCurrency)logical or boolean (ADO datatype 11, adBoolean)date (ADO datatype 7, adDate, using Jet; 135, adDBTimestamp, using ODBC)text (an ADO ad…Char type, such as 202, adVarChar, 200, adVarWChar or similar) The numeric_precision for a numeric column is always returned as 15 (which is the maximum precision in Excel); the character_maximum_length of a text column is always returned as 255 (which is the maximum display width, but not the maximum length, of text in an Excel column). There is not much useful field information that you can obtain beyond the data_type property. You request a list of the available fields in a table with the following code:

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, “TableName”, Empty)) The Jet Provider returns a recordset that contains 28 fields, of which it populates eight (8) for numeric fields and nine (9) for text fields. The useful fields are likely to be these:
table_namecolumn_nameordinal_positiondata_type The ODBC Provider returns a recordset containing 29 fields, of which it populates ten (10) for numeric fields and 11 for text fields. The useful fields are the same as earlier. Enumerate Tables and Fields and Their Properties Visual Basic code (such as the following sample) can be used to enumerate the tables and columns in an Excel data source and the available fields of information about each. This sample outputs its results to a Listbox, List1, on the same form.

Dim cn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim intTblCnt As Integer, intTblFlds As IntegerDim strTbl As StringDim rsC As ADODB.RecordsetDim intColCnt As Integer, intColFlds As IntegerDim strCol As StringDim t As Integer, c As Integer, f As IntegerSet cn = New ADODB.ConnectionWith cn .Provider = “Microsoft.Jet.OLEDB.4.0″ .ConnectionString = “Data Source=” & App.Path & _”\ExcelSrc.xls;Extended Properties=Excel 8.0;” ‘.Provider = “MSDASQL” ‘.ConnectionString = “Driver={Microsoft Excel Driver (*.xls)};” & _”DBQ=” & App.Path & “\ExcelSrc.xls; ” .CursorLocation = adUseClient .OpenEnd WithSet rsT = cn.OpenSchema(adSchemaTables)intTblCnt = rsT.RecordCountintTblFlds = rsT.Fields.CountList1.AddItem “Tables: ” & intTblCntList1.AddItem “——————–”For t = 1 To intTblCnt strTbl = rsT.Fields(“TABLE_NAME”).Value List1.AddItem vbTab & “Table #” & t & “: ” & strTbl List1.AddItem vbTab & “——————–” For f = 0 To intTblFlds – 1 List1.AddItem vbTab & rsT.Fields(f).Name & _vbTab & rsT.Fields(f).Value Next List1.AddItem “——————–” Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTbl, Empty)) intColCnt = rsC.RecordCount intColFlds = rsC.Fields.Count For c = 1 To intColCnt strCol = rsC.Fields(“COLUMN_NAME”).Value List1.AddItem vbTab & vbTab & “Column #” & c & “: ” & strCol List1.AddItem vbTab & vbTab & “——————–” For f = 0 To intColFlds – 1 List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _vbTab & rsC.Fields(f).Value Next List1.AddItem vbTab & vbTab & “——————–” rsC.MoveNext Next rsC.Close List1.AddItem “——————–” rsT.MoveNextNextrsT.Closecn.Close Use the Data View Window If you create a data link to an Excel data source in the Visual Basic Data View window, the Data View window displays the same information that you can retrieve programmatically as described earlier. In particular, note that the Jet Provider lists both worksheets and named ranges under “Tables,” where the ODBC Provider shows only named ranges. If you are using the ODBC Provider and have not defined any named ranges, the “Tables” list will be empty. Excel Limitations The use of Excel as a data source is bound by the internal limitations of Excel workbooks and worksheets. These include, but are not limited to:
Worksheet size: 65,536 rows by 256 columnsCell contents (text): 32,767 charactersSheets in a workbook: limited by available memoryNames in a workbook: limited by available memory

Symptoms
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
Resolution
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe(http://download.microsoft.com/download/excel2000/sample/12_27_2000/w98nt42kme/en-us/excelado.exe)Release Date: December 12, 2000
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591?(http://support.microsoft.com/kb/119591/EN-US/)How to Obtain Microsoft Support Files from Online ServicesMicrosoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.Why Use ADO?The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel: Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format ismaintained. If you require “conditional” formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.Jet OLE DB Provider Specifics for Excel WorkbooksThe Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks: Excel 3.0Excel 4.0Excel 5.0Excel 8.0NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:

Dim oConn As New ADODB.ConnectionWith oConn.Provider = “Microsoft.Jet.OLEDB.4.0″.Properties(“Extended Properties”).Value = “Excel 8.0″.Open “C:\Book1.xls”‘…..CloseEnd With ?????-or-

Dim oConn As New ADODB.ConnectionoConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=C:\Book1.xls;” & _”Extended Properties=”"Excel 8.0;”"”oConn.Close Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.

oRS.Open “Select * from [Sheet1$]“, oConn, adOpenStatic Use a range with a defined name (for example, [Table1]).

oRS.Open “Select * from Table1″, oConn, adOpenStatic Use a range with a specific address (for example, [Sheet1$A1:B10]).

oRS.Open “Select * from [Sheet1$A1:B10]“, oConn, adOpenStatic Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to “guess” the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.Files Included with the SampleThe ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project FilesExcelADO.vbpForm1.frmForm1.frxActive Server PagesEmpData.aspOrders.aspMicrosoft Excel WorkbooksOrdersTemplate.xlsEmpDataTemplate.xlsProductsTemplate.xlsSourceData.xlsMicrosoft Access DatabaseData.mdbHow to Use the SampleExtract the contents of the .exe file to a folder.
To use the Visual Basic project: In Visual Basic, open the ExcelADO.vbp file. On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.Press the F5 key to run the program. A form for the demonstration appears.Click Sample 1.This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:On the Insert menu in Excel, select Names, and then select Define. In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel’s OFFSET function, to compute a total on the data added to the worksheet. Quit Microsoft Excel and return to the Visual Basic application.Click Sample 2.This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3.This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new “Products” worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any “conditional” formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4.This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet (“Sheet1$” or “Sheet2$”) for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.To use the Active Server Pages (ASP):Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.Copy the following files to the folder you created in the previous step:EmpData.aspOrders.aspData.mdbEmpDataTemplate.xlsOrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application. (c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.