dcsimg
Results 1 to 4 of 4

Thread: Problem Sorting An Excel Spreadsheet

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    15

    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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,236

    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( _
      ...

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    15

    Re: Problem Sorting An Excel Spreadsheet

    Quote Originally Posted by si_the_geek View Post
    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.

  4. #4
    Junior Member
    Join Date
    May 2010
    Posts
    21

    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 Name:  VB_Error.jpg
Views: 36
Size:  28.0 KB.

    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
  •  



Featured


Click Here to Expand Forum to Full Width