使用 vb6 从 Excel 中提取忽略的列

发布于 2024-07-07 09:04:22 字数 2592 浏览 5 评论 0 原文

我正在尝试使用 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

连接并执行查询“SELECT * FROM [Sheet1$]”或什至特定于列的“SELECT [Option#6] FROM [Sheet1$]” ”（参见脚注 1）并循环遍历结果，为 Name9、Option.4 行提供了 Null 值--> Option.6 而不是正确的值 5、6 和 7。似乎与电子表格的连接正在使用“最佳猜测”来决定有效的表限制是什么，并且只需要一组考虑的行数。

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

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

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 & "]")

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

我包含的值得注意的项目参考有：

MS ActiveX 数据对象 2.8 库
MS ActiveX 数据对象 Recordset 2.8 库
MS Excel 11.0 对象库
MS 数据绑定集合 VB 6.0 (SP4)

任何有关此事的帮助将不胜感激！

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

感谢大家！尝试从 KB155512Schema.ini a> onedaywhen 非常棒帖子向我指出了解决方案：

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

我会鼓励任何有类似问题的人阅读这篇文章和评论，因为每个人的解决方案都略有不同。

原文

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:

    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.

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

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.

Notable project references I have included are:

MS ActiveX Data Objects 2.8 Library
MS ActiveX Data Objects Recordset 2.8 Library
MS Excel 11.0 Object Library
MS Data Binding Collection 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 #.

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

.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.

分享到QQ

分享到微博