Sign in to follow this  
Followers 0
SpecialK

Getting data from an excel sheet?

3 posts in this topic

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

Share this post


Link to post
Share on other sites



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

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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by randallc

Share this post


Link to post
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
Sign in to follow this  
Followers 0