Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Object
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Object
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "generallevy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Charts and Charting in Excel | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Discussion (Misc queries) | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Worksheet Functions | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Programming | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | New Users to Excel |