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
6 12902
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.
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.
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.
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
-
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.
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".
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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"...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
| |