Getting data from an excel sheet?

[SpecialK]

Hello,

i have a small Program where i can type a Name and the program gives me the according pc number. At the moment, all the informations are in the sourcecode hardcoded. This is of course not the best solution because there are often changes in the data.

So i want to get the information out of an excel sheet. I know that i have to use Exelcom, but i didn't get this working.

I don't want you to write my program but it would be great if you can give me a tip how to search in the excel sheet.

In the example i want to type "Michael" in an InputBox and then i want the program to give me his pc number "xp1212" in a Messagebox.

Thank you

SpecialK

[ptrex]

This is not exactly what you need, but it should get you starting.

expandcollapse popup

Opt("MustDeclareVars", 1)

; Declarations
Dim $oAppXL, $oWorkbook, $oWorksheet
Dim $hOutputFile
Dim $sWorkbookPath, $sOutputFilePath

; Flags declarations - Excel
Global Const $xlCellTypeLastCell = 11

; Initialize variables
$sWorkbookPath = "C:\Test.xls"
$sOutputFilePath = "C:\Test.txt"

; Create Excel object
$oAppXL = ObjCreate("Excel.Application")
        
; Check if object creation succeeded
If Not IsObj($oAppXL) Then
    MsgBox(16, "ActiveX Error", "Excel object creation failed." & @CRLF & _
                "Please verify the installation of Microsoft Excel.")
    Exit
EndIf

; Open workbook and text file
$oAppXL.Workbooks.Open($sWorkbookPath)
$hOutputFile = FileOpen($sOutputFilePath, 2)

; Find the opened workbook in all the workbooks that are open
For $i = 1 to $oAppXL.Workbooks.Count
    If $oAppXL.Workbooks($i).FullName = $sWorkbookPath Then
        $oWorkbook = $oAppXL.Workbooks($i)
    EndIf
Next

; Associate first sheet in workbook with $oWorksheet
$oWorksheet = $oWorkbook.Worksheets(1)

; Read all rows in column A and write contents to output text file
For $i = 1 to $oWorksheet.Cells.SpecialCells($xlCellTypeLastCell).Row
    FileWriteLine($hOutputFile, $oWorksheet.Cells($i, 1).Value)
Next

; Close files
FileClose($hOutputFile)
$oAppXL.Quit

Regards

ptrex

[randallc]

Hi,

If you want to use "ExcelCom.au3", here's a way;

;XL_Search2.au3  0_2
;_XLSearch($s_FilePath,$s_i_Sheet,$s_i_ExcelValue,$s_i_Visible)
#include"ExcelCOM.au3"
;****** Create Blank Files, then add some data to XL file as example in column "A"  on
$ReadXLPath=@ScriptDir&"\ReadXLPath.xls"
_XLCreateBlank($ReadXLPath)
_XLWrite($ReadXLPath,1,5,5,"g",0)
$DataString="Peter, xp1563"&@CRLF&"Name,PC-Number"&@CRLF&"Joe, 1521"&@CRLF&"Michael,xp1212"
$DataString=StringReplace($DataString,",",@TAB)
$XLRange=_XLpaste($ReadXLPath,1,"A",1,$DataString,1,"",0) ; _XLpaste($ReadXLPath,1,"Z",11,$DataString,1,0,1)

;****** Now read addresses from array or "Nothing" string*****************
;func _XLSearch($s_FilePath,$s_i_Sheet,$s_i_ExcelValue,$s_i_Visible)
$s_FoundList=_XLSearch($ReadXLPath,1,"Michael",1)
if $s_FoundList<> "Nothing" then
    $a_ArrayAnswer=StringSplit($s_FoundList,"|")
    _ArrayDisplay($a_ArrayAnswer,"")
Else
    msgbox(0,"","$s_FoundList="&$s_FoundList)
    exit
EndIf; loop through the array of answers if there is more than 1;
$Row = StringRegExpReplace($a_ArrayAnswer[1], "[a-z,A-Z,$]", "", 0); to get the row number from the address; 
$XLRowToArray=_XLRowToArray($ReadXLPath,1,$XLRange,$Row)
_ArrayDisplay($XLRowToArray," Get it from the array on this raow; $XLRowToArray")

best, randall Edited August 14, 2006 by randallc