I can read from 3 spreadsheets but how to read from 3 worksheets within 1 Spreadsheet?

[joeloyzaga]

the code is below - but I'd like to read the worksheets named "Servers,Biztalkserver,SQLserver" from 1 spreadsheet - they are currently in their own spreadsheets

I would like to also be able to read down specific columns (by header or cell number) if possible - Can this be done? can you show me?biztalkinput.au3Servers.xlsxSqlserver.xlsxBiztalkserver.xlsx

expandcollapse popup

Global $sFilePathOrigin = 'C:\pwc\'

Dim $Display1 = ""

Dim $sPathAUT = 'C:\pwc\Servers.xlsx'

Dim $sPathUSERS = 'C:\pwc\Biztalkserver.xlsx'

Dim $sPathENVS = 'C:\pwc\Sqlserver.xlsx'

Dim $oExcel1 = _ExcelBookOpen($sPathAUT, 0)

Dim $oExcel2 = _ExcelBookOpen($sPathUSERS, 0)

Dim $oExcel3 = _ExcelBookOpen($sPathENVS, 0)

Dim $aArrayAUT = _ExcelReadSheetToArray($oExcel1)

Dim $aArrayUSER = _ExcelReadSheetToArray($oExcel2)

Dim $aArrayENVS = _ExcelReadSheetToArray($oExcel3)

_ExcelBookClose($oExcel1, 0)



_ExcelBookClose($oExcel3, 0)

Dim $cellcount = 0

Dim $sCellValue = ''

Dim $sFilePath

Dim $vsheet

For $i = 1 to 256

    $sCellValue = _ExcelReadCell($oExcel2, $i, 1)

    If $sCellValue = '' Then

        ;MsgBox(0x40, 'Cell count is ', $i)

        $cellcount = $i

        $i = 256

    EndIf

Next

$cellcount = $cellcount - 1



Dim $sStr = ''

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

    For $j = 1 To $aArrayAUT[0][1]

        If $aArrayAUT[$i][$j] <> "" Then $sStr &= $aArrayAUT[$i][$j] & '|'

    Next

Next

$sStr = StringTrimRight($sStr, 1)

Dim $sStr2 = ''

For $i = 1 to $cellcount

    $sStr2 &= _ExcelReadCell($oExcel2, $i, 1) & '|'

Next

_ExcelBookClose($oExcel2, 0)

; Open an Excel file - set to not visible and read-only

$oExcel = _ExcelBookOpen($sFilePath, 0, True)



; Move to correct sheet

_ExcelSheetActivate($oExcel, $vSheet)



; Read in cells

; Initialise an array

Global $aArray[1]

; Work down sheet until there is an empty cell

$iRow = 1

Do

    _ArrayAdd($aArray, _ExcelReadCell($oExcel, $iRow)); Default column is 1

    $aArray[0] += 1; Increase element count

    $iRow += 1 ; Move to next row

Until _ExcelReadCell($oExcel, $iRow) = ""; Looks for an empty cell to end



; Close Excel file

_ExcelBookClose($oExcel)

;Dim $sStr2 = ''

;For $i = 1 To $aArrayUSER[0][0]

;    For $j = 1 To $aArrayUSER[0][1]

;        If $aArrayUSER[$i][$j] <> "" Then $sStr2 &= $aArrayUSER[$i][$j] & '|'

;    Next

;Next

$sStr2 = StringTrimRight($sStr2, 1)

Dim $sStr3 = ''

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

    For $j = 1 To $aArrayENVS[0][1]

        If $aArrayENVS[$i][$j] <> "" Then $sStr3 &= $aArrayENVS[$i][$j] & '|'

    Next

Next

$sStr3 = StringTrimRight($sStr3, 1)

[water]

This can easily be done with the latest version of AutoIt and the completely rewritten Excel UDF.

[joeloyzaga]

is the udf help in latest version?

[water]

Sure.