Import into Excel from Access table

[rollingzep]

Hi All,

I work on Excel reports.

I need to import data from a source that has 2 million rows or more.
Because of Excel row constraint, I did an import into an Access DB table.

I need to download this data to excel by splitting the data between two worksheets since Excel has row constraints.
How do I import using Excel VBA by specifying the row number (until 1,048,576 rows into WS1 and greater than that into WS2)?

Or is there any alternate way?

 

[ranman256]

'ADD VBA REFERENCE: Microsoft ActiveX Data Objects 6.x Library
( alt-F11 (vbe), tools , references)

try this. Make 2 queries:
1. (qsMyDataQuery1 ) to pull TOP 1.x million records.
2. qsMyDataQuery2, to pull the rest.

then the code will grab the 1st query, paste to sheet1
then grabs the next query, and pastes to sheet2

Public Sub CopyData()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DB
Dim vProvid
Dim i As Integer

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
 
DB = "\\server\folder1\myDatabase.mdb"
vProvid = "Microsoft.Jet.OLEDB.4.0"        ' or for Sqlsvr:  "SQLOLEDB"
 
 
With con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "Data Source=" & DB & ";Jet OLEDB"
End With
 
On Error Resume Next
For i = 1 To 2
    AddSheet i
    Set rs = con.Execute("qsMyDataQuery" & i)
    If rs.RecordCount > 0 Then
       ActiveWorkbook.Worksheets("Sheet" & i).Range("A1").CopyFromRecordset rs
    End If
Next
rs.Close
con.Close
End Sub

private Sub AddSheet(ByVal pvShtNo)
Dim vSht
 On Error GoTo MakeSht
 
vSht = "sheet" & pvShtNo
 
If Worksheets(vSht).Name = vSht Then
End If
Exit Sub

MakeSht:
 Sheets.Add
 ActiveSheet.Name = vSht
End Sub