Excel Export - only 16384 rows out of 50000

[Chach]

I've got a big problem. I've got Project 2003, and Excel 2002
installed on my machine. I've got a project that has about 90 linked
projects. I want to export all the data in all the linked projects
(lots of data, about 50,000 rows). So I expand the view to show all
task levels and run the excel pivot table export wizard. I map the
fields that I want and run the export. But everytime I run the export
I only get 16,384 rows. One of the limitations of Excel '97 and
earlier is that the maximum number of rows is 16,384, so I suspect
Project is exporting to an older version of Excel. But this is
unnacceptable to me as it's truncating a lot of my data. Can anybody
help me? Thanks in advance.

 

[John]

I've got a big problem. I've got Project 2003, and Excel 2002
installed on my machine. I've got a project that has about 90 linked
projects. I want to export all the data in all the linked projects
(lots of data, about 50,000 rows). So I expand the view to show all
task levels and run the excel pivot table export wizard. I map the
fields that I want and run the export. But everytime I run the export
I only get 16,384 rows. One of the limitations of Excel '97 and
earlier is that the maximum number of rows is 16,384, so I suspect
Project is exporting to an older version of Excel. But this is
unnacceptable to me as it's truncating a lot of my data. Can anybody
help me? Thanks in advance.

Chach,
I don't know, what is the row limitation on Excel 2003? Maybe it hasn't
changed. Nonetheless I find it very surprising that you in fact NEED to
export every Project row to Excel. Why in the world do you need all that
data in Excel? Something just doesn't sound right.

Help us out. Give us a little more insight as to your real need (i.e.
end goal) and maybe we can help with a solution that works.

John
Project MVP

 

[Chach]

My end all goal is to export the project into an access database. I
know that this is a regular export function, however due to limitations
our system as well as many other variables this is not an option. I
have a script written in Access VBA that builds a database from a pivot
table. That is why I'm looking to get all the rows out of the Project
file. The row limit in Excel 97 and up is 65536 rows (more than enough
for my needs). It would seem that Project is trying to play nice for
people who have an outdated version of Excel.

 

[Chach]

My end all goal is to export the project into an access database. I
know that this is a regular export function, however due to limitations
our system as well as many other variables this is not an option. I
have a script written in Access VBA that builds a database from a pivot
table. That is why I'm looking to get all the rows out of the Project
file. The row limit in Excel 97 and up is 65536 rows (more than enough
for my needs). It would seem that Project is trying to play nice for
people who have an outdated version of Excel.

 

[John]

My end all goal is to export the project into an access database. I
know that this is a regular export function, however due to limitations
our system as well as many other variables this is not an option. I
have a script written in Access VBA that builds a database from a pivot
table. That is why I'm looking to get all the rows out of the Project
file. The row limit in Excel 97 and up is 65536 rows (more than enough
for my needs). It would seem that Project is trying to play nice for
people who have an outdated version of Excel.

Chach,
I guess I don't understand why the more efficient approach of saving
Project as an Access database isn't an option but let's look at some
other options.

I honestly don't know if Project limits the amount of data that can go
to an Excel Pivot Table, perhaps someone else has experience in that
area. However, assuming there is a limitation, one solution might be to
perform multiple exports (divide and conquer so to speak). This isn't
necessarily an elegant or convenient approach but sometimes a brute
force method can get the job done.

You mentioned you have a VBA script so you must have some experience
with VBA. You could write VBA code to export the data from Project
directly to Access. It may seem like overkill in lieu of a direct save
option but if a direct save is not an option....

You could also work directly with the Project database and perhaps use
an SQL query. It may get around whatever limitations you have with other
methods. For more information about the Project database structure, look
for a file named projdb.htm on your hard drive.

I'm not sure if any of this will help but at least it gives a few other
choices.

John
Project MVP