TransferSpreadSheet problem import excel to access

[Quique]

Hello,
I've got a problem importing a worksheet Excel into a temporary table in
access. All the information is imported but the table is not ordered as it
is originally in excel.

I'm using a VBA code rather simple as the following:

Private Sub Import()
...
Dim TableName As String

DoCmd.TransferSpreadsheet , , TableName, XLFileName, , test!A10:AX"
....
End Sub

where XLFileName is my excel file and "test" my worksheet within that file.
This code imported perfectly all the records from the excel file but since
the worksheet increased number of lines up to 1800 (which I do not consider
is a big number!) all of them are imported but after the line 35 (not always
the same) jumps to te line 1400 or so, then comes back to the line 36 until
80, then again another jump...
Despite of having all data the different order does not help afterwards.

Any help out there?
Thanks beforehand
Qq

[Smartin]

Quique wrote:

Hello,
I've got a problem importing a worksheet Excel into a temporary table in
access. All the information is imported but the table is not ordered as it
is originally in excel.

I'm using a VBA code rather simple as the following:

Private Sub Import()
..
Dim TableName As String

DoCmd.TransferSpreadsheet , , TableName, XLFileName, , test!A10:AX"
...
End Sub

where XLFileName is my excel file and "test" my worksheet within that file.
This code imported perfectly all the records from the excel file but since
the worksheet increased number of lines up to 1800 (which I do not consider
is a big number!) all of them are imported but after the line 35 (not always
the same) jumps to te line 1400 or so, then comes back to the line 36 until
80, then again another jump...
Despite of having all data the different order does not help afterwards.

Any help out there?
Thanks beforehand
Qq

Hi Qq,

Tables do not have an inherent order. Order is obtained through queries,
assuming there is a field that facilitates order.

If you are looking at your table and asking, "why can I not find order
in this" it's because you should not be looking at the table at all.
Look at a query, or better yet, a form that takes its data and order
from a query. Then you can order the imported records in any manner
available.

Hoping this helps,
--
Smartin

[Ron2006]

The other consideration is that if you look at your imported table and
change the sequence as you are looking at it, access will remember
that sequence and always put it that way when you come back. It seems
that access creates and establishes a query by which you are looking
at the table and forever remembers that query.

I have spreadsheets that I import that I HAVE to maintain the import
sequece. I established an autonumber on the imported to table, and
whenever I want to look at it in exactly the sequence that it came in
as I sort it by that autonumber. Once I did that I have not had any
problems with the sequence.

In this application the information I am looking for is in groups of
successive lines and the fields are in different columns depending on
an identifier which is different and in different colums. And there
can be multiple and variable sets of sub information for each major
set. This mandates reading the table in exactly the sequence that it
was imported as. Hence the autonumber field, and I use vba code to
read a query of that table that is sorted in autonumber sequence.

Hope this gives you some ideas.

Ron