Worksheet Object

[generallevy]

Hi,

Im using access xp to programme a very simple database. Ive got a button to
transfer some data into excel and its all working great but theres one thing
ive been stuck on for a while now and thats how to sort a worksheet as part
of the automation process.

Ive looked in the worksheet object in object explorer and there isnt a sort
option. Ive seen the xlsortdata, xlsortmethod listed outside the worksheet
object listed as enums. Would these be any good?

Thanks for your help

This is the snippet of code im using

With objSht
.Activate

For intWait = 1 To 100
DoEvents
Next

.Range("A7").CopyFromRecordset rs
.Columns("A:A").NumberFormat = "hh:mm:ss;@"
.Columns("C:C").NumberFormat = "hh:mm:ss;@"
.Columns("A:J").Select

End With

 

[ryguy7272]

It is quite easy to perform operations in Excel, and control the entire
process from Access. Make sure you set a reference to Excel, and then run
this code in an Access module:

Option Compare Database

Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet


Sub Rep()

Dim strFile As String

strFile = "C:\Documents and Settings\ryan\Desktop\Ryan\Crosstab Query.xls"

‘Of course, this is just an example; put the actual path to your actual file
here…
' Opens Excel and makes it Visible

Set objExcel = New Excel.Application

objExcel.Visible = True

' Opens up a Workbook

Set xlWB = objExcel.Workbooks.Open(strFile)



' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.

Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")


With xlWS ' You are now working with the Named file and the named worksheet
' Your Excel code begins here…you can even record a macro and make the
process super easy!!

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile
xlWB.Close

xlapp.Quit
Set xlapp = Nothing

End Sub

Try doing it that way and let me know how it goes...

HTH,
Ryan---