473,287 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Exporting huge records to Excel from Access

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
6 12902
Rabbit
12,516 Expert Mod 8TB
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
sranilp
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
14,534 Expert Mod 8TB
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
2,653 Expert 2GB
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
sranilp
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
sranilp
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

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

Similar topics

3
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works...
5
by: jsudo | last post by:
I have a Access database with a large amount of records (close to 500,000) that I would like to export to Excel. I found out that Excel has the capability of of about 65,000 rows so I know I...
4
by: D | last post by:
I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear"...
2
by: Kenneth | last post by:
How do I remove the limitation in Access that deny me from exporting 24000 rows and 17 columns (in a query) into Excel? Kenneth
2
by: amitshinde02 | last post by:
Hi Experts, I have been struggling on this problem since last one month. I have a huge Excel sheet (Not well formatted) with around 10000 records or more. with around 60 fields. I want to...
21
by: bobh | last post by:
Hi All, In Access97 I have a table that's greater than 65k records and I'm looking for a VBA way to export the records to Excel. Anyone have vba code to export from access to excel and have the...
4
by: deejayquai | last post by:
Hi First of all my level = basic! My question= I have a report containing student performance data with a sub-report showing the subjects they have taken that year. I run the report group by...
3
by: JHNielson | last post by:
I am having quite the unique problem trying to Export to Excel and I need ot find a solution within 3 hours. -- PLEASE HELP! The system exports a file from the application that the users can make...
2
by: atlbearcat | last post by:
Here's one that's been bugging me for about a week now... I have a form that allows users to filter records, simple enough. But I want to give them the option to export the filtered records to...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.