How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

Kevin wrote:
How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

In Access go to File Get external Date Import

Only if you have Excel 2007.

Where is this data how are trying to import it?


gls858

Buy the new 2007 version of Excel to get a million rows.


Gord Dibben MS Excel MVP

On Fri, 2 Feb 2007 14:16:01 -0800, Kevin
wrote:

How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

If, by chance, he actually has XL2007 - isn't there some sort of feature that
will restrict it to 65K rows (for backward compatibility maybe) that has to
be turned off???

"Gord Dibben" wrote:

Buy the new 2007 version of Excel to get a million rows.


Gord Dibben MS Excel MVP

On Fri, 2 Feb 2007 14:16:01 -0800, Kevin
wrote:

How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

If a workbook was created in an earlier version only 65536 rows will be shown
and available.

From a post by Bob Umlas

If you're opening a file last saved in 2003, it will open in "compatibility
mode" which displays the same limits as xl2003. You need to convert it to
the current format. Use Office Button/Convert. Note that this will delete
the original. You'll then have all the cells.
Bob Umlas
Excel MVP


Gord

On Fri, 2 Feb 2007 15:21:00 -0800, JMB wrote:

If, by chance, he actually has XL2007 - isn't there some sort of feature that
will restrict it to 65K rows (for backward compatibility maybe) that has to
be turned off???

"Gord Dibben" wrote:

Buy the new 2007 version of Excel to get a million rows.


Gord Dibben MS Excel MVP

On Fri, 2 Feb 2007 14:16:01 -0800, Kevin
wrote:

How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

Thanks Gord.

"Gord Dibben" wrote:

If a workbook was created in an earlier version only 65536 rows will be shown
and available.

From a post by Bob Umlas

If you're opening a file last saved in 2003, it will open in "compatibility
mode" which displays the same limits as xl2003. You need to convert it to
the current format. Use Office Button/Convert. Note that this will delete
the original. You'll then have all the cells.
Bob Umlas
Excel MVP


Gord

On Fri, 2 Feb 2007 15:21:00 -0800, JMB wrote:

If, by chance, he actually has XL2007 - isn't there some sort of feature that
will restrict it to 65K rows (for backward compatibility maybe) that has to
be turned off???

"Gord Dibben" wrote:

Buy the new 2007 version of Excel to get a million rows.


Gord Dibben MS Excel MVP

On Fri, 2 Feb 2007 14:16:01 -0800, Kevin
wrote:

How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

See the above responses - especially the first one from Gord Dibben. The
'simple' answer is, as he said, get Excel 2007.

Barring that, the only practical way to do it is to use VBA code to open up
the Access database and start reading in records, one by one, and keeping a
counter of the records imported. When that gets to the limit for your
version of Excel (varies from 16K to 64K over the years) then you have the
code insert a new page and switch to that page to continue importing the
data: repeat as required until all records are read. Note: to get the same
number of rows in Excel 2003 that you can have in Excel 2007, you'll need a
total of 16 sheets.

But getting the new version of Excel may be the quick, easy answer. I just
finished giving some assistance to someone from another web site who had need
of the same thing: to be able to read in over 64K rows of data from either
..txt or .csv files and first thing I told him was to get Excel 2007. He did,
and this evening I coded up a process so that he can read the output from his
test equipment - sample file he sent to me had over 185,000 rows of entries.
Works like a champ - I even tested with one file that we created that
actually filled all 1,048,576 rows and it held up under the strain.

"Kevin" wrote:

How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

Thanks Gord. I do have Excel 2007 - I made the Company upgrade my laptop to
Excel 2007 specifically for the 1 million row feature. I got a little
stressed out Friday afternoon when I opened an existing file and could not
expand the file beyond the old 63k + limitation.

I have not yet found the "Office Button / Convert" feature - but I copied
the old worksheet into a new Excel 2007 file, and then expanded it - and it
works!

I am now officially divorcing Access and re-marrying my old spouse - EXCEL!

"JLatham" wrote:

See the above responses - especially the first one from Gord Dibben. The
'simple' answer is, as he said, get Excel 2007.

Barring that, the only practical way to do it is to use VBA code to open up
the Access database and start reading in records, one by one, and keeping a
counter of the records imported. When that gets to the limit for your
version of Excel (varies from 16K to 64K over the years) then you have the
code insert a new page and switch to that page to continue importing the
data: repeat as required until all records are read. Note: to get the same
number of rows in Excel 2003 that you can have in Excel 2007, you'll need a
total of 16 sheets.

But getting the new version of Excel may be the quick, easy answer. I just
finished giving some assistance to someone from another web site who had need
of the same thing: to be able to read in over 64K rows of data from either
.txt or .csv files and first thing I told him was to get Excel 2007. He did,
and this evening I coded up a process so that he can read the output from his
test equipment - sample file he sent to me had over 185,000 rows of entries.
Works like a champ - I even tested with one file that we created that
actually filled all 1,048,576 rows and it held up under the strain.

"Kevin" wrote:

How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

<<<"I am now officially divorcing Access and re-marrying my old spouse -
EXCEL!"

WATCH OUT ! ! !

