Excel 工作表中的非空字段在记录集中显示为空白
我正在使用 Excel VBA 编写一个宏,用于从 Excel 工作表读取数据并对其进行处理。基本上,我将数据从 Excel 工作簿 A
复制到 Excel 工作簿 B
中的工作表 X
。工作簿B
包含一个宏,该宏执行此复制操作,然后将数据从工作表X
读取到记录集中。
我遇到了一个非常奇怪的问题。我的问题是,当我尝试打印记录集值时,记录集中的一个字段显示为空白。
这是我的代码中有问题的部分。 packageName
被传递到包含字符串的此函数中。当我尝试打印该项目的记录集值时,记录集对象 objRecordset
错误地获取 Name
字段并显示为空白,即使它非空。其他字段打印出来就好了。名称字段包含字母和数字,例如 ABC1232WHSJ、ABCD3456
。知道出了什么问题吗?
Dim objConnect As ADODB.Connection, objRecordset
Set objConnect = New ADODB.Connection
objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.Open "Select * FROM [Sheet1$] WHERE Package LIKE '" & _
packageName & "'", objConnect, adOpenStatic, adLockOptimistic, adCmdText
Debug.Print objRecordset.Fields.Item("Package")
Debug.Print objRecordset.Fields.Item("Name")
I am using excel vba to write a macro that reads data from an Excel sheet and processes it. Basically, I am copying data from an Excel workbook A
to a worksheet X
in Excel workbook B
. Workbook B
contains a macro that does this copying and then reads data into a recordset from worksheet X
.
I'm running into a real weird issue. My problem is there is one field in the recordset that shows up as blank when I try to print the recordset value.
This is the part of my code that has a problem. packageName
is passed into this function that contains a string. The recordset object objRecordset
fetches the Name
field incorrectly and shows up as blank when I try to print the recordset value for the item, even though it is non-empty. The other fields are printed out just fine. The name field contains letters and numbers like ABC1232WHSJ, ABCD3456
. Any idea what is going wrong?
Dim objConnect As ADODB.Connection, objRecordset
Set objConnect = New ADODB.Connection
objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.Open "Select * FROM [Sheet1$] WHERE Package LIKE '" & _
packageName & "'", objConnect, adOpenStatic, adLockOptimistic, adCmdText
Debug.Print objRecordset.Fields.Item("Package")
Debug.Print objRecordset.Fields.Item("Name")
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我也遇到过同样的问题。
验证列是否具有相同的数字格式。
当您使用 ado 连接时,ado 使用第一行的格式。例如,如果是一个数字,第二个是字符串,则表中的值为空(如果创建表,否则您会收到错误)
解决方案(对我有用)是在导入数据之前打开工作簿,并使用需要格式,我使用范围内的属性数字格式。
[]的
I've had same problem.
Verify if columns has same number format.
When you use ado connection ado use a format for you first line . if is a number ,for example, and second is string a value in table is empty (if create a table,else you receives a error)
The solution (work's for me) is open workbook before import data ,and format a column with a need format, i use propertie numberformat from range.
[]'s