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
  • 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
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]
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
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
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]
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
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]
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
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

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