Jump to content

Criteria based Sorting in an Excel File

litlmike

By litlmike,
in AutoIt General Help and Support

 Share

Recommended Posts

litlmike Universalist

litlmike

Posted
  • Author
Posted

Hi,

glad it works;

Here's one way (many others!)

While Not $objRecordSet.EOF ()
                .cells ($i_viewer, $j_viewer) = $objRecordSet.Fields (0).value
                .cells ($i_viewer, $j_viewer + 1) = $objRecordSet.Fields (12).value
                $aArray_sum1 += $objRecordSet.Fields (12).value
                .cells ($i_viewer, $j_viewer + 2) = $objRecordSet.Fields (13).value
                $aArray_sum2 += $objRecordSet.Fields (13).value
                $objRecordSet.MoveNext ()
                $i_viewer += 1
            WEnd
                .cells ($i_viewer, $j_viewer) = "Avge"
                .cells ($i_viewer, $j_viewer + 1) =$aArray_sum1/($i_viewer-2)
                .cells ($i_viewer, $j_viewer + 2) = $aArray_sum2/($i_viewer-2)
            MsgBox(0, "", "TEST", 2)
Randall

Thanks!!! Your code pwns mine! And it works! Woo hoo!

_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
  • Replies 53
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

litlmike Universalist

litlmike

Posted
  • Author
Posted

@ptrex

1) With your GUI Excel Viewer thing, is there a way to resize the colums to fit the data? Like in Excel, when you double click the colum to resize.

2) I notice that on Machines with Office 2000, I am not able to get your GUI excel viewer to declare the object. Even though you have the following code in there:

;~  Declare objects
$oExcel = ObjCreate("OWC10.spreadsheet"); Default to Office XP

If not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003    
EndIf
IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC00.spreadsheet"); Office 2000

Suggestions?

Thanks.

_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
Locodarwin Universalist

Locodarwin

Posted
Posted

@ptrex

1) With your GUI Excel Viewer thing, is there a way to resize the colums to fit the data? Like in Excel, when you double click the colum to resize.

2) I notice that on Machines with Office 2000, I am not able to get your GUI excel viewer to declare the object. Even though you have the following code in there:

;~  Declare objects
$oExcel = ObjCreate("OWC10.spreadsheet"); Default to Office XP

If not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003    
EndIf
IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC00.spreadsheet"); Office 2000

Suggestions?

Thanks.

1. Use the AutoFit method:

$oExcel.Rows.AutoFit

$oExcel.Columns.AutoFit

You can also specify the particular row or column, like this:

$oExcel.Columns(2).AutoFit

If you want to specify specific heights or widths, use the .RowHeight or .ColumnWidth properties:

$oExcel.Range("a1:a10").RowHeight = 15 ; the value is in PostScript points

2. Use OWC9.Spreadsheet.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites
randallc Universalist

randallc

Posted
Posted

Hi,

From @ptrex [or @Blink123?] originally;

.columns ($i).autofitoÝ÷ Ù«­¢+Ù%II ÅÕ½Ðí!-
HÀäÈí=]ä¹ÍÁÉÍ¡ÐÅÕ½Ðì°ÅÕ½ÐìÅÕ½Ðì¤Q¡¸ÀÌØíá±1Xô=©
ÉÑ ÅÕ½Ðí=]ä¹ÍÁÉÍ¡ÐÅÕ½Ðì¤
best, randall
ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

Hi,

From @ptrex [or @Blink123?] originally;

.columns ($i).autofitoÝ÷ Ù«­¢+Ù%II ÅÕ½Ðí!-
HÀäÈí=]ä¹ÍÁÉÍ¡ÐÅÕ½Ðì°ÅÕ½ÐìÅÕ½Ðì¤Q¡¸ÀÌØíá±1Xô=©
ÉÑ ÅÕ½Ðí=]ä¹ÍÁÉÍ¡ÐÅÕ½Ðì¤oÝ÷ Ù·¬¶¶§u©eþ«¨µâèr§v¶§u©eq1ÚK¢»az)éºÒ0Ym¯+azãyËm)à²'NºÚ×hzÌ(®M÷Z­Ëaz¸ Û2Ö±~æºX­W¢µé¬v®+^®&GÁ©í¶ç-JÖ­zÍý
ýjwwôÁ7ôZ+{-jw0¢é]"jüºÚ"µÍ][ÝÔÑSPÕ
ÓHÔÚY]IÌÍ×HÒTHÝ]HH  ÌÎNÐÐIÌÎNÈ   ][Ý
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

I am still having problems with Office 2000. I cannot declare the OWC Spreadsheet. Here is what I have so far.

;~  Declare objects
$oExcel = ObjCreate("OWC10.spreadsheet"); Default to Office XP

If not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003    
EndIf
IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC00.spreadsheet"); Office 2000
EndIf

IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC9.spreadsheet"); Office 2000 - Another way to do it, according to randallc
EndIf

If RegRead("HKCR\OWC9.spreadsheet", "") Then 
        If @error = 1 Then
            MsgBox (0, "", "@error =" & @error & @CRLF & "Unable to open requested key", 2)
        ElseIf @error = 2 Then
            MsgBox (0, "", "@error =" & @error & @CRLF & "Unable to open requested main key", 2)
        ElseIf @error = 3 Then
            MsgBox (0, "", "@error =" & @error & @CRLF & "Unable to remote connect to the registry", 2)
        ElseIf @error = -1 Then
            MsgBox (0, "", "@error =" & @error & @CRLF & "Unable to open requested value", 2)
        ElseIf @error = -2 Then
            MsgBox (0, "", "@error =" & @error & @CRLF & "Value type not supported", 2)
        EndIf

    $ExcelLV = ObjCreate("OWC9.spreadsheet")
EndIf
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
Locodarwin Universalist

Locodarwin

Posted
Posted (edited)

Delete this part:

IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC00.spreadsheet"); Office 2000
EndIf
oÝ÷ Ø    ݲ)Üy¼­ëb쨺·ray EæÊ«¡¸ÞrÛ!¢é]jZÞiÜyÊÞj×Ê¢}ý¶wvÚ¡©ò¶§­Ø^­è"²Úòbjx¶¸¡ûay Eæ!û^²Ö¬Áée)ÚÉ⦭jëh×6
;~  Declare objects
$oExcel = ObjCreate("OWC10.spreadsheet"); Default to Office XP

