Export large access table to excel in multiple worksheets
Martin Hanna
export to an Excel 97 spreadsheet. Excel doesn't like more than about
64,000 rows and so cant cope with a simple export/import. I know that
a field ( a week number of the year) in the table breaks down the data
into sub 64,000 rows. What I'd like is an automatic facility to read
the table and create the data in multiple worksheets according to this
particular field. I know very little about VBA but in a layman's spec:
Create SpreadSheet
Read LargeTable
On new WeekNo
Import data to worksheet :weekno
loop
I've trawled through some old google/deja suggestions but am no closer
to a possible solution. Can anyone suggest what to do?
If this cant be done then I may play with creating multiple ascii csv
files (split up according to week number) and then somehow finding a
way to import these multiple flat files into one spreadsheet, again
split up per file into worksheets.
TIA,
Martin
JIMMIE WHITAKER
has any math function you could possible dream of in your wildest dreams.
Gee, you could probably program access to launch and guide a rocket to the
moon with visual bacis for access.
"Martin Hanna" <martin...@yahoo.com> wrote in message
news:71c135d7.02012...@posting.google.com...
j
"JIMMIE WHITAKER" <kps...@worldnet.att.net> wrote in message
news:E9J58.962$zT.6...@bgtnsc06-news.ops.worldnet.att.net...
Martin Hanna
> has any math function you could possible dream of in your wildest dreams.
> Gee, you could probably program access to launch and guide a rocket to the
> moon with visual bacis for access.
The reason I'm doing this is cause I've been asked to do it for
someone in my department. They want to put the Excel spreadsheet onto
a CDROM for reference. Mine is not to reason why, mine is just to do
and die (or get sacked).
Peter Russell
In principle what you need to do is create a parameter query which selects the records you want based on a variable week/year number.
You then export this as many times as necessary.
You will almost certainly have to export to a separate workbook for each week/year.
Just because Excel says you can have 256 pages with 64k rows * 256 columns doesn't mean you can do this all at once.
It would be possible to automate what you want but if it's a once-off exercise I'm not sure it's worth it. How many separate exports do you expect?
Automation would involve a query to select each distinct value of week and year from the table, and then running a code loop to read each value found and
executing a routine which would feed the (week/year) value to the parameter query and exporting to Excel. You could/must assign a unique name to each
workbook.
Regards
Peter Russell
Shuffs
Didn't George Bernard Shaw say something like;
The reasonable man adapts himself to the world around him.
The unreasonable makes the world adapt to him.
Therefore, all progress depends upon the unreasonable man.
Dave Peterson
But if you save your file as an ASCII file (use an extension of .txt, excel's
VBA doesn't react nicely with .csv files when trying to override field
definitions), then MS has written a way to import files into multiple
worksheets.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596
It was written for xl95 (with its limit of 16384 rows).
If you decide to use it, modify the macro accordingly.
(I seem to have calculation/response time problems with files that exceed 10-20
meg. I don't know how this will affect you. You may want to play around with a
smaller subset even if it's technically possible.)
--
Dave Peterson
ec3...@msn.com
JIMMIE WHITAKER
query view, it looks like a spread sheet this way anyhow. Excel is good,
but not designed to handle such large numbers.
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3C5883C2...@msn.com...
Martin Hanna
> It would be possible to automate what you want but if it's a once-off exercise I'm not sure it's worth it. How many separate exports do you expect?
I've 6 large tables, each with over 2 million rows. Since Excel cant
cope with loading that much data (and a user insists the data is in
Excel format for a reference CDROM for our clients) then the only way
is to break it down into weeks. Access has allowed me to specifiy a
week column. With 52 weeks and 6 tables that's 312 separate exports.
With that much data exporting will take, I guess, 10 mins * 312
exports ~= 3,000 minutes. 50 hours. Then the data has to be imported
into Excel into separate worksheets. So you can see it's a long task.
The actual work isnt that difficult. It's just a matter of time and
perseverence. Obviously some automatic procedure is what I'm after.
Martin
Ronald W. Roberts
Below is some code I use to export tables to Excel. I know this isn't
what you want, but you could put a loop around this and create different
Excel files by varying the file name and in your query provide a record
number. The you can select records from your query by looping thru your
records and selecting a range on record number.
Dim cTableName As String
Dim cOutPutFileName As String
Dim nSpreadsheet As Integer
Dim cPath As String
Dim nPos As Long
Private Sub CmdOKButton_Click()
On Error GoTo Err_CmdOKButton_Click
'0 acSpreadsheetTypeExcel3 (Default)
'2 acSpreadsheetTypeLotusWK1
'3 acSpreadsheetTypeLotusWK3
'5 acSpreadsheetTypeExcel5
'5 acSpreadsheetTypeExcel7
'6 acSpreadsheetTypeExcel4
'7 acSpreadsheetTypeLotusWK4
'8 acSpreadsheetTypeExcel97
'--------------------
If IsNull(Me!ctl_FileType) Or IsEmpty(Me!ctl_FileType) Then
MsgBox " You must select a file to export.", 48
Exit Sub
End If
If IsNull(Me!ctlSpreadsheet) Or IsEmpty(Me!ctlSpreadsheet) Then
MsgBox " You must select a spreadsheet type.", 48
Exit Sub
End If
nSpreadsheet = Me!ctlSpreadsheet
If IsNull(Me!ctl_FileName) Or IsEmpty(Me!ctl_FileName) Then
MsgBox " You must enter a file name.", 48
Exit Sub
End If
Select Case Me!ctl_FileType
Case 1 'Student
Me!ctl_Status = "Process Student Data"
cTableName = "qryExcelStudent"
Case 2 'Attendance
Me!ctl_Status = "Process Attendance Data"
cTableName = "qryExcelAttendance"
Case 3 'Staff
Me!ctl_Status = "Process Staff Data"
cTableName = "qryExcelStaff"
Case 4 'Class
Me!ctl_Status = "Process Class Data"
cTableName = "qryExcelClass"
Case Else
MsgBox "Invalid file type selected", 48
Exit Sub
End Select
cPath = CurrentDBDir
nPos = 0
nPos = InStr(1, Me!ctl_FileName, "\")
If nPos = 0 Then
cOutPutFileName = cPath & Me!ctl_FileName
Else
cOutPutFileName = Me!ctl_FileName
End If
DoCmd.TransferSpreadsheet acExport, nSpreadsheet, cTableName,
Me!ctl_FileName, True
Me!ctl_Status = "Processing completed."
Exit_CmdOKButton_Click:
Exit Sub
Err_CmdOKButton_Click:
MsgBox Err.Description
Resume Exit_CmdOKButton_Click
End Sub
Hope this helps.
Ron
--
Ronald W. Roberts
Roberts Communication
r...@robcom.com
Martin Hanna
Thanks for your help. I'll try it on Monday/Tuesday. I tried the
Microsoft code that Dave Peterson suggested but I wasn't successful
with it. When I tried it, Excel grabbed 90%+ cpu of my pc for over an
hour and in the end I gave up. I think I need a dedicated pc to try
any script on.
I re-read some comments from people who've said why on earth would I
want to do this task. Basically, as a one off someone produced the
data for the guy in our department once in an excel format (with
worksheets) and since then he's decided that it's the format he wants.
Bloody users.
Martin
From what I've tested myself, it seems Access isn'
"Ronald W. Roberts" <r...@robcom.com> wrote in message