Yet Another -- ExcelCOM UDF

[ptrex]

@Tweaky

Why would you need to open the files to update the values ?

You can use this to do so.

Const $xlLinkTypeExcelLinks = 1
Const $xlExcelLinks = 1

$objExcel = ObjCreate("Excel.Application")
$objExcel.Visible = True

$objWorkbook = $objExcel.Workbooks.Open("C:\Scripts\Test.xls", 3)

$arrLinks = $objWorkbook.LinkSources($xlLinkTypeExcelLinks)

For i = 1 to Ubound($arrLinks)
    $objWorkbook.BreakLink ($arrLinks(i), $xlLinkTypeExcelLinks)
Next

Beaware the last part does do a breaklink. You can remove it if needed.

regards,

ptrex

PS : corrected, forgot to add some quotes here and there.

Edited March 27, 2008 by ptrex

[Tweaky]

Hi,

hm sorry I don`t understand you.

What did you mean with "breaklink".

I get an syntax error at "$objWorkbook.BreakLink $arrLinks(i), $xlLinkTypeExcelLinks"

Yes I must open the first file to update file 2.

In file 2 are formulas (summewenn)

[Locodarwin]

Well, I'm sorry, Tweaky - I don't understand what it is you want to do. Is this truly a linked document scenario? My UDF doesn't support the creation of linked DDE/OLE, linked documents, or the like - but it should have no problem working with existing links, depending on what you need to do. Is there any way you can provide a better example?

-S

[hazed]

I am trying to open in on script upto 5 different excel books. 3 of them are Readonly. I am using excel 2007, haven't tested on 2003. All the books are written with 2003 or in 2003 compatible mode.

I open, read to array,close, but according to Taskmanager EXCEL.exe is still running. When I end the program if I didn't open any more books, the EXCEL process dies when the program ends. when it calls the other 2 readonly books, they get added to the Process table, but do not close cleanly when I close even though the close command says it did. The Processes are still active. and crashes excel 2007 until I end the processes and then the last open file gives warning in excel that it was not closed correctly

Any clue what is going on?

[Tweaky]

Hi,

hm, I have uploaded three files.

One is a test .au3 file - So I have tried to open the tweo excel files.

The second is mappe1.txt - Please rename this into mappe1.xls

The third is mappe2.txt - Please rename this into mappe2.xls

The two file will be opened correct - but in two instead of one instance.

So the formula in mappe2.xls (summewenn) is called #WERT.

So how can I attach a second file to the already opened first instance.

I`ll hope you have understand me

excel.au3

Mappe1.txt

Mappe2.txt

[ohgod]

_ExcelBookClose($bookExported,0,0)

When trying to close a book with the alerts of i get this message:

C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3 (395) : ==> The requested action with this object has failed.:

$oExcel.Application.DisplayAlerts = $fAlerts

$oExcel.Application^ ERROR

>Exit code: 1 Time: 25.065

an alert pops up, its about the clipboard that contains large amounts of data, do i want to save or not.

is there any way to solve this?

[Locodarwin]

ohgod,

Use something like this instead:

$ObjExcel.Application.DisplayAlerts = 0
$ObjExcel.ActiveWorkBook.Save
$ObjExcel.Quit

...until I fix that part of the UDF to everyone's satisfaction.

-S

[Locodarwin]

hazed,

I do not have that problem in all of my use of the UDF, and I use it to do what your example states on a daily basis. Perhaps you could post the exact code which reproduces the abandoned process?

-S

Edited March 31, 2008 by Locodarwin

[Locodarwin]

Tweaky,

I've run your example and what I get is the standard message asking me if I want to update the links from the source document. Remember when I mentioned before that the UDF does not support linked documents? There is a parameter needed in order to make it work. Try this code in place of yours:

#include<ExcelCOM_UDF.au3>

$datei_1 = @ScriptDir & "\" & "Mappe1.xls"
$datei_2 = @ScriptDir & "\" & "Mappe2.xls"

$oExcel1 = ObjCreate("Excel.Application")
$oExcel1.Visible = 1
$oExcel1.WorkBooks.Open($datei_1, 0)


$oExcel2 = ObjCreate("Excel.Application")
$oExcel2.Visible = 2
$oExcel2.WorkBooks.Open($datei_2, 0)

