调试程序-运行时错误 ’-214217904

barryro

1、运行时错误 ’-214217904，至少一个参数没有被指定值。
2、型号YJV 和规格1*1.5  数据源中有，但查找就没有记录。

barryro

自己顶一下

barryro

再顶一下。。。

ione_fox

有这样的[f1]写法？改成'f1'试试

barryro

ione_fox 发表于 2018-8-31 16:02
有这样的[f1]写法？改成'f1'试试

sSelect = "Select [F2] From [Sheet1$] Where [F1]='" & sType & "'"
这个"[F1]"改成“F1"吗？

lsdongjh

1. Sub test()
   
2.     '用法：在单元格D2内录入   =目标值(B2,C2)
   
3.     'vModle,型号单元格的值；vType，规格单元格的值
   
4.     Dim sModel As String, sType As String
   
5.     Dim oConn As Object, oRS As Object
   
6.     Dim sSelect As String
   
7.     Dim i As Long
   
8.     Dim vModel As Variant
   
9.    
   
10.     Set oConn = CreateObject("Adodb.Connection")
    
11.     Set oRS = CreateObject("Adodb.RecordSet")
    
12.    
    
13.     If Val(Application.Version) < 12 Then
    
14.         oConn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;IMEX=0;HDR=NO';Data Source=" & ThisWorkbook.Path & "\数据源.xlsx"
    
15.     Else
    
16.         oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;IMEX=0;HDR=YES';Data Source=" & ThisWorkbook.Path & "\数据源.xlsx"
    
17.     End If
    
18.    
    
19.     For i = 2 To 9
    
20.         sModel = Cells(i, 2).Value
    
21.         sType = Cells(i, 3).Value
    
22.         
    
23.         sSelect = "Select [" & sModel & "] From [Sheet1$A2:C] Where [规格] = '" & sType & "'"
    
24.         If oRS.State = 1 Then oRS.Close
    
25.         
    
26.         oRS.Open sSelect, oConn, 3, 1
    
27.         
    
28.         If oRS.RecordCount > 0 Then
    
29.             Cells(i, 4).Value = oRS.Fields(sModel).Value
    
30.         Else
    
31.             Cells(i, 4).Value = "没有记录"
    
32.         End If
    
33.     Next
    
34.    
    
35.     Set oRS = Nothing
    
36.     oConn.Close
    
37.     Set oConn = Nothing
    
38. End Sub
    

复制代码

barryro

lsdongjh 发表于 2018-8-31 16:46

非常感谢您的帮助！

microyip

把原来oConn.Open里的 IMEX=0 改成 IMEX=1 即可
当然你的循环加进去的位置有点问题，oConn，oRS绑定了之后再循环，无需每循环一次进行一次打开和关闭操作的

microyip

ione_fox 发表于 2018-8-31 16:02
有这样的[f1]写法？改成'f1'试试

当oConn打开方式中的 HDR=No 的设置时，表示读取表格是无标题的，F1 表示所设定读取的区域中的第一列的意思

microyip

不用一个个去读写的

1. Sub 匹配()
   
2.     Dim oConn As Object, oRS As Object, sConnMe As String
   
3.     Dim sSelect As String, sDataBase As String
   
4.         
   
5.     On Error Resume Next
   
6.     Set oConn = CreateObject("Adodb.Connection")
   
7.     If Val(Application.Version) < 12 Then
   
8.         oConn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;IMEX=1;HDR=NO';Data Source=" & ThisWorkbook.Path & "\数据源.xlsx"
   
9.         sConnMe = "[Excel 8.0;IMEX=0;Database=" & ThisWorkbook.FullName & "]."
   
10.     Else
    
11.         oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;IMEX=1;HDR=NO';Data Source=" & ThisWorkbook.Path & "\数据源.xlsx"
    
12.         sConnMe = "[Excel 12.0;IMEX=0;Database=" & ThisWorkbook.FullName & "]."
    
13.     End If
    
14.     Set oRS = CreateObject("Adodb.RecordSet")
    
15.     On Error GoTo CloseRead
    
16.    
    
17.     With Sheet1
    
18.         .UsedRange.Offset(1, 3).ClearContents
    
19.         
    
20.         sDataBase = "Select 'YJV' as 型号,[F1] as 规格,[F2] as 目标 From [Sheet1$] Where Not([F1] Is Null) And [F1]<>'规格'"
    
21.         sDataBase = sDataBase & "Union Select 'ZR-YJV' as 型号,[F1] as 规格,[F3] as 目标 From [Sheet1$] Where Not([F1] Is Null) And [F1]<>'规格'"
    
22.         
    
23.         sSelect = "Select Val(B.目标) From " & sConnMe & "[Sheet1$B:C] A Left Join (" & sDataBase & ") B " & _
    
24.             "On A.型号=B.型号 And A.规格=B.规格"
    
25.             
    
26.         Set oRS = oConn.Execute(sSelect)
    
27.         .[D2].CopyFromRecordset oRS
    
28.     End With
    
29.         oRS.Close
    
30. CloseRead:
    
31.     On Error GoTo 0
    
32.     Set oRS = Nothing
    
33.     oConn.Close
    
34.     Set oConn = Nothing
    
35. End Sub

复制代码