VBA实用技巧-导入excel
时 间:2015-11-24 00:13:04
作 者:Allen Lee ID:13894 城市:厦门
摘 要:导入excel
正 文:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'引用Microsoft ActiveX Data Objects 2.x Library
'引用Microsoft AD0 Ext 2.8 for DDL and Security
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim Cat As New ADOX.Catalog
Dim myPath As String
Dim p
Dim sh As Worksheet
Dim SQL As String
Dim F As Boolean
Dim wk As Workbook
myPath = ThisWorkbook.Path & "\"
myData = ThisWorkbook.Path & "\data.accdb"
p = Dir(myPath & "A list *.xls?")
Set wk = Workbooks.Open(ThisWorkbook.Path & "\" & p)
If Not wk Is Nothing Then
If Dir(myData) = "" Then
Cat.Create "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myData
Set Cat = Nothing
F = True '数据库文件不存在标志
End If
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myData
For Each sh In Worksheets
If Not F Then '数据库文件已经存在,先判断同名数据表是否存在,如果存在就删除它
Set rs = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, sh.Name, Empty))
If Not rs.EOF Then
SQL = "Drop TABLE " & sh.Name
cnn.Execute SQL
End If
End If
SQL = "Select * INTO " & sh.Name & " FROM [Excel 12.0;Database=" & wk.FullName _
& ";].[sheet1$" & Range("a1").CurrentRegion.Address(0, 0) & "]"
cnn.Execute SQL
Next
MsgBox " 成功导入 ", vbInformation, " 导入数据库 "
cnn.Close
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Set cnn = Nothing
Else
MsgBox "无须更新"
End If
End Sub
Access软件网QQ交流群 (群号:483923997) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access窗体导出PDF】...(04.08)
- 【Access窗体导出PDF】...(04.07)
- Access两种方式实现即时更...(03.01)
- Access隐藏与显示lacc...(01.12)
- 【Access高效办公】将每个...(12.23)
- Access21点游戏源代码(12.13)
- 【Access窗体导出Exce...(11.15)
- 【Access开发】Acces...(11.14)
- 通过Access宏录入数据到选...(11.10)
学习心得
最新文章
- Access VBA语句If Me...(04.24)
- 【Access修改记录示例】编辑选...(04.22)
- 【Access表名称命名建议】将A...(04.20)
- Access学习笔记--用Acce...(04.19)
- 【Access重复项查询示例】将A...(04.17)
- Access快速开发平台企业版--...(04.16)
- 【Access模块示例】通过模块代...(04.15)
- Access查询里面分组合计功能添...(04.13)
- 【Access删除查询】删除数字最...(04.12)
- 显示文件夹中所有文件的修改时间(04.11)