Sorting problem 2003 vs 2007

[AlanAnderson]

Hi All,

The code below works fine on a machine running Excel 2003 but when transferred to a machine running Vista with Excel 2007 I get a “Run time Error 1004”

Is there a way to have the same code run on both versions?

PS I have deliberately done all my development in 2003 believing that it would all work on 2007 but that it would not work the other way around. Am I correct?

Regards,

Alan

Please Login or Register  to view this content.

[mojo249]

Your code works fine for me in 2007. My guess is it's something outside of the code you've provided which is causing the problem.

Can you upload the workbook?

Dion

[AlanAnderson]

Hi Dion,

Thanks for the help.

I think you are right. It is probably not feasible to upload the entire workbook as it is really big.

What I have noticed is that 2007 is creating a fancy table with headers containing sorting facilities etc. This does not occur in 2003. Seeing as the error message refers to a sort problem is this not the issue?

What can I put in the code to either "switch off" this facility in 2007 or to make the 2003 sort "acceptable" to the new type of table?

Thanks again,

Alan

[romperstomper]

What exactly is this range you are sorting? An external query?
Have you tried including row 1 in the sort and specifying that you have headers?

[royUK]

Try using the macro Recorder whilst sorting, then post the code for help modifying it

[romperstomper]

FWIW, I just tested your code on a simple table in 2007 and it worked fine, so maybe a sample of the actual failing workbook would be useful.

[AlanAnderson]

Hi,
I am in the process of extracting the offending modules and sheets from the programme.

Will get back later

[AlanAnderson]

Hi Again,
I am enclosing the module that contains the "offending" code.
I am also enclosing 4 xls files.
Whilst being a total newbiw I am convinced the problem lies in these. . Where the import file and export files are both handled in Excel 2003 the info resides in a straight forward data format..
When these files are taken to a 2007 machine a table is created with facilities for sorting etc which does not happen in 2003.
btw the format of some of the columns also changes (num,bers to dates etc) but this may just be a bug I have not looked into yet.

What really bugs me is that this code works perfectly in 2003 - its just when copied to 2007 it falls over.

Please help - I really am lost.

Regards,

Alan.

PS the entire programme is very large.and cant be uploaded.

This code opens the file and imports it into my data file to a page called PasConv

Please Login or Register  to view this content.

After the above code is all run then following routine sorts and then puts data ito various pages. The web page will not allow the entire routine as its too large so I have cut it off just after the problem occurs.

Please Login or Register  to view this content.

[AlanAnderson]

Sorry,
Having real problems uploading files.
If I could just describe the data files.
They are simple - just 10columns containining data. 3 columns containing data obtained by simple arithmetic calculations based on other columns.

All columns have headers.
WWhere the files are in 2007 those headers get changed to "smart headers" - ie you can click on headers to sort data. This is not the case in 2003.

When I try to use VBA generated in 2003 to sort these "converted files" it falls over.

Regards,

Alan

[AlanAnderson]

Hi,
Problem solved. I had to write routine that did different sort depending on version.

Thanks,

Alan