可能ACCESS数据量过大,导入EXCEL表,超过65536行或1048576行。在此以OFFICE2003为例。
假如有一个数据库DATABASE.MDB,其中有一张表DATE1,在VBA中建立数据通道,然后用命令”Select * FROM DATE1“返回全体记录,利用循环一条一条拷贝到EXCEL表中,每复制65536条记录则换一张表
运行前把此文件复制到MDB所在目录下,然后修改代码中的数据库名称和表格名称,具体位置在代码中有注释
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Private Sub CommandButton8_Click()
Unload Me
End Sub
Private Sub CommandButton9_Click()
Dim RecordCount, SheetNO, Line
Application.ScreenUpdating = False
RecordCount = 1
SheetNO = 1
Line = 1
While Not rs.EOF
Application.StatusBar = RecordCount
RecordCount = RecordCount + 1
If Line <= 65536 Then
For J = 0 To rs.Fields.Count - 1
ActiveSheet.Cells(Line, J + 1) = rs.Fields(J)
Next
rs.MoveNext
Line = Line + 1
Else
Sheets.Add AFTER:=Sheets(SheetNO)
SheetNO = SheetNO + 1
Sheets(SheetNO).Select
Line = 1
End If
Wend
Application.ScreenUpdating = True
MsgBox "导入结束"
End Sub
Private Sub UserForm_Activate()
Dim DataPath As String
'数据库(*.MDB)必须在当前目录下,且文件名为:DATABASE.MDB,待导入的表名必须是:DATA,否则请修改下面的连接字符串
DataPath = ActiveWorkbook.Path & "\"
Set conn = New ADODB.Connection
conn.C + DataPath + "database.mdb;Persist Security Info=False"
conn.ConnectionTimeout = 5
conn.Open
sql = "select * from data" '表名为:DATA
Set rs = New ADODB.Recordset
rs.Open sql, conn, adOpenDynamic
End Sub
Private Sub UserForm_Terminate()
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub