Exporting from Access to Excel Workbook

  • Thread starter Thread starter Marko Laine
  • Start date Start date
M

Marko Laine

I can export data from Access tables to multiple sheet with
TransferSpreadsheet and it works ok if there is under 65500 rows in table.
But the question is how I can split data after that 65500 rows to continue
in next sheet. TransferSpreadsheet only drops data that is over the limit.

I have now Access tables / Excel sheets a, b, c, d, e and that's fine yet,
but when rows are over 65500 / sheet I want it for example a1, a2, b, c, d1,
d2, d3, e. Do I have to split data to the temp tables and then make tranfer
or is there easier way to manage this Excel limit?

Thanks,

-- Marko --
 
TransferSpreadsheet has its limitations. This is one of them (obviously).
You don't say if you are using code or a macro, but I would create queries
that split the data in to smaller chunks and export them to the workbook.
If you name the queries d1, d2, d3 and so on, the sheet will be named that
as well.
 
Hi Marko,

Just a comment. I'm curious as to why you're exporting such a large (i.e.
large by Excel measure) amount of data to a workbook. Opening an Excel
workbook with 60000+ records loads the whole file into memory at once. I
can't imagine doing any kind of analysis on such files. If I may ask, what
are you doing with the Excel workbook once all the data is in?

Seems to me that there is a reason for the 65k limit in an Excel workbook,
and that is if you even get close to half of that number you really need a
database application such as Access. Is there something that Excel can do
and Access can't which forces you to do this?

Just curious!

Immanuel Sibero
 
Immanuel Sibero said:
Hi Marko,

Just a comment. I'm curious as to why you're exporting such a large (i.e.
large by Excel measure) amount of data to a workbook. Opening an Excel
workbook with 60000+ records loads the whole file into memory at once. I
can't imagine doing any kind of analysis on such files. If I may ask, what
are you doing with the Excel workbook once all the data is in?
Click to expand...

We have to deliver those Excel workbook to third part users and they have
used to use Excel, not Access.
If this would be my decision, I wanted to make it with MS SQL / Oracle with
web UI. But sometimes you have to do what client wants. ;)


-- Marko --
 
Roger Carlson said:
TransferSpreadsheet has its limitations. This is one of them (obviously).
You don't say if you are using code or a macro, but I would create queries
that split the data in to smaller chunks and export them to the workbook.
If you name the queries d1, d2, d3 and so on, the sheet will be named that
as well.
Click to expand...

I'm using VBA code. I have to make it that way then.
I can rename those sheets like I want, the issue was only that splitting
with TransferSpreadsheet method.
Thanks for the answer.

-- Marko --
 
Back
Top