使用vb6忽略列以从excel中提取

I am trying to extract a table of values from an excel (2003) spreadsheet using vb6, the result of which needs to be stored in a (adodb) recordset. The table looks like this:

我试图使用vb6从excel(2003)电子表格中提取值表,其结果需要存储在(adodb)记录集中。该表如下所示:

    Name   Option.1  Option.2  Option.3  Option.4  Option.5  Option.6 
    -----------------------------------------------------------------
    Name1         2         3         4
    Name2         2         3         4
    Name3         2         3         4
    Name4         2         3         4
    Name5         2         3         4
    Name6         2         3         4
    Name7         2         3         4
    Name8         2         3         4
    Name9         2         3         4         5         6         7

Upon connecting and executing the query "SELECT * FROM [Sheet1$]" or even a column-specific, "SELECT [Option#6] FROM [Sheet1$]" (see footnote 1) and looping through the results, I am given Null values for the row Name9, Option.4 --> Option.6 rather than the correct values 5, 6, and 7. It seems the connection to the spreadsheet is using a "best guess" of deciding what the valid table limits are, and only takes a set number of rows into account.

在连接并执行查询“SELECT * FROM [Sheet1 $]”或甚至是特定于列的“SELECT [Option#6] FROM [Sheet1 $]”(参见脚注1)并循环结果时,我被赋予Null行Name9,Option.4 - > Option.6的值而不是正确的值5,6和7.似乎与电子表格的连接使用“最佳猜测”来决定有效的表限制是什么,并且只考虑一定数量的行。

To connect to the spreadsheet, I have tried both connection providers Microsoft.Jet.OLEDB.4.0 and MSDASQL and get the same problem.

为了连接到电子表格,我尝试了两个连接提供程序Microsoft.Jet.OLEDB.4.0和MSDASQL并得到了同样的问题。

Here are the connection settings I use:

以下是我使用的连接设置:

Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & filePath & ";Extended Properties=Excel 8.0;"
    - - - - OR - - - - 
    .Provider = "MSDASQL"
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
                        "DBQ=" & filePath & ";MaxScanRows=0;"
    .CursorLocation = adUseClient
    .Open
End With  
Set rsSelects = New ADODB.Recordset
Set rsSelects = cn.Execute("SELECT [Option#5] FROM " & "[" & strTbl & "]")

This problem only occurs when there are more than 8 rows (excluding the column names), and I have set MaxScanRow=0 for the MSDASQL connection, but this has produced the same results.

仅当有超过8行(不包括列名)时才会出现此问题,并且我已为MSDASQL连接设置了MaxScanRow = 0,但这产生了相同的结果。

Notable project references I have included are:

我所包含的值得注意的项目参考是:

MS ActiveX Data Objects 2.8 Library

MS ActiveX数据对象2.8库

MS ActiveX Data Objects Recordset 2.8 Library

MS ActiveX数据对象Recordset 2.8库

MS Excel 11.0 Object Library

MS Excel 11.0对象库

MS Data Binding Collection VB 6.0 (SP4)

MS数据绑定集合VB 6.0(SP4)

Any help in this matter would be very appreciated!

任何有关此事的帮助将非常感谢!

(1) For some reason, when including a decimal point in the column name, it is interpreted as a #.

(1)由于某种原因,当在列名中包含小数点时,它被解释为#。

Thanks everyone! Halfway through trying to set up a Schema.ini "programmatically" from KB155512 onedaywhen's excellent post pointed me towards the solution:

感谢大家!尝试从KB155512“以编程方式”设置Schema.ini的过程中,当时优秀的帖子向我指出了解决方案:

.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

I would encourage anyone with similar problems to read the post and comments, since there are slight variations to a solution from one person to another.

我会鼓励任何有类似问题的人阅读帖子和评论,因为从一个人到另一个人的解决方案略有不同。

3 个解决方案

#1

You are correct: it is guessing the data type based on a number of rows. There are local machine registry keys you may be able to alter to influence the data type chosen. For more details, see this answer.

你是对的:它根据行数猜测数据类型。您可以更改本地计算机注册表项以影响所选的数据类型。有关详细信息,请参阅此答案。

#2

The Excel ISAM driver by default looks into the first handful of your rows and guesses their data type. Should there be (later in the table) data that does not fit into the initial assumption, it frowns and turns it to NULL.

默认情况下,Excel ISAM驱动程序会查看您的第一批行并猜测其数据类型。如果(在表格的后面)数据不适合初始假设,它会皱眉并将其变为NULL。

Your MaxScanRows=0 setting is the key to this problem. It sounds like it would do the Right Thing (scan the whole table for the data type to use), but really it doesn't.

您的MaxScanRows = 0设置是此问题的关键。听起来它会做正确的事情(扫描整个表格以使用数据类型),但实际上它没有。

See onedaywhen's answer for further details, my first info about KB282263 was not the correct advice.

有关详细信息,请参阅onedaywhen的答案,我关于KB282263的第一个信息不是正确的建议。

#3

The best advice I can give you is to stop doing it in the VB6 environment. Open Excel, press ALT+F11 and load the VBA IDE. Put your code in there. From within this environment you can access the full Excel object model.

我能给你的最好建议就是停止在VB6环境中这样做。打开Excel,按ALT + F11并加载VBA IDE。把你的代码放在那里。在此环境中,您可以访问完整的Excel对象模型。

I've seen many people try and interact with Excel in many different ways and they all have problems. Using either the VBA macro, or Add-in method is a best way I have found of getting at the data. It's how Microsoft get Excel and Project to integrate with TFS.

我见过很多人尝试以多种不同的方式与Excel交互,他们都有问题。使用VBA宏或Add-in方法是我找到获取数据的最佳方法。这就是微软如何让Excel和Project与TFS集成。

Sometimes you need to rethink the process a little for this approach to be suitable. E.g. You may need to get the user who is using the spreadsheet to run a macro that will push the data out of the spreadsheet instead of you running a process to pull the data from the spreadsheet but usually it is quite doable.

有时您需要重新考虑该过程,以使此方法合适。例如。您可能需要让正在使用电子表格的用户运行一个宏,该宏将数据从电子表格中推出,而不是运行从电子表格中提取数据的过程,但通常这是非常可行的。

#1