AW: Datenimport aus Access
20.09.2006 14:24:48
Coach
Hallo Peter,
folgender Code verteilt Deine Daten auf Tabellen mit Namen Part1...N
Option Explicit
Sub ImportFromACCESS()
Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Master As Workbook
Dim i As Long, j As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
'Alle alten Importtabellen löschen
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Part*" Then ws.Delete
Next
Application.DisplayAlerts = True
Set Cn = New ADODB.Connection
Set Rs = New ADODB.Recordset
Set Master = ThisWorkbook
Cn.Open "Provider =Microsoft.Jet.OLEDB.4.0; Data Source =C:\Test.mdb;"
Rs.Open "Select * From tbl_Test2 Order By ID", Cn, CursorType:=adOpenKeyset, LockType:=adLockOptimistic
Rs.MoveLast
For i = 1 To (Int(Rs.RecordCount / 65000) + 1)
Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
ws.Name = "Part" & i
With ws
For j = 0 To Rs.Fields.Count - 1
.Cells(1, j + 1).Value = Rs.Fields(j).Name
Next
Rs.AbsolutePosition = (65000 * (i - 1)) + 1
.Cells(2, 1).CopyFromRecordset Rs
.Rows("65002:" & ws.Rows.Count).ClearContents
End With
Next
Application.ScreenUpdating = True
Rs.Close
Cn.Close
Set Cn = Nothing
Set Rs = Nothing
Application.ScreenUpdating = True
End Sub
Bitte MDB und Tabelle und Sortierfeld entsprechend anpassen.
Gruß Coach