Solved

sort specified  column on excel

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

thanks
love
0
Comment
Question by:lovemeto
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

932 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now