ACCESS TO EXCEL EXPORTATION coming through in a random order (Office 10)

[R_McCallan]

I have exported some data from Access to Excel (2010) and once exported to access the data is in a different order from when it was in the query. The data is all under the correct headers but each column is randomly ordered.

The query is in the following order (by fieldnames) CompanyName, CompanyAddress, TownCity, Postcode, ContactNumber1, WebsiteAddress, VATNumber, CompanyContact and CompanyType. The export comes out in this order (by header names) CompanyAddress, CompanyContact, CompanyName, CompanyType, ContactNumber1, Postcode, TownCity, VATNumber, WebsiteAddress.

There is an intermediary table which may be affecting the order, tbl_ExportDetails gives the fieldnames new header names for the export (I have used the original field names above so as not to confuse). Within the export details table, the fields are in the same order as in the original query so I don't see why they have been randomised!

Any help is much appreciated

Thanks
Rebecca.

 

[Fazza]

Hi, Rebecca

If it is like Excel 2003 & earlier, just rearrange the columns to the order you want in the destination worksheet. They should stay in that order and all be OK.

HTH, F

 

[R_McCallan]

Hi,

Thanks for the reply

Yes that will work but I was wondering if there was a way to automatically put them back in the correct order? Otherwise when a customer is using the system they will have to reorder the fields each time.

Rebecca

 

[Fazza]

OK, Rebecca.

I was wrong before; my answer must seem rather stupid, actually. I was on the 'wrong track' thinking you had a query in Excel importing the data.

I now appreciate that you have an Access export. Excel is almost incidental. Maybe if you don't get a good answer in this forum [Excel], ask again in the Access sub-forum?

I am not familiar with exporting from Access. So I can't answer your question. I'm sure there must be many ways to export data from Access so it might be best to describe the approach taken. For example, I can imagine if you've written a query the intermediate step is unnecessary if it just renames headers as this can be done in SQL. And this should also re-set the field order to whatever you define in the SQL. As in,

SELECT field_X AS [New name], field_M AS [its new name], field_A
FROM table

Or even better, "SELECT * INTO etc". Please refer to http://support.microsoft.com/kb/295646 and
http://www.xtremevbtalk.com/showthread.php?t=217783

I'm guessing though that you're not using ADO, and hopefully someone familiar with Access exports will give you the answer.

regards, F

 

[R_McCallan]

Ok thanks for trying! No I am not using ADO I'm using DAO. I thought I'd post it in here because I thought it may be more the Excel side which was causing the problem.

Thanks anyway

Rebecca

 

[Fazza]

OK, Rebecca.

Normally exports are in the same order. The fact that this one isn't must be due to the steps taken. The steps need to be explained. Please describe the steps taken, the DAO, the extra table, the SQL, the code, etc.

It is a surprise that the fields are in a random order. (So, a different order on successive exports, I assume.)

Are any fields not renamed? Do they always appear first or also randomly.

Maybe if you post sample data too, it will help. See if others get the same randomness, or maybe someone can spot a pattern to it.

As I don't use Excel or Access after 2003 I will not be able to assist further.

regards, F