Solved

sort specified  column on excel

Posted on 2004-04-01
14
754 Views
Last Modified: 2013-12-18
guys,
 how to sort a specified column in excel?

thanks
love
0
Comment
Question by:lovemeto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10731531
From Notes? Are you in the right forum? If you are, then we need a lot more info...
0
 

Author Comment

by:lovemeto
ID: 10731585
sorry if my question is not that clear.
here is my situation

i have a lotus script that generate report to excel using db.search, because i am using db.search the data is not sorted so i need  to sort this using code that sort a specified column in excel.

thanks
love
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10731609
Ah, I understand. Two approaches:
a. use a view that's already sorted the way you want; by the way, db.search is VERY inefficient...
b. do it the way you described; I'd have to look up how to write the correct VB-script, but the easiest way for you is to record a macro, click in the spreadsheet how you want it sorted, then stop recording and open the macro-editor (Alt-F11); you then see the code you have to simulate from LotusScript
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lovemeto
ID: 10731742
but how i cant get your instruction,

thank you
0
 

Author Comment

by:lovemeto
ID: 10731766
a:using a view is not applicable on my application, so i need a dbsearch,
b: but how ? i cant get your instruction. im not good in excel macro

thanks


0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10731844
You don't need to be good at VBA, the macro buttons will do it for you. I assume you have a COM-object in LotusScript? Then you can adapt the code generated in Excel to your needs.

- So generate the (non-sorted) spreadsheet
- Open it in Excel
- Click on Extra/Macro's/Record new macro (maybe it's not called Extra in your version)
- select the whole spreadsheet
- click on Data/Sort and set it up to sort the way you want
- when sorting is finished, click on the macro stop recording button
- then type Alt-F11
0
 

Author Comment

by:lovemeto
ID: 10732011
soryy but i can get you instruction i cant see any changes on the properties of spreadsheet,

here is one thing i already have code using other code thant can fomat cell and row the only thing that i dont have is the code that sort a column.

here is my sample code

                       .application.activesheet.PageSetup.PrintTitleRows = "$1:$4"
            .application.activesheet.PageSetup.PrintTitleColumns =""
            .application.activesheet.PageSetup.LeftHeader = ""
            .application.activesheet.PageSetup.CenterHeader = ""            .application.activesheet.PageSetup.RightHeader = ""
            .application.activesheet.PageSetup.LeftFooter = ""
            .application.activesheet.PageSetup.CenterFooter = "Page &P"
            .application.activesheet.PageSetup.RightFooter = "&D"
            .application.activesheet.Pagesetup.Orientation = 2
            .application.activesheet.Pagesetup.PaperSize = 5


example 2


                                 Selection.HorizontalAlignment = xlLeft
            .Selection.VerticalAlignment = xlTop
            .Selection.WrapText = True
            .Selection.Orientation = 0
            .Selection.ShrinkToFit = False
            .Selection.MergeCells = False
            .Selection.Borders.LineStyle = 1
            .Selection.Borders.Weight = 2
            .statusbar="Completed ..."

but no code for column formating,


thank you


0
 

Author Comment

by:lovemeto
ID: 10732042
sorry but i cant get your instruction i cant see any changes on the properties of spreadsheet
0
 

Author Comment

by:lovemeto
ID: 10732157
can u please give me the code i need this one very badly, thanks a lot!
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 10732264
Hi lovemeto,

You are wrong.  A view IS appropriate to your situation.  Whatever your DBSearch formula is can be the SELECT formula for a view.

And what Sjef is talking about is as follows:

    - open the unsorted spreadhseet using teh regular Excel UI
    - tools->macro->record
    - select the columns/rows to be sorted
    - data -> sort
    - sort it
    - stop recording
    - tools -> macros
    - edit your newly-recorded macro

The statements you see are the exact statement you will use in LotusScript EXCEPT:
1) remove all the "paramater name :=" stuff, just leave the parts between the := and the , (and the , itself)
2) if there are any constants used as parameters, you will haveto find their value.  just create a new sub in the macro editor, and use msgbox PARAMETER_CONSTANT_NAME and run that sub.  It will display the value of that constant

Now, you can bring the same code over to LotusScript.
   

Cheers!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10732324
Hey, I don't have the code. And I'm not going to create it either. I know how to get there, but you don't seem to understand what I'm writing. Are you familiar with Excel basics, or not? I don't mean to be insulting, I like to help, but I don't like you trying to use my brains without trying your own brains first.
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 125 total points
ID: 10733802
Try this

Set Sheet = Excel.Application.ActiveWorkbook.Sheets.Application.Worksheets("Vamshee")
Call Sheet.Activate
Set rSortKey = ActiveSheet.Range("E1")       ' Sort Column 1st value
Set rSort = ActiveSheet.Range("E:E")           'Sort based on this column
rSort.Select
Set aRange = ActiveSheet.UsedRange
Call aRange.Sort(rSortKey, xlAscending)

~Hemanth
0
 

Author Comment

by:lovemeto
ID: 10738219
hermanth your code is working, but i want to specified where row it will going to start becuase  when i try your code, the title goes down. thanks .
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 10787609
Disservice.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question