Aaron will get you!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kevin" wrote in message
...
Thanks Gord. I do have Excel 2007 - I made the Company upgrade my laptop
to
Excel 2007 specifically for the 1 million row feature. I got a little
stressed out Friday afternoon when I opened an existing file and could not
expand the file beyond the old 63k + limitation.

I have not yet found the "Office Button / Convert" feature - but I copied
the old worksheet into a new Excel 2007 file, and then expanded it - and
it
works!

I am now officially divorcing Access and re-marrying my old spouse -
EXCEL!

"JLatham" wrote:

See the above responses - especially the first one from Gord Dibben.
The
'simple' answer is, as he said, get Excel 2007.

Barring that, the only practical way to do it is to use VBA code to open
up
the Access database and start reading in records, one by one, and
keeping a
counter of the records imported. When that gets to the limit for your
version of Excel (varies from 16K to 64K over the years) then you have
the
code insert a new page and switch to that page to continue importing the
data: repeat as required until all records are read. Note: to get the
same
number of rows in Excel 2003 that you can have in Excel 2007, you'll
need a
total of 16 sheets.

But getting the new version of Excel may be the quick, easy answer. I
just
finished giving some assistance to someone from another web site who had
need
of the same thing: to be able to read in over 64K rows of data from
either
.txt or .csv files and first thing I told him was to get Excel 2007. He
did,
and this evening I coded up a process so that he can read the output
from his
test equipment - sample file he sent to me had over 185,000 rows of
entries.
Works like a champ - I even tested with one file that we created that
actually filled all 1,048,576 rows and it held up under the strain.

"Kevin" wrote:

How can I import Access data that exceeds 65 rows? I understand that
Excel
can now handle 1 million rows - but I cannot figure out how.

Good luck with the settlement!

Some things Excel does better than Access, some things Access excels at.
Often the combination of them working together is an awesome tool.

If you haven't seen it yet, Microsoft has an Excel workbook (2MB) that maps
out the Excel 2003 commands to their new locations in Excel 2007. You can
get a copy of it from the link near the bottom of this page:
http://office.microsoft.com/search/r...1 00860481033

"Kevin" wrote:

Thanks Gord. I do have Excel 2007 - I made the Company upgrade my laptop to
Excel 2007 specifically for the 1 million row feature. I got a little
stressed out Friday afternoon when I opened an existing file and could not
expand the file beyond the old 63k + limitation.

I have not yet found the "Office Button / Convert" feature - but I copied
the old worksheet into a new Excel 2007 file, and then expanded it - and it
works!

I am now officially divorcing Access and re-marrying my old spouse - EXCEL!

"JLatham" wrote:

See the above responses - especially the first one from Gord Dibben. The
'simple' answer is, as he said, get Excel 2007.

Barring that, the only practical way to do it is to use VBA code to open up
the Access database and start reading in records, one by one, and keeping a
counter of the records imported. When that gets to the limit for your
version of Excel (varies from 16K to 64K over the years) then you have the
code insert a new page and switch to that page to continue importing the
data: repeat as required until all records are read. Note: to get the same
number of rows in Excel 2003 that you can have in Excel 2007, you'll need a
total of 16 sheets.

But getting the new version of Excel may be the quick, easy answer. I just
finished giving some assistance to someone from another web site who had need
of the same thing: to be able to read in over 64K rows of data from either
.txt or .csv files and first thing I told him was to get Excel 2007. He did,
and this evening I coded up a process so that he can read the output from his
test equipment - sample file he sent to me had over 185,000 rows of entries.
Works like a champ - I even tested with one file that we created that
actually filled all 1,048,576 rows and it held up under the strain.

"Kevin" wrote:

How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

Thanks for providing that reference.
I just cleaned it up and its came out at 600 kb
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"JLatham"
<HelpFrom @ Jlathamsite.com.(removethis)
wrote in message
Good luck with the settlement!

Some things Excel does better than Access, some things Access excels at.
Often the combination of them working together is an awesome tool.

If you haven't seen it yet, Microsoft has an Excel workbook (2MB) that maps
out the Excel 2003 commands to their new locations in Excel 2007. You can
get a copy of it from the link near the bottom of this page:
http://office.microsoft.com/search/r...1 00860481033

I found another link to it later. In Excel 2007 Help, under "What's New" the
second topic page also provides a link to it. I found it originally while
reading some of the 'Crabby Office Lady' articles which led me to it.

"Jim Cone" wrote:

Thanks for providing that reference.
I just cleaned it up and its came out at 600 kb
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"JLatham"
<HelpFrom @ Jlathamsite.com.(removethis)
wrote in message
Good luck with the settlement!

Some things Excel does better than Access, some things Access excels at.
Often the combination of them working together is an awesome tool.

If you haven't seen it yet, Microsoft has an Excel workbook (2MB) that maps
out the Excel 2003 commands to their new locations in Excel 2007. You can
get a copy of it from the link near the bottom of this page:
http://office.microsoft.com/search/r...1 00860481033

On Feb 3, 9:16 am, Kevin wrote:
How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.

Hi Kevin,

Why don't you use the PivotTable function...

What it does is import all of the data into the memory of Excel and
caches it (very small file size/footprint), and then you can build the
table/report however you want...

As you use Data Import External Data New Database Query, you will
get to a point in the Wizard to use a PivotTable...

To be honest it's a little tricky for first time users, but are very
very powerful once you get the hang of it