|
用Excel批量导入- Sub SavePic()
- Dim BtArr() As Byte
- Dim PicPath As String
- Dim PicName As String
- Dim SQL As String
- Dim Fn As Integer
- Dim cnn As New ADODB.Connection
- Dim rst As New ADODB.Recordset
- Dim myPath As String
- Dim myTable As String
- Dim PicSum As Long
- myPath = ThisWorkbook.Path & "\学校管理.mdb"
- myTable = "学生档案"
- On Error GoTo ErrMsg
- cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath
- SQL = "select 学生编号,相片 from " & myTable & " Where IsNull(相片)= True"
- rst.Open SQL, cnn, adOpenKeyset, adLockOptimistic
- Do Until rst.EOF
- PicName = rst(0)
- PicPath = Dir(ThisWorkbook.Path & "\pic" & PicName & ".*")
- If Len(PicPath) <> 0 Then
- PicPath = ThisWorkbook.Path & "\pic" & PicPath
- Fn = FreeFile
- Open PicPath For Binary As #Fn
- ReDim BtArr(LOF(Fn) - 1)
- Get #Fn, , BtArr
- Close #Fn
- rst("相片") = BtArr
- PicSum = PicSum + 1
- End If
- rst.MoveNext
- Loop
- MsgBox "共有 " & PicSum & " 张相片存入数据库" & vbCr _
- & "还有 " & rst.RecordCount - PicSum & " 人未提供相片", , "保存相片"
- Exit Sub
- ErrMsg:
- MsgBox Err.Description, , "错误报告"
- End Sub
复制代码 |
|