That should get you going!

-S

[Wooltown]

Updated _ExcelFindInrange, small bug found in it, look for ----- CHANGED HERE ----- in code.

Added _ExcelReadFormula

Please add and change in your code.

_ExcelFindInrange

expandcollapse popup

;===============================================================================
;
; Description:    Finds all instances of a string in a range and returns their addresses as a two dimensional array.
; Syntax:          _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
;                   $iDataType = 0, $iWholeOrPart = 1, $iSearchOrder = 1, $iSearchDirection = 1, $fMatchCase = False, _
;                   $fMatchFormat = False)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sFindWhat - The string to search for
;                   $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;                   $iColStart - The starting column for the number format(left) (default=1)
;                   $iRowEnd - The ending row for the number format (bottom) (default=1)
;                   $iColEnd - The ending column for the number format (right) (default=1)
;                   $iDataType - Limit the search to a certain kind of data (0=all, $xlFormulas(-4123), $xlValues(-4163), or $xlNotes(-4144)) (default=0)
;                   $iWholeOrPart - Whole or part of cell must match search string (1=Whole, 2=Part) (default=2)
;                   $fMatchCase - Specify whether case should match in search (True or False) (default=False)
;                   $fMatchFormat - Specify whether cell formatting should match in search (True, False, or empty string) (default=empty string=do not use parameter)
; Requirement(s):   AutoIt Beta 3.2.1.12
; Return Value(s):  On Success - Returns a two dimensional array with addresses of matching cells.  If no matches found, returns null string
;                       $array[0][0] - The number of found cells
;                       $array[x][0] - The address of found cell x in A1 format
;                       $array[x][1] - The address of found cell x in R1C1 format
;                       $array[x][2] - The row of found cell x as an integer
;                       $array[x][3] - The column of found cell x as an integer
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Starting row or column invalid
;                           @extended=0 - Starting row invalid
;                           @extended=1 - Starting column invalid
;                       @error=3 - Ending row or column invalid
;                           @extended=0 - Ending row invalid
;                           @extended=1 - Ending column invalid
;                       @error=4 - Data type parameter invalid
;                       @error=5 - Whole or part parameter invalid
; Author(s):        SEO <locodarwin at yahoo dot com> and MRDev, many thanks to DaLiMan
; Note(s):        None
;
;===============================================================================
Func _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "")
    Local $iCount, $sA1, $sR1C1, $sFound, $Temp1, $Temp2, $sFirst
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iDataType <> 0 Then
        If Not ($iDataType <> -4163 Or $iDataType <> -4123 Or $iDataType <> -4144) Then   ; ----- CHANGED HERE -----
            Return SetError(4, 0, 0)
        EndIf
    EndIf
    If $iWholeOrPart < 1 Or $iWholeOrPart > 2 Then Return SetError(5, 0, 0)
    If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Select
    Else
        $oExcel.Range($sRangeOrRowStart).Select
    EndIf
    If $iDataType = 0 Then
        If $fMatchFormat = "" Then
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default)
        Else
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat)
        EndIf
    Else
        If $fMatchFormat = "" Then
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, $iDataType, $iWholeOrPart, Default, Default, $fMatchCase, Default)
        Else
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, $iDataType, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat)
        EndIf
    EndIf
    If IsObj($oFound) Then
        $oFound.Activate
    Else
        Local $aFound[1][1]
        $aFound[0][0] = 0
        Return $aFound
    EndIf
    While 1
        If $iCount > 0 And $iCount < 2 Then $sFirst = $sA1
        $Temp1 = $oExcel.ActiveCell.Address
        $Temp2 = $oExcel.ActiveCell.Address(True, True, $xlR1C1)
        If $Temp1 = $sFirst Then ExitLoop
        If $iCount > 0 Then
            $sA1 = $sA1 & "*" & $Temp1
            $sR1C1 = $sR1C1 & "*" & $Temp2
        Else
            $sA1 = $Temp1
            $sR1C1 = $Temp2
        EndIf
        $iCount += 1
        $oExcel.Selection.FindNext($oExcel.ActiveCell).Activate
    WEnd
    Local $aFound[$iCount + 1][4]
    $sA1 = StringReplace($sA1, "$", "")
    Local $aA1 = StringSplit($sA1, "*")
    Local $aR1C1 = StringSplit($sR1C1, "*")
    $aFound[0][0] = $iCount
    For $xx = 1 To $iCount
        $aFound[$xx][0] = $aA1[$xx]
        $aFound[$xx][1] = $aR1C1[$xx]
        $Temp1 = StringRegExp($aR1C1[$xx], "[RZ]([^CS]*)[CS](.*)",3)
        $aFound[$xx][2] = Number($Temp1[1])
        $aFound[$xx][3] = Number($Temp1[0])
    Next
    Return $aFound
