Thread: Sorting problem 2003 vs 2007

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

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

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

Will get back later

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

Sub ImportPastelXls()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Sheets("PasConv").Select
    FinalRow = Cells(Rows.count, 1).End(xlUp).Row
    Range("a1", "ag" & FinalRow).Select
    Selection.ClearContents
    Dim FileName As Variant
    Dim FInfo As String
    Dim FIndex As Integer
    Dim Title As String
    FInfo = "XL Worksheets Files (*.xls),*.xls," & "All files (*.*),*.*,"
    FIndex = 1
    Title = "Choose the file you wish to import"
    FileName = Application.GetOpenFilename(FInfo, FIndex, Title)
        If FileName = False Then
            MsgBox "Please select a valid existing data file"
            Exit Sub
        End If
    Debug.Print "Filename: " & FileName; ""
    ImportXLFilePastel fname:=CStr(FileName)
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Public Sub ImportXLFilePastel(fname As String)
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Dim FinalRowFr As Long
    Dim WriteRow As Long
    Dim FullName As String
    Dim nFileName As String
    FullName = fname
    nFileName = GetFileName(FullName)
    Application.Workbooks.Open (FullName)
    Sheets(1).Select
    FinalRowFr = Cells(Rows.count, 9).End(xlUp).Row
    FinalRowFr = FinalRowFr + 1
    ActiveWindow.WindowState = xlMaximized
    FinalRow = Cells(Rows.count, 9).End(xlUp).Row
    WriteRow = FinalRow + 1
    FinalCol = Cells(3, Columns.count).End(xlToLeft).Column
    Range("A1", "ag" & FinalRowFr).Select
    Selection.Copy
    ActiveWindow.WindowState = xlMinimized
    Sheets("PasConv").Select
   ' Range("a1", "Q" & FinalRow).Select
  '  MsgBox (FinalRow)
   ' Selection.ClearContents
    Range("A1").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Call SelectSageCols
    Call FixDateSage
    Call TaxTable
    Call SagePgNames
    Call CalcTotals
    Call TransferSagePages
    Application.ScreenUpdating = True
    Sheets("Menu").Select
    frmVarMenu.Show
    Application.Calculation = xlCalculationAutomatic
zz:
End Sub

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.

Sub TransferSagePages()
    Dim currRow As Long
    Dim CurrCol As Integer
    Dim VatCol As Integer
    Dim SrcTypeCol As Integer
    Dim TranCol As Integer
    Dim CustCol As Integer
    Dim DateCol As Integer
    Dim VatRateCol As Integer
    Dim VatAmtCol As Integer
    Dim NetAmtCol As Integer
    Dim TotIncVatCol As Integer
    Sheets("PasConv").Select
    FinalRow = Cells(Rows.count, 1).End(xlUp).Row
    FinalCol = Cells(1, Columns.count).End(xlToLeft).Column
    Range("A1:Z" & FinalRow).Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Key2:=Range("S2") _
        , Order2:=xlAscending, Key3:=Range("V2"), Order3:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
        
    'Range("A1:Y109").Select
    'Application.CutCopyMode = False
    'Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Key2:=Range("S2") _
        , Order2:=xlAscending, Key3:=Range("V2"), Order3:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
        
    Cells(1, 18) = "Tot_Incl_VAT"
    Columns("R:V").Select

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

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

Thanks,

Alan