Problem Sorting An Excel Spreadsheet

[Barber]

I'm a novice and I'm trying to run a sort on an Excel spreadsheet, but get an error:

Public Member 'Sort' on type 'workbook' not found

Here is the code. I'm using VB Express 2010. Any help would be appreciated!



Option Strict Off
Option Infer On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.Reflection


Module Module1
Public Interface Sort

End Interface


Public oExcel As New Microsoft.Office.Interop.Excel.Application
Public oBook As Microsoft.Office.Interop.Excel.Workbook
Public oSheet As Microsoft.Office.Interop.Excel.Worksheet


Sub Main()



oBook = oExcel.Workbooks.Open("D:\DVD List.xls") ' replace "yourfile" with your filepath & name

oSheet = CType(oBook.ActiveSheet, Excel.Worksheet)

oBook.Activate()



oBook.Sort( _
Key1:=oSheet.Columns(6), Order1:=Excel.XlSortOrder.xlAscending, _
Key2:=oSheet.Columns(1), Order2:=Excel.XlSortOrder.xlAscending, _
Orientation:=Excel.XlSortOrientation.xlSortColumns, _
Header:=Excel.XlYesNoGuess.xlNo, _
SortMethod:=Excel.XlSortMethod.xlPinYin, _
DataOption1:=Excel.XlSortDataOption.xlSortNormal, _
DataOption2:=Excel.XlSortDataOption.xlSortNormal, _
DataOption3:=Excel.XlSortDataOption.xlSortNormal)

oBook.SaveAs("D:\DVD List1.xls")

End Sub

End Module

[si_the_geek]

Thread moved from the FAQ forum, which is not the place to post your questions.


Sorting an entire Workbook wouldn't really make much sense, and a Worksheet would be a bit dubious too... I'm pretty sure you need to sort a Range, eg:

oSheet.Range("A1:Z44").Sort( _
  ...

[Barber]

Thread moved from the FAQ forum, which is not the place to post your questions.


Sorting an entire Workbook wouldn't really make much sense, and a Worksheet would be a bit dubious too... I'm pretty sure you need to sort a Range, eg:

oSheet.Range("A1:Z44").Sort( _
  ...

Thanks! That was it, I only had to change it to A2 due to headings.

[steve2688]

Sorry those expect, i dont know how to compose a new post, hope some one will notice my question here,

i want to sort a excel in vb6, but keep come out a error .

the code is as per below.

Dim objExcel As Excel.Application
Dim ws As Worksheet
Dim wb As Workbook
Dim OutputLocation As String


OutputLocation = OutputPath & "" & "Merge.xls"

'MsgBox OutputLocation

Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Open(OutputLocation)
Set ws = wb.Worksheets("Sheet1") 'Specify your worksheet name


objExcel.Visible = True
ws.Range("A2").Select

ws.Range("A2:Z24").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
"B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal


and the Merge.xls file i want to sort Column A (Key1) and Column F(Key2) and Column G(Key3),
the excel have header, which mean the data start from row 2, the total of number is variance.

hope somebody can give me a help.

Thanks in Advance,

Steve
steve_41@hotmail.com