EndFunc;==>_ExcelFindInRange

_ExcelReadFormula

;===============================================================================
;
; Description:    Read the formula from the active worksheet of the specified Excel object.
; Syntax:          $val = _ExcelReadFormula($oExcel, $sRangeOrRow, $iColumn = 1)
; Parameter(s):  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sRangeOrRow - Either an A1 range, or an integer row number to read from if using R1C1
;                   $iColumn - The column to read from if using R1C1 (default = 1)
; Requirement(s):   None
; Return Value(s):  On Success - Returns the data from the specified cell
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Specified parameter is incorrect
;                           @extended=0 - Row out of valid range
;                           @extended=1 - Column out of valid range
; Author(s):        Wooltown 
; Note(s):        This function will only read one cell per call - if the specified range spans
;                   multiple cells, only the content of the top left cell will be returned.
;
;===============================================================================
Func _ExcelReadFormula($oExcel, $sRangeOrRow, $iColumn = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Formula
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Formula
    EndIf
EndFunc;==>_ExcelReadFormula

[Wooltown]

Updated _ExcelFindInrange, small bug found in it, look for ----- CHANGED HERE ----- in code.

Added _ExcelReadFormula

Please add and change in your code.

_ExcelFindInrange

expandcollapse popup

;===============================================================================
;
; Description:    Finds all instances of a string in a range and returns their addresses as a two dimensional array.
; Syntax:          _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
;                   $iDataType = 0, $iWholeOrPart = 1, $iSearchOrder = 1, $iSearchDirection = 1, $fMatchCase = False, _
;                   $fMatchFormat = False)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sFindWhat - The string to search for
;                   $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;                   $iColStart - The starting column for the number format(left) (default=1)
;                   $iRowEnd - The ending row for the number format (bottom) (default=1)
;                   $iColEnd - The ending column for the number format (right) (default=1)
;                   $iDataType - Limit the search to a certain kind of data (0=all, $xlFormulas(-4123), $xlValues(-4163), or $xlNotes(-4144)) (default=0)
;                   $iWholeOrPart - Whole or part of cell must match search string (1=Whole, 2=Part) (default=2)
;                   $fMatchCase - Specify whether case should match in search (True or False) (default=False)
;                   $fMatchFormat - Specify whether cell formatting should match in search (True, False, or empty string) (default=empty string=do not use parameter)
; Requirement(s):   AutoIt Beta 3.2.1.12
; Return Value(s):  On Success - Returns a two dimensional array with addresses of matching cells.  If no matches found, returns null string
;                       $array[0][0] - The number of found cells
;                       $array[x][0] - The address of found cell x in A1 format
;                       $array[x][1] - The address of found cell x in R1C1 format
;                       $array[x][2] - The row of found cell x as an integer
;                       $array[x][3] - The column of found cell x as an integer
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Starting row or column invalid
;                           @extended=0 - Starting row invalid
;                           @extended=1 - Starting column invalid
;                       @error=3 - Ending row or column invalid
;                           @extended=0 - Ending row invalid
;                           @extended=1 - Ending column invalid
;                       @error=4 - Data type parameter invalid
;                       @error=5 - Whole or part parameter invalid
; Author(s):        SEO <locodarwin at yahoo dot com> and MRDev, many thanks to DaLiMan
; Note(s):        None
;
;===============================================================================
Func _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "")
    Local $iCount, $sA1, $sR1C1, $sFound, $Temp1, $Temp2, $sFirst
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iDataType <> 0 Then
        If Not ($iDataType <> -4163 Or $iDataType <> -4123 Or $iDataType <> -4144) Then   ; ----- CHANGED HERE -----
            Return SetError(4, 0, 0)
        EndIf
    EndIf
    If $iWholeOrPart < 1 Or $iWholeOrPart > 2 Then Return SetError(5, 0, 0)
    If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Select
    Else
        $oExcel.Range($sRangeOrRowStart).Select
    EndIf
    If $iDataType = 0 Then
        If $fMatchFormat = "" Then
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default)
        Else
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat)
        EndIf
    Else
        If $fMatchFormat = "" Then
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, $iDataType, $iWholeOrPart, Default, Default, $fMatchCase, Default)
        Else
            $oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, $iDataType, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat)
        EndIf
    EndIf
    If IsObj($oFound) Then
        $oFound.Activate
    Else
        Local $aFound[1][1]
        $aFound[0][0] = 0
        Return $aFound
    EndIf
    While 1
        If $iCount > 0 And $iCount < 2 Then $sFirst = $sA1
        $Temp1 = $oExcel.ActiveCell.Address
        $Temp2 = $oExcel.ActiveCell.Address(True, True, $xlR1C1)
        If $Temp1 = $sFirst Then ExitLoop
        If $iCount > 0 Then
            $sA1 = $sA1 & "*" & $Temp1
            $sR1C1 = $sR1C1 & "*" & $Temp2
        Else
            $sA1 = $Temp1
            $sR1C1 = $Temp2
        EndIf
        $iCount += 1
        $oExcel.Selection.FindNext($oExcel.ActiveCell).Activate
    WEnd
    Local $aFound[$iCount + 1][4]
    $sA1 = StringReplace($sA1, "$", "")
    Local $aA1 = StringSplit($sA1, "*")
    Local $aR1C1 = StringSplit($sR1C1, "*")
    $aFound[0][0] = $iCount
    For $xx = 1 To $iCount
        $aFound[$xx][0] = $aA1[$xx]
        $aFound[$xx][1] = $aR1C1[$xx]
        $Temp1 = StringRegExp($aR1C1[$xx], "[RZ]([^CS]*)[CS](.*)",3)
        $aFound[$xx][2] = Number($Temp1[1])
        $aFound[$xx][3] = Number($Temp1[0])
    Next
    Return $aFound
