改了一个
Sub 空数据查询()
[B10:L2200].ClearContents
Set xSh = ThisWorkbook.Worksheets(Sheet1.Name)
Set sRan = xSh.Range("C8")
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" _
& ThisWorkbook.Path & "\" & ThisWorkbook.Name
conn.Open
Sheet1.[C9].Select
If conn.State = adStateOpen Then
' 吕布更改于20110928
' 1. 生成SQL
sSql = "INSERT INTO [v数据库] " & _
"(ID,入库日期,责任人,书类,编号,价值,密级,重新入库日期,借调日期,毁损转出日期,状态) " & _
"VALUES (vID,v入库日期,v责任人,v书类,v编号,v价值,v密级,v重新入库日期,v借调日期,v毁损转出日期,v状态)"
sSql = Replace(sSql, "v数据库", Sheet2.Name & "$")
sSql = Replace(sSql, "vID", Application.Max(Sheets("数据库").Range("A2:A65000")) + 1)
Dim i As Long, lb As Long
Dim arrFields As Variant
arrFields = Array("v入库日期", "v责任人", "v书类", "v编号", "v价值", "v密级", "v重新入库日期", "v借调日期", "v毁损转出日期", "v状态")
lb = LBound(arrFields)
For i = LBound(arrFields) To UBound(arrFields)
sSql = Replace(sSql, arrFields(i), IIf(Len(Cells(9, i - lb + 3)) > 0, Cells(9, i - lb + 3), "Null"))
Next i
' 2. 执行插入
'sRan.Offset(2, -1).CopyFromRecordset
conn.Execute (sSql)
conn.Close
If Sheet1.[B10] = "" Then
MsgBox "“" & Sheet2.Name & "”无数据!"
End If
End If
Set conn = Nothing
End Sub |