|
本帖最后由 dasda 于 2013-6-20 16:46 编辑
Sub 更新数据()
Dim cnn As Object, SQL As String, arr, i&
arr = Range("b1:b" & Range("b65536").End(xlUp).Row)
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no';Data Source=" & ThisWorkbook.Path & "\B.xls"
For i = 3 To UBound(arr)
If IsNumeric(arr(i, 1)) Then
SQL = "update [Sheet1$C" & i & ":C" & i & "] set f1=" & arr(i, 1)
Else
SQL = "update [Sheet1$C" & i & ":C" & i & "] set f1='" & arr(i, 1) & "'"
End If
cnn.Execute SQL
Next
cnn.Close
Set cnn = Nothing
End Sub
这里的F1,我猜是,因为连接数据库时没有指定属性HEADER=NO,所以,字段名就以F1(Fields1)这样的格式来命名,如果有多个有效字段,那字段名就依顺序为F1,F2,,F3这种格式。
|
|