Solved

sort specified  column on excel

Posted on 2004-04-01
14
768 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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