Hello.

I am using Access 97 and am saving the files as Excel 97, however Access is only importing 16838 rows of my files.

I tried changing the cell formats to "text" and then re-saving the excel file, it works sometimes but then not others.

Is there something I am missing? I can't understand why the fix works one minute and then not the next, unless I am doing something I don't realize I am doing....

Hello.

I am using Access 97 and am saving the files as Excel 97, however Access is only importing 16838 rows of my files.


Acutally it's 16383

That doesn't sound like Excel 97 to me. 16384 rows is a limitation of Excel version 5 (95). Double check your file type/version.

Excel 97 has a maximum number of rows of 65536.

That doesn't sound like Excel 97 to me. 16384 rows is a limitation of Excel version 5 (95). Double check your file type/version.

Excel 97 has a maximum number of rows of 65536.

That's the problem, I just really don't get it! and it's driving me crazy. I made sure it is saved in 97 format and it's still not functioning properly

Define "exporting" used in your OP.

I can import the files as just a .xls file (not 3.0, 4.0 etc) and the complete row will upload. However, the problem is fields will import into other fields and randomly be in another field... Example Work order will be in the Patient name field. I am baffled by the way that these fields jump around... is there a better way to import these files as tables so they do not move?

csv does not work as there are fields with commas so it imports them incorrectly.

Thank you for your help!

Oh, I thought the issue was the number of rows, not the columns moving around.

I always set up a table just for imports that is based on the expected input (let the import wizard set it up with a PK). I tweak the table to take care of anomolies (date fields that are supposed to be text fields, number fields that are supposed to be text fields, etc.), delete everything from it, and then write a VBA module that does the import via TransferSpreadsheet(). After all the data "imports" the way I want it to, I write a series of update/insert queries to pull data from my import table and put it into the various normalized tables. Then create a delete query to "empty" your import table.

After you're satisfied everything is working right, just call all your queries in the right order from a VBA subroutine.

I really am surprised you're no longer having problems with the number of rows, though. Is this something that fixed itself? How come no feed back?

Oh, I thought the issue was the number of rows, not the columns moving around.

I really am surprised you're no longer having problems with the number of rows, though. Is this something that fixed itself? How come no feed back?

The rows I fixed by not using the Excel 97 file type that the Macro is looking for, I was trying to just import the tables myself and then create an append query to append the two tables into the one table that the Macro is looking for instead of having the "transfer spreadsheet" portion of the macro run due to the fact that it was causing many more errors and I can have more control if I import the tables myself. (the highest excel spreadsheet type for the transfer spreadsheet option is 5.0 or Excel which cause the cutting off of the row issue and causing the columns to move around as well)

I am really new to databases, and don't have a lot of visual basic experience, and surprisingly the person who built the database didn't use VB at all. So I am trial and error at this point, but it has me scratching my head as to why it will work sometimes and then not work at all 2 minutes later. I just want to figure out the easiest way to fix this issue so I can know exactly what I have to do to build the enhancements that are needed next. Yay! :eek:

I wouldn't mind taking a look if you wanted to post sample data and empty tables.