Info from Excel to GUI using ExcelCOM_UDF

[quinner]

Hi guys

I'm having some trouble extracting data from Excel into GUI's.

Putting information into Excel seems pretty straightforward.

I've made a small GUI specifically to learn extracting this data, and if someone could fill in the blanks it would teach me a hell of a lot

........ I'm pulling my hair out trying to figure all this out

Constraints:

- the amount of rows will always be increasing, therefore having a lot selection possibilities from the first listbox

- the Excel file cannot remain open for any longer than a few seconds while reading or writing information, but the GUI needs to show it all the time.

CODE

#include <GUIConstants.au3>

#include <ExcelCOM_UDF.au3>

$oExcel = ObjGet ("c:\worksheet.xls", "Excel.Application")

$Form1 = GUICreate("Test", 817, 565, 190, 112)

;;;;; Buttons ;;;;;

$Button1 = GUICtrlCreateButton("View", 76, 54, 75, 25, 0) ; Displays information after row is selected in $List1

;;;;; Lists ;;;;;

$List1 = GUICtrlCreateList("", 32, 158, 145, 390) ; Shows all cells text from column A - By selecting one from the list it Displays all data from the Row in other listboxes

;;;;; This list could end up having a lot of data, as the amount of Rows in Excel document get higher

;;;;; Some type of find end function would rock (possibly $last = _XLLastRow ($path, "1")???)

$List2 = GUICtrlCreateList("", 332, 88, 360, 32) ; Displays info from cell (Column 2, row decided by $list1)

$List3 = GUICtrlCreateList("", 332, 188, 360, 32) ; Displays info from cell (Column 3, row decided by $list1)

$List4 = GUICtrlCreateList("", 332, 288, 360, 32) ; Displays info from cell (Column 4, row decided by $list1)

;;;;; Labels ;;;;;

$Label1 = GUICtrlCreateLabel("Shows all cells text from column A - By selecting one from the list it Displays all data from the Row in other listboxes", 32, 110, 220, 47)

$Label2 = GUICtrlCreateLabel("Displays info from cell (Column 2, row decided by $list1)", 302, 68, 400, 17)

$Label3 = GUICtrlCreateLabel("Displays info from cell (Column 3, row decided by $list1)", 302, 168, 400, 17)

$Label4 = GUICtrlCreateLabel("Displays info from cell (Column 4, row decided by $list1)", 302, 268, 400, 17)

GUISetState(@SW_SHOW)

While 1

$nMsg = GUIGetMsg()

Switch $nMsg

Case $GUI_EVENT_CLOSE

Exit

;;;;;

;;;;;

;;;;;

;;;;;

;;;;;

;;;;;

;;;;;

;;;;;

;;;;;

;;;;;

;;;;;

EndSwitch

WEnd

Thanks in advance - I know someone will love to help

[quinner]

While 1                     
    $Msg = ForumGetMsg()
    Switch $Msg
        Case $No_Reply
            BUMP()
    EndSwitch
WEnd

8)

[quinner]

Ok, looks like I'm not getting any help on this one

I'm trying to finish a complex interface that will be used by lots of people in my company,

but i'm only being held up by this part.

As I said, I can write to the excel files no problem,

but taking out the data, closing excel immediately, and then storing all of the data in sortable list/combo/edit boxes seems impossible to me, as there is no real noobs guide to the ExcelCOM_UDF (the UDF does rock though)

Is there any other Excel UDF that has a noobs guide to the commands?

[searchresult]

I have the same problem.

The thing is that I don't know what is the last cell number or how many rows are there in the file and just can't figure it out.

It would be easy to put it in the for loop if i knew the last cell number or the number of rows in the file.

[PsaltyDS]

Ok, looks like I'm not getting any help on this one

I'm trying to finish a complex interface that will be used by lots of people in my company,

but i'm only being held up by this part.

As I said, I can write to the excel files no problem,

but taking out the data, closing excel immediately, and then storing all of the data in sortable list/combo/edit boxes seems impossible to me, as there is no real noobs guide to the ExcelCOM_UDF (the UDF does rock though)

Is there any other Excel UDF that has a noobs guide to the commands?

Still pretty vague on what's stopping you:

"...taking out the data" implies to me _ExcelReadCell(), or _ExcelReadArray() -- is there trouble with these?

"...closing excel" implies to me _ExcelBookClose() -- is there trouble with that?

"...storing all of the data in sortable list/combo/edit boxes" has nothing to do with Excel at all, it's generic AutoIt GUI stuff.

Take it one step at a time -- How are you reading the data from Excel, and is that working OK?

[searchresult]

Still pretty vague on what's stopping you:

"...taking out the data" implies to me _ExcelReadCell(), or _ExcelReadArray() -- is there trouble with these?

"...closing excel" implies to me _ExcelBookClose() -- is there trouble with that?

"...storing all of the data in sortable list/combo/edit boxes" has nothing to do with Excel at all, it's generic AutoIt GUI stuff.

Take it one step at a time -- How are you reading the data from Excel, and is that working OK?

how do you know which is the last cell?