If not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003   
EndIf

IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC9.spreadsheet"); Office 2000 - Another way to do it, according to randallc
EndIf

-S

Edited by Locodarwin
(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

Hmmm, still not working. Suggestions? Other things I should look at?

Also, is there a way to make those Excel values show up as a clickable hyperlink?

For instance, if I wanted the following to input the values as a link:

;Provides the SF Link in the GUI Viewer
                    .cells ($i_viewer, $j_viewer + 3) = $objRecordSet.Fields (8).value
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
Locodarwin Universalist

Locodarwin

Posted
Posted

What's not working, specifically? Do you get an error on the object creation? Or just no object created?

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites
randallc Universalist

randallc

Posted
Posted (edited)

Hi,

"SELECT * FROM [Sheet1$] WHERE State = 'CA' OR 'ME'"oÝ÷ ØÓ¢{"uêí+0k(§²Ú÷ô Rµ«^¡÷hx")ìµæÊjx§r[z)íç^)¢µçl¶¸§«&j|¬µ«^±«2¢ìÖ®¶­sc´66W74W6VÄW3æS2¢6æ6ÇVFRgV÷C´66W744ôÒæS2gV÷C°¤vÆö&Âb33c·5öF&æÖSÔfÆTvWE6÷'DæÖR67&DF"fײgV÷C²b3#´W6VÂW×ÆRçÇ2gV÷C²Âb33c·5õF&ÆVæÖRÒgV÷Cµµ6VWCb33cµÒgV÷C°¢b33c¶õöFô6öâÒö&¤7&VFRgV÷C´DôD"ä6öææV7FöâgV÷C²¢b33c¶õöFô6öâä÷VâgV÷Cµ&÷fFW#ÔÖ7&÷6ögBä¦WBäôÄTD"ãBã´FF6÷W&6SÒgV÷C²fײb33c·5öF&æÖRfײgV÷C³´WFVæFVB&÷W'FW3ÒgV÷C²gV÷C´W6VÂã´E#ÕW3²gV÷C²gV÷C³²gV÷C²¢b33c·VW'ÒgV÷Cµ4TÄT5B¢e$ôÒgV÷C²fײb33c·5õF&ÆVæÖRfײgV÷C²tU$Rç7G"b33´4ÄÔRb33²Å7FFRgV÷C²fײð gV÷C²æB6FTBfÇC³#²gV÷C³²fײð¢b33c¶%õ&÷w2Òõ&V6÷&E6V&6b33c·5öF&æÖRÂb33c·VW'Âb33c¶õöFô6öâ¥ô'&fWuVW'F&ÆRb33c¶%õ&÷w2Âb33c·VW'
Randall Edited by randallc
ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

@randallc

Thanks for the heads up on that coding.

What's not working, specifically? Do you get an error on the object creation? Or just no object created?

-S

On Windows 2000 with Office 2000 installed, I cannot create the objects below. The error I get is based on the Else Statement for "Not an object" (shown below).

Else
       MsgBox(0,"Reply","Not an Object",4)oÝ÷ ØæãyËl­æ­yÖ®¶­sdö&¤7&VFRgV÷C´õt3ç7&VG6VWBgV÷C²¤ö&¤7&VFRgV÷C´õt3ç7&VG6VWBgV÷C²¤ö&¤7&VFRgV÷C´õt3ç7&VG6VWBgV÷C²oÝ÷ Ø*yú+*ç-r·¶*'r©¢w§µ«­¢+Øíø±É½©ÑÌ(ÀÌØí½á°ô=©
ÉÑ ÅÕ½Ðí=]ÄÀ¹ÍÁÉÍ¡ÐÅÕ½Ðì¤ìÕ±ÐѼ=¥a@()%¹½Ð%Í=¨ ÀÌØí½á°¤Q¡¸(ÀÌØí½á°ô=©
ÉÑ ÅÕ½Ðí=]ÄĹÍÁÉÍ¡ÐÅÕ½Ðì¤ì=¥ÈÀÀÌ)¹%()%¹½Ð%Í=¨ ÀÌØí½á°¤Q¡¸(ÀÌØí½á°ô=©
ÉÑ ÅÕ½Ðí=]ä¹ÍÁÉÍ¡ÐÅÕ½Ðì¤ì=¥ÈÀÀÀ´¹½Ñ¡ÈÝäѼ¼¥Ð°½É¥¹Ñ¼É¹±±)¹%((%%Í=¨ ÀÌØí½á°¤Q¡¸(Ý¥Ñ ÀÌØí½á°(ì¹]½É­Í¡ÑÌ ÅÕ½ÐíM¡ÐÄÅÕ½Ð줹ѥÙÑ(ì¹Ñ¥Ù͡йɹ ÅÕ½ÐíÄéÄÀÅÕ½Ð줹ٱÕôÅÕ½ÐíQMP%9
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...