Access to Excel transferspreadsheet and automatic sort data

[cbs81]

Hi, im puzzeled.

I have an access data base that has two colums of data, a driver name in the first column and an ID number in the second column.

in access, I do a data sort by ID number and then, when I run the following code it dumps this data in excel, but its not sorted in the same order as in excel.

My objective is to have this data automatically sorted in excel without having to do anything in excel... ie.. I want the sorted version in Access to be dumped in excel, exactly as it appears in Access...

the code i have running in access is as follows

[VBA]

Option Compare Database
Function modTransfer()
On Error GoTo modTransfer_Err
'docmd.TransferSpreadsheet TransferType, Version, TableName, _
'FileName, HasFieldNames(True/False), Range
DoCmd.TransferSpreadsheet acExport, 8, "MASTER", _
"H:\Cash reconciliation progressive backup\clarks driver database migration\Driver Database.xls", True, ""

modTransfer_Exit:
Exit Function
modTransfer_Err:
MsgBox Error$
Resume modTransfer_Exit
End Function



[/VBA]


What do I do and how do i do it??

[geekgirlau]

Is "MASTER" the table? If so, you'd probably be better off creating a query that sorts the records by ID, and export the query rather than the table.

[cbs81]

HI there.. I already tried to export a query to excel but how do you do this in code...

btw, master is the tabel name

I created a query named QRY_MASTER

then wrote the code:

[VBA]


Option Compare Database
Function modTransfer()
On Error Goto modTransfer_Err
DoCmd.TransferSpreadsheet acExport, 8, "QRY_MASTER", _
"H:\Cash reconciliation progressive backup\clarks driver database migration\Driver Database.xls", True, ""

modTransfer_Exit:
Exit Function
modTransfer_Err:
MsgBox Error$
Resume modTransfer_Exit
End Function
[/VBA]


but this doesnt work... how can i fix this all in code.. so i can export the results of a query to excel.... thankyou sooo much

[geekgirlau]

That looks correct, although you don't need anything after TRUE in your TransferSpreadsheet statement.

What is the error that you're receiving?