joeloyzaga Posted July 21, 2014 Share Posted July 21, 2014 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 popupGlobal $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) Link to comment Share on other sites More sharing options...
water Posted July 21, 2014 Share Posted July 21, 2014 This can easily be done with the latest version of AutoIt and the completely rewritten Excel UDF. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
joeloyzaga Posted July 21, 2014 Author Share Posted July 21, 2014 is the udf help in latest version? Link to comment Share on other sites More sharing options...
water Posted July 21, 2014 Share Posted July 21, 2014 Sure. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now