-
Apr 11th, 2017, 02:56 PM
#1
Thread Starter
New Member
Problem Sorting An Excel Spreadsheet
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
-
Apr 11th, 2017, 03:42 PM
#2
Re: Problem Sorting An Excel Spreadsheet
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:
Code:
oSheet.Range("A1:Z44").Sort( _
...
-
Apr 11th, 2017, 04:21 PM
#3
Thread Starter
New Member
Re: Problem Sorting An Excel Spreadsheet
 Originally Posted by 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:
Code:
oSheet.Range("A1:Z44").Sort( _
...
Thanks! That was it, I only had to change it to A2 due to headings.
-
May 28th, 2019, 02:54 AM
#4
Junior Member
Re: Problem Sorting An Excel Spreadsheet
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|