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  12923 
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: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
