By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Exporting huge records to Excel from Access

P: 10
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
Aug 3 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,396
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.
Aug 3 '07 #2

P: 10
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.
Aug 3 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Aug 3 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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".
Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportToXL()
  2.  
  3.     Const SheetSize = 65000     'Number of records per Excel sheet
  4.  
  5.     Dim appExcel As Excel.Application
  6.     Dim wkbWorkBook As Excel.Workbook
  7.     Dim wksWorkSheet As Excel.Worksheet
  8.     Dim rngYCursor As Excel.Range, rngXCursor As Excel.Range
  9.     Dim RS As New ADODB.Recordset
  10.     Dim i As Long, lngPN As Long
  11.     Set appExcel = CreateObject("Excel.Application")
  12.  
  13.     With appExcel
  14.         .Visible = True
  15.         .UserControl = True
  16.         Set wkbWorkBook = .Workbooks.Add
  17.     End With
  18.  
  19.     With wkbWorkBook.Worksheets
  20.         While .Count > 1
  21.             .Item(1).Delete
  22.         Wend
  23.         Set wksWorkSheet = .Item(1)
  24.     End With
  25.  
  26.     With wksWorkSheet
  27.         lngPN = 1
  28.         .Name = "Page " & lngPN
  29.         Set rngYCursor = .Range("A1")
  30.     End With
  31.  
  32.     With RS
  33.         .ActiveConnection = CurrentProject.Connection
  34.         .CursorType = adOpenForwardOnly
  35.         .LockType = adLockReadOnly
  36.         .Open "tblExport"
  37.  
  38.         While True
  39.             For i = 1 To SheetSize
  40.                 Set rngXCursor = rngYCursor
  41.                 If .EOF Then GoTo ExitSub
  42.                 For Each fld In .Fields
  43.                     rngXCursor.Value = fld.Value
  44.                     Set rngXCursor = rngXCursor.Offset(ColumnOffset:=1)
  45.                 Next
  46.                 Set rngYCursor = rngYCursor.Offset(RowOffset:=1)
  47.                 .MoveNext
  48.             Next i
  49.             Set wksWorkSheet = wkbWorkBook.Worksheets.Add(After:=wksWorkSheet)
  50.             With wksWorkSheet
  51.                 lngPN = lngPN + 1
  52.                 .Name = "Page " & lngPN
  53.                 Set rngYCursor = .Range("A1")
  54.             End With
  55.         Wend
  56.  
  57.     End With
  58.  
  59. ExitSub:
  60.     RS.Close
  61.     Set rngXCursor = Nothing
  62.     Set rngYCursor = Nothing
  63.     Set RS = Nothing
  64.     Set wksWorkSheet = Nothing
  65.     Set wkbWorkBook = Nothing
  66.     Set appExcel = Nothing
  67.  
  68. End Sub
  69.  
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportToXL1()
  2.  
  3.     Const SheetSize = 65000
  4.  
  5.     Dim appExcel As Excel.Application
  6.     Dim wkbWorkBook As Excel.Workbook
  7.     Dim wksWorkSheet As Excel.Worksheet
  8.     Dim RS As New ADODB.Recordset
  9.     Dim lngPN As Long, lngRecordsCopied As Long
  10.     Dim strSQL As String
  11.  
  12.     Set appExcel = CreateObject("Excel.Application")
  13.  
  14.     With appExcel
  15.         .Visible = True
  16.         .UserControl = True
  17.         Set wkbWorkBook = .Workbooks.Add
  18.     End With
  19.  
  20.     With wkbWorkBook.Worksheets
  21.         While .Count > 1
  22.             .Item(1).Delete
  23.         Wend
  24.         Set wksWorkSheet = .Item(1)
  25.     End With
  26.  
  27.     With wksWorkSheet
  28.         lngPN = 0
  29.         .Name = "Page " & lngPN + 1
  30.         Set rngYCursor = .Range("A1")
  31.     End With
  32.  
  33.     With RS
  34.         .ActiveConnection = CurrentProject.Connection
  35.         .CursorType = adOpenForwardOnly
  36.         .LockType = adLockReadOnly
  37.  
  38.         While True
  39.             strSQL = "SELECT txtField1, lngField2 FROM tblExport WHERE " & _
  40.                 "keyAN >= " & lngPN * SheetSize + 1 & _
  41.                 " AND keyAN <= " & lngPN * SheetSize + SheetSize & ";"
  42.             .Open strSQL
  43.             lngRecordsCopied = wksWorkSheet.Range("A1").CopyFromRecordset(RS)
  44.             .Close
  45.             If lngRecordsCopied < SheetSize Then GoTo ExitSub
  46.             Set wksWorkSheet = wkbWorkBook.Worksheets.Add(After:=wksWorkSheet)
  47.             With wksWorkSheet
  48.                 lngPN = lngPN + 1
  49.                 .Name = "Page " & lngPN + 1
  50.             End With
  51.         Wend
  52.  
  53.     End With
  54.  
  55. ExitSub:
  56.     Set RS = Nothing
  57.     Set wksWorkSheet = Nothing
  58.     Set wkbWorkBook = Nothing
  59.     Set appExcel = Nothing
  60.  
  61. End Sub
  62.  
Aug 4 '07 #5

P: 10
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.
Aug 7 '07 #6

P: 10
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".
Aug 7 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.