Exporting huge records to Excel from Access

[sranilp]

Hi Friends,

My name is Anil,i will use the Business Objects/COGNOS which will retrives the morerecords like 3,00,000.I will create the .csv/.txt file for this and import to Access and do the pivot there.

But i want this data to be exported to Excel, as first 65000 records dump to 1st sheet,next 65000 records to 2nd sheet and so on.

How to do this,can anybody help me on this.Any VB code???

Thanks,
Anil

[Rabbit]

You can't put this in Access, you can't have that many records in an Access table.

As for importing into excel... I can hardly reccomend that either. But the gist would be:

1) Open the csv file.

2) Determine how many columns there are and fill in the cells one at a time.

3) Once you hit 65k rows, increment a sheet and continue.

[sranilp]

Hi,

Thanks for info, but i required some sort of VBA code like recordset it will count no.of rows and if it exceeds 65k then it will post to another sheet so and on.Whether is it possible.

Thanks,
Anil

You can't put this in Access, you can't have that many records in an Access table.

As for importing into excel... I can hardly reccomend that either. But the gist would be:

1) Open the csv file.

2) Determine how many columns there are and fill in the cells one at a time.

3) Once you hit 65k rows, increment a sheet and continue.

[MMcCarthy]

Hi,

Thanks for info, but i required some sort of VBA code like recordset it will count no.of rows and if it exceeds 65k then it will post to another sheet so and on.Whether is it possible.

Thanks,
Anil

Hi Anil

Have a look at this code for the importing of the csv file.

Import csv File

You will have to keep a count of the records and when you reach 65,000 then run the export to and excel spreadsheet. If you specify the sheet name in the range each time with a count variable which increments each time. Then empty the table and start on the next 65,000 records. One piece of advice is to keep the records slightly under 65,000.

[FishVal]

Hi Friends,

My name is Anil,i will use the Business Objects/COGNOS which will retrives the morerecords like 3,00,000.I will create the .csv/.txt file for this and import to Access and do the pivot there.

But i want this data to be exported to Excel, as first 65000 records dump to 1st sheet,next 65000 records to 2nd sheet and so on.

How to do this,can anybody help me on this.Any VB code???

Thanks,
Anil

Hi, Anil. Below is an example of code exporting table records to multiple sheets of Excel file. Table to be exported has name "tblExport" .

Public Sub ExportToXL()
    
    Const SheetSize = 65000     'Number of records per Excel sheet
    
    Dim appExcel As Excel.Application
    Dim wkbWorkBook As Excel.Workbook
    Dim wksWorkSheet As Excel.Worksheet
    Dim rngYCursor As Excel.Range, rngXCursor As Excel.Range
    Dim RS As New ADODB.Recordset
    Dim i As Long, lngPN As Long
    Set appExcel = CreateObject("Excel.Application")
    
    With appExcel
        .Visible = True
        .UserControl = True
        Set wkbWorkBook = .Workbooks.Add
    End With
    
    With wkbWorkBook.Worksheets
        While .Count > 1
            .Item(1).Delete
        Wend
        Set wksWorkSheet = .Item(1)
    End With
    
    With wksWorkSheet
        lngPN = 1
        .Name = "Page " & lngPN
        Set rngYCursor = .Range("A1")
    End With
    
    With RS
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open "tblExport"
        
        While True
            For i = 1 To SheetSize
                Set rngXCursor = rngYCursor
                If .EOF Then GoTo ExitSub
                For Each fld In .Fields
                    rngXCursor.Value = fld.Value
                    Set rngXCursor = rngXCursor.Offset(ColumnOffset:=1)
                Next
                Set rngYCursor = rngYCursor.Offset(RowOffset:=1)
                .MoveNext
            Next i
            Set wksWorkSheet = wkbWorkBook.Worksheets.Add(After:=wksWorkSheet)
            With wksWorkSheet
                lngPN = lngPN + 1
                .Name = "Page " & lngPN
                Set rngYCursor = .Range("A1")
            End With
        Wend
        
    End With
    
ExitSub:
    RS.Close
    Set rngXCursor = Nothing
    Set rngYCursor = Nothing
    Set RS = Nothing
    Set wksWorkSheet = Nothing
    Set wkbWorkBook = Nothing
    Set appExcel = Nothing

End Sub

This code is rather slow. I suppose it will take several hours to (maybe) several days to export 3000000 records.

With a little trick it can be boosted much. To the table to be exported (the name is still "tblExport" ) add Autonumber field (the code below assumes it has a name "keyAN"). Edit SQL expression in the code (line#39) to select fields you need to be exported.

Public Sub ExportToXL1()
    
    Const SheetSize = 65000
    
    Dim appExcel As Excel.Application
    Dim wkbWorkBook As Excel.Workbook
    Dim wksWorkSheet As Excel.Worksheet
    Dim RS As New ADODB.Recordset
    Dim lngPN As Long, lngRecordsCopied As Long
    Dim strSQL As String
    
    Set appExcel = CreateObject("Excel.Application")
    
    With appExcel
        .Visible = True
        .UserControl = True
        Set wkbWorkBook = .Workbooks.Add
    End With
    
    With wkbWorkBook.Worksheets
        While .Count > 1
            .Item(1).Delete
        Wend
        Set wksWorkSheet = .Item(1)
    End With
    
    With wksWorkSheet
        lngPN = 0
        .Name = "Page " & lngPN + 1
        Set rngYCursor = .Range("A1")
    End With
    
    With RS
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        
        While True
            strSQL = "SELECT txtField1, lngField2 FROM tblExport WHERE " & _
                "keyAN >= " & lngPN * SheetSize + 1 & _
                " AND keyAN <= " & lngPN * SheetSize + SheetSize & ";"
            .Open strSQL
            lngRecordsCopied = wksWorkSheet.Range("A1").CopyFromRecordset(RS)
            .Close
            If lngRecordsCopied < SheetSize Then GoTo ExitSub
            Set wksWorkSheet = wkbWorkBook.Worksheets.Add(After:=wksWorkSheet)
            With wksWorkSheet
                lngPN = lngPN + 1
                .Name = "Page " & lngPN + 1
            End With
        Wend
        
    End With
    
ExitSub:
    Set RS = Nothing
    Set wksWorkSheet = Nothing
    Set wkbWorkBook = Nothing
    Set appExcel = Nothing

End Sub

[sranilp]

Hi ,

Thanx a lot for the code i will it check it out.

Anil

Hi Anil

Have a look at this code for the importing of the csv file.

Import csv File

You will have to keep a count of the records and when you reach 65,000 then run the export to and excel spreadsheet. If you specify the sheet name in the range each time with a count variable which increments each time. Then empty the table and start on the next 65,000 records. One piece of advice is to keep the records slightly under 65,000.

[sranilp]

Hi,

Thanx a lot for the code, i will check it out.

Anil

Hi, Anil. Below is an example of code exporting table records to multiple sheets of Excel file. Table to be exported has name "tblExport" .