EndFunc;==>_ExcelFindInRange

_ExcelReadFormula

;===============================================================================
;
; Description:    Read the formula from the active worksheet of the specified Excel object.
; Syntax:          $val = _ExcelReadFormula($oExcel, $sRangeOrRow, $iColumn = 1)
; Parameter(s):  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sRangeOrRow - Either an A1 range, or an integer row number to read from if using R1C1
;                   $iColumn - The column to read from if using R1C1 (default = 1)
; Requirement(s):   None
; Return Value(s):  On Success - Returns the data from the specified cell
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Specified parameter is incorrect
;                           @extended=0 - Row out of valid range
;                           @extended=1 - Column out of valid range
; Author(s):        Wooltown 
; Note(s):        This function will only read one cell per call - if the specified range spans
;                   multiple cells, only the content of the top left cell will be returned.
;
;===============================================================================
Func _ExcelReadFormula($oExcel, $sRangeOrRow, $iColumn = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Formula
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Formula
    EndIf
EndFunc;==>_ExcelReadFormula

[Wooltown]

Another updated function: _ExcelBookClose, updated to handle if the excel sheet is shared, and then you always get a question to save when you close it, regardless if you have mada any changes or not.

Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    Else
        $oExcel.Application.DisplayAlerts = False
        $oExcel.Activewindow.Close
    EndIf
    $oExcel.Application.ScreenUpdating = True
    $oExcel.Application.DisplayAlerts = True
    $oExcel.Quit
    Return 1
EndFunc;==>_ExcelBookClose

[Tweaky]

@Locodarwin

hm, this doesn`t work, too.

Two excel instances will be opened.

And the forumula doesn´t going actuall.

But I think it doesn`t work how I want.

So you can let it

Thanks for your good work

[Wooltown]

Sorry, the line to be changed in _ExcelFindInRange should be:

If NOT ($iDataType = -4163 Or $iDataType = -4123 Or $iDataType = -4144) Then then it just accepts the values in the test

if it is If Not ($iDataType <> -4163 Or $iDataType <> -4123 Or $iDataType <> -4144) all values will be accepted

if it is like the original If $iDataType <> -4163 Or $iDataType <> -4123 Or $iDataType <> -4144 no values will be accepted

[Locodarwin]

You're right, Wooltown, the logic works better that way. I'll update that for the next version of the UDF. As well, I'll add _ExcelReadFormula(), verbatim. Thanks for the correction and submission!

-S

[Locodarwin]

Tweaky,

Well, now you've stumped me. One of your documents is properly linked to the other. When I open the linked document after changing the values in the first document, I see what I would expect to see. I don't understand where you expect to take it from there.

-S

[Tweaky]

Oh,

sorry I think I don`t understand you correct.

Now I have created a xlw file with the two files and now it works with open the xlw file

[Locodarwin]

Ah. You wanted an Excel workspace that contained the two linked documents. Now I get it. That's not the kind of scenario I envisioned when I put the UDF together, since I expected the user to want to automate Excel on the workbook level. Workspaces are really only useful when doing manual tasks with Excel, which I presume is what you're doing. In fact, if all you need is something to launch your workspace, I recommend not using the UDF at all, and instead calling the workspace container document via the Run() or ShellExecute() functions. Including the UDF in your script (when all you want to do is launch your workspace) adds a lot of unnecessary overhead to it.

Good luck with your Excel endeavors!

-S

[Tweaky]

Thanks, this works perfectly

[hazed]

hazed,

I do not have that problem in all of my use of the UDF, and I use it to do what your example states on a daily basis. Perhaps you could post the exact code which reproduces the abandoned process?

-S

The file that is closed incorrectly when I kill the process, is only called in this routine. ( I know it's not great code, the "While Not _excelbookclose($oExcel, 0, 0)...wend" was added in an weak attempt to fix the problem and closing the book in 3 different places instead of one, was to kill anything I missed).

Func _getstreet($passed)

$skip = False

$oExcel = _ExcelBookOpen($Locidxls, 0, 1)

$exinfo = _ExcelSheetUsedRangeGet($oExcel, 1)

$locid = _ExcelReadSheetToArray($oExcel)

If $Employinfo[0] > 4 Then

$empstreet = _ExcelFindInRange($oExcel, $Employinfo[5], 1, 1, $exinfo[3], 2)

Else

$skip = True

While Not _excelbookclose($oExcel, 0, 0)

WEnd

Return ""

EndIf

If Not $skip Then

If StringStripWS($locid[$empstreet[1][3]][$empstreet[1][2] + 1], 3) = StringStripWS($passed, 3) Then

Return $locid[$empstreet[1][3]][$empstreet[1][2] + 2]

While Not _excelbookclose($oExcel, 0, 0)

WEnd

Else

$ftown = _ExcelFindInRange($oExcel, $passed, 1, 2, $exinfo[3], 2)

While Not _excelbookclose($oExcel, 0, 0)

WEnd

If $ftown[0][0] <> 1 Then

Dim $linesx[$ftown[0][0] + 1]

$line1 = ""

For $i = 1 To $ftown[0][0]

$linesx[$i] = StringStripWS($locid[$ftown[$i][3]][$ftown[$i][2]], 3) & ", " & StringStripWS($locid[$ftown[$i][3]][$ftown[$i][2] + 1], 3)

$line1 = $line1 & "|" & $linesx[$i]

Next

$form2 = GUICreate("Street search", 386, 145, 193, 125, BitOR($WS_CAPTION, $ws_popup, $WS_BORDER, $WS_CLIPSIBLINGS))

$NamesX = GUICtrlCreateList("", 15, 18, 350, 95, 0)

GUICtrlSetData(-1, $line1, "")

$select = GUICtrlCreateButton("Select", 15, 120)

GUISetState(@SW_SHOW)

While 1

$nMsg = GUIGetMsg()

Switch $nMsg

Case $GUI_EVENT_CLOSE

Exit

Case $select

$test = GUICtrlRead($NamesX)

$fields = StringSplit($test, ",")

$locid[$ftown[1][3]][$ftown[1][2] + 1] = StringStripWS($fields[2], 3)

ExitLoop

EndSwitch

WEnd

GUIDelete($form2)

EndIf

Return $locid[$ftown[1][3]][$ftown[1][2] + 1]

EndIf

EndIf

EndFunc ;==>_getstreet