Lost in translation (1 Viewer)

[Ksan]

Apologies in advance if this is the wrong board to post this under (it was 50/50) and I'm leaning toward the problem being with the macro end of the problem...

I have a Query set up which select records based on an inital criteria being met, then sorts the 8 fields of the records selected into ascending order (based on 4 of the fields).

The Query works fine, the records are ordered by the 1st field, then within that order by the 2nd field value, then within… etc. Just what I need and how I need it, no problems there.

This Query is then part of a Macro, which when invoked saves the results of the Query to an Excel 97-2003 format spreadsheet file.

However when I view the spreadsheet all the required data is there but only sorted ascending by a single field. All the rest of the ‘nested’ sorting has got lost in translation somewhere…

Can anyone offer some advice on this please?

 

[ajetrumpet]

Ksan,

I know that when you transfer data the other way, Access doesn't conform to the formatting from Excel unless you force it. Maybe you could format your sheet before you send it over. I'm not sure if it would work, but its worth a try (running it once on a blank sheet I mean)...

Sub Test ()
  Dim r As Range
  For Each r In Range([column], [column].End(xlDown))
    With r
      .Value = "'" & .Value
    End With
  Next
End Sub

Be aware that running this on a blank sheet will take some time, because it will loop through all 65,000 and some rows if you are running Excel 2003.

 

[Ksan]

Thanks for the reply Ajetrumpet. I'm not sure that is quite the answer though, so I'll try and clarify my question a bit.

The actual 'formatting' of the output'ed Excel file is not a problem, and the data is not the issue

The query works as follows:

Select By [Department]

Sort by [Clock Number] ** As having a Clock Number isn't a fixed requirement, this also has the effect of splitting out the output into those with and those without Clock numbers. Those with, being sorted into sequence.

Sort by [Surname] ** Because this is after Clock number it is lower in priority and essentially then, only sorts those without a Clock number.

Sort by [System] ** Puts the systems used by each individual into alphabetic order.

Sort by [System Option] ** Puts the elements of a system into numberic order.

Now this is all just background info, and the query works and shows the data on-screen fine. The macro to run the query and export the data to Excel works fine too.

The data in the Excel spreadsheet created by the Macro is (apparently) only sorted by [Surname] and all the rest of the ordering is lost... Now I'm hypothesizing here but I think the [Surname] sort in the Excel file is just an illusion due to the order in which the results of the query are written to the Excel file and that none of the sort criteria has made the transition by design.

Is there any way to preserve the specific view of the Query when it is exported out?

 

[Ksan]

Result!

I continued on poking through different threads that looked similar and found a useful code snippet.

For the benefit of anyone who stumbles across this particular thread in the future, my problem was solved by assigning the vb code given below to a button on a form rather than having the button trigger a macro.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", "Filename", True