For $n = 0 To $Cellnum
                    
            $ListViewItems = _ExcelReadArray($oExcel, 3, 1, $n+3, 0, 1)
            
            _GUICtrlListViewInsertItem($ListView1,$n, $ListViewItems)
            
        Next

How to find out how many cells have data stored in it?

How to gel $CellNum

[PsaltyDS]

how do you know which is the last cell?

For $n = 0 To $Cellnum
                    
            $ListViewItems = _ExcelReadArray($oExcel, 3, 1, $n+3, 0, 1)
            
            _GUICtrlListViewInsertItem($ListView1,$n, $ListViewItems)
            
        Next

How to find out how many cells have data stored in it?

How to gel $CellNum

Look at _ExcelSheetUsedRangeGet().

[quinner]

Ok, forget about the closing excel straight away part.... My GUI will now create a temp excel file on each persons PC.

How do I put a whole column of cells from Excel into a listbox? (the amount of cells will always be changing)

I can take 1 cell out and display it, but have no idea how to do it for the whole column.

Then, If I select one of the cells by clicking on the listbox (and maybe pressing a button), how would I get it to display information from a different cell that is in the same row, into a seperate listbox?

^^ Thanks for the help so far

[searchresult]

Look at _ExcelSheetUsedRangeGet().

why this doesn't work?

$ListViewItems = ""
        
        $var = FileOpenDialog("Otvori dokument", @MyDocumentsDir , "excel (*.xls)", 2, "med")
        
        $oExcel = _ExcelBookOpen($var,0)
        
        $SheetName = _ExcelSheetNameGet($oExcel)
        
        $CellNum = _ExcelSheetUsedRangeGet($oExcel,$SheetName)
        
        $RowNum = $CellNum / 18
        
        For $n = 0 To $RowNum
            
            If $n > 1 Then
            
                For $i = 0 To 18
                
                    $ListViewItems &= _ExcelReadCell($oExcel, $n, $i)
                    
                Next
                
                    _GUICtrlListViewInsertItem($ListView1, 0, $ListViewItems & "|")
                        
            EndIf
            
        Next

[PsaltyDS]

Look inside the ExcelCOM_UDF.au3 include file, go down to the _ExcelSheetUsedRangeGet() function and read the comments header for it on how to use it. Specifically, that function returns an array. You don't have to calculate the values, just use the correct array reference:

; Return Value(s): On Success - Returns an array of used range values:

; $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank)

; $array[1] - The last cell used, in R1C1 format

; $array[2] - The last column used, as an integer

; $array[3] - The last row used, as an integer

So in your example $CellNum[3] already contains the last row number, which should be the last cell number in any given column.

It's quite as easy as "Read the help file!", but the listing is the place to look for notes on UDF included functions that are not in the AutoIt help file.

[searchresult]

Look inside the ExcelCOM_UDF.au3 include file, go down to the _ExcelSheetUsedRangeGet() function and read the comments header for it on how to use it. Specifically, that function returns an array. You don't have to calculate the values, just use the correct array reference:

So in your example $CellNum[3] already contains the last row number, which should be the last cell number in any given column.

It's quite as easy as "Read the help file!", but the listing is the place to look for notes on UDF included functions that are not in the AutoIt help file.

I have made some changes but it always writes the same row in the listbox, why?

$ListViewItems = ""
        
        $var = FileOpenDialog("Otvori dokument", @MyDocumentsDir , "excel (*.xls)", 2, "med")
        
        $oExcel = _ExcelBookOpen($var,0)
        
        $SheetName = _ExcelSheetNameGet($oExcel)
            
        $CellNum = _ExcelSheetUsedRangeGet($oExcel,$SheetName)
            
        For $n = 0 To $CellNum[3]
            
            If $n > 2 Then
            
                For $i = 0 To 18
                
                    $ListViewItems &= _ExcelReadCell($oExcel, $n, $i+1) & "|"
                    
                    
                Next
                    _GUICtrlListViewInsertItem($ListView1, 0, $ListViewItems & "|")
                        
            EndIf
            
        Next

EDIT:

$n is getting higher every step in the for loop 3, 4, 5, 6... but it is writing always the same row

Edited July 25, 2007 by searchresult

[searchresult]

I found out how to do it.

$ListViewItems = ""
        
        $var = FileOpenDialog("Otvori dokument", @MyDocumentsDir , "excel (*.xls)", 2, "med")
        
        $oExcel = _ExcelBookOpen($var,0)
        
        $SheetName = _ExcelSheetNameGet($oExcel)
            
        $CellNum = _ExcelSheetUsedRangeGet($oExcel,$SheetName)
        
        For $n = 3 To $CellNum[3]
            
                For $i = 0 To 18
                
                    $ListViewItems &= _ExcelReadCell($oExcel, $n, $i+1) & "|"
                    
                    
                Next
                
                _GUICtrlListViewInsertItem($ListView1, -1, $ListViewItems & "|")

                $ListViewItems = ""

        Next

Thanks to PsaltyDS.

Edited July 25, 2007 by searchresult