Syed23 Posted April 23, 2012 Share Posted April 23, 2012 Hi All, I am having data which will be displayed by using ArrayDisplay(). if i select an option and paste it in to excel that get save as text. instead i wanted to save it in to excel format. is there any option available? sorry for my poor searching skill.. Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
water Posted April 23, 2012 Share Posted April 23, 2012 (edited) To copy the content of an array to Excel try _ExcelReadArray. Edited April 23, 2012 by water 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...
Syed23 Posted April 23, 2012 Author Share Posted April 23, 2012 To copy the content of an array to Excel try _ExcelReadArray.Thanks for the quick reply Water! But this is wise-versa of what i am asking right? or can we use it ? just got confused Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
water Posted April 24, 2012 Share Posted April 24, 2012 Correct, sorry. You need _ExcelWriteArray. 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...
Syed23 Posted April 24, 2012 Author Share Posted April 24, 2012 (edited) Correct, sorry. You need _ExcelWriteArray.This is perfect! i am an idiot i was digging in to the help file and google by searching as _Arraywritetoexcel() ...Thanks a lot for your help water! Edited April 24, 2012 by Syed23 Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
water Posted April 24, 2012 Share Posted April 24, 2012 The larger the array gets the slower _ExcelWriteArray is. If you have performance problems please drop me a note. There is a much faster way available. 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...
Syed23 Posted April 24, 2012 Author Share Posted April 24, 2012 The larger the array gets the slower _ExcelWriteArray is. If you have performance problems please drop me a note. There is a much faster way available.i have some large datas to be written in to the excel. if you have another good option please provide me water! Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
water Posted April 24, 2012 Share Posted April 24, 2012 This taks array $aOverView, transposes it and copies the content to the range with the left top position in cell A1; Copy data to Excel. Taken from http://www.avdf.com/apr98/art_ot003.html $aOverViewTransposed = $oExcel.transpose($aOverView) $oExcel.Range("A1:A1").Resize(UBound($aOverView,1),UBound($aOverView,2)) = $aOverViewTransposed 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...
Syed23 Posted April 24, 2012 Author Share Posted April 24, 2012 This taks array $aOverView, transposes it and copies the content to the range with the left top position in cell A1; Copy data to Excel. Taken from http://www.avdf.com/apr98/art_ot003.html $aOverViewTransposed = $oExcel.transpose($aOverView) $oExcel.Range("A1:A1").Resize(UBound($aOverView,1),UBound($aOverView,2)) = $aOverViewTransposed Let me try this out water! Thanks a lot! Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
AmbiguousJoe Posted June 27, 2012 Share Posted June 27, 2012 (edited) Hey Water, I've been using the code you posted to quickly update my Excel Sheets (It's dropped my Excel times from 6+ minutes to 5 seconds). It's worked fine for all my data up until now. My script is now crashing whenever it hits my excel update function with a large array. The array is 580 rows, 20 columns. If I run it on my smaller projects, under 100 rows, it completes just fine. I checked the help files and couldn't find anything that helped me with the UBound and .transpose commands. Is there any way to catch the following error so my script doesn't stop when it hits it? -=Console's output=- Function: UpdateExcelFile U:My DocumentsScriptsPPNavigator8.au3 (2351) : ==> The requested action with this object has failed.: $aOverViewTransposed = $oExcel.transpose($aExcelList) $aOverViewTransposed = $oExcel.transpose($aExcelList)^ ERROR Func _UpdateExcelFile() ConsoleWrite("Function: UpdateExcelFile"&@CRLF) Local $oExcel = _ExcelBookOpen($sExcelTemplateFile) _ExcelSheetActivate($oExcel,"Passport Information") Sleep($nLagTimer) ;Copy data to Excel. $aOverViewTransposed = $oExcel.transpose($aExcelList) $oExcel.Range("A1:A1").Resize(UBound($aExcelList,1),UBound($aExcelList,2)) = $aOverViewTransposed _ExcelBookSaveAs($oExcel, $sExcelCompletionFile, "xls", 0, 1) Sleep($nLagTimer) _ExcelBookClose($oExcel, 0) EndFunc ;==>UpdateExcelFile Edited June 27, 2012 by AmbiguousJoe Link to comment Share on other sites More sharing options...
water Posted June 27, 2012 Share Posted June 27, 2012 Which version of Excel do you use? 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...
AmbiguousJoe Posted June 28, 2012 Share Posted June 28, 2012 (edited) I use Excel 2007, however, I managed to isolate the problem a bit more. It seems I broke it when adding some code to another function (which is usually the case). Along with your code to Write Excel files quickly, I found the following code somewhere on these forums in order to read Excel files very quickly. However, I added a loop in the ReadExcelFile function to handle cells which had over 256 characters (which otherwise get converted to "###" using this method). expandcollapse popup;Opens Excel Project File and creates an array for it. Func _ReadExcelFile() Local $oExcel = _ExcelBookOpen($sExcelProjectFile) Global $aDistCodes = ReadSheet($oExcel, "DistCodes") ;Creates an array of Distribution Codes. Global $nTotalDistCodes = $nTotalRows Global $nTotalDistTypes = $nTotalCols Global $aExcelList = ReadSheet($oExcel, "Passport Information") ;Creates an array of Update information. Global $nTotalExcelRows = $nTotalRows Global $nTotalExcelCols = $nTotalCols ;This code prevents cells that are over 256 characters from showing up as all pound symbols. ;Cycles through all the data rows of the Excel Spreadsheet For $nCount = 0 To $nTotalExcelRows - 1 ;Cycles through all delimiter columns of the Excel spreadsheet. Earlier cols should be under 256 characters. For $nLoopCount = 10 To $nTotalExcelCols - 1 ;If a cell is populated by all pound symbols (assumed if starts with 3 of them) If StringLeft($aExcelList[$nCount][$nLoopCount], 3) = "###" Then ;Updates the Array value with the correct spreadsheet value. $aExcelList[$nCount][$nLoopCount] = _ExcelReadCell($oExcel, $nCount+1, $nLoopCount+1) EndIf Next Next ConsoleWrite("There are "&$nTotalExcelRows&" rows, and "&$nTotalExcelCols&" columns in the Project file."&@CRLF) _ExcelBookClose($oExcel) EndFunc ;==>ReadExcelFile Func ReadSheet($excel, $sheet) ;Used by the new Excel read-in function Return String2DSplit(ExcelSheetToClip($excel, $sheet)) EndFunc ;==>ReadSheet Func String2DSplit($string, $rowDelimiter = @CRLF, $columnDelimiter = " ") ;Used by the new Excel read-in function $lines = StringSplit(StringStripWS($string, 3), $rowDelimiter, 1) $columnsNum = _StringCount($lines[1], $columnDelimiter) + 1 Global $nTotalRows = $lines[0] Global $nTotalCols = $columnsNum Dim $result[$lines[0]][$columnsNum] = [[0]] For $i = 1 To $lines[0] $columns = StringSplit($lines[$i], $columnDelimiter) For $j = 1 To $columns[0] $result[$i - 1][$j - 1] = $columns[$j] Next Next Return $result EndFunc ;==>String2DSplit Func _StringCount($string, $substring) ;Used by the new Excel read-in function Local $i, $count = 0 For $i = 1 To StringLen($string) If StringMid($string, $i, StringLen($substring)) = $substring Then $count = $count + 1 Next Return $count EndFunc ;==>_StringCount Func ExcelSheetToClip($excel, $sheet) ;Used by the new Excel read-in function _ExcelSheetActivate($excel, $sheet) ClipPut("") Send("^{HOME}^a^c") Do Sleep(100) Until ClipGet() Return ClipGet() EndFunc ;==>ExcelSheetToClip The error is only occuring when a cell contains more than 256 characters, but runs fine if I remove the code I added. My guess is that it doesn't play well with the _ExcelReadCell function, but I only started scripting a few months ago, so I've made a fair share of mistakes. At this point, I can at least create a trigger variable that will let me know when this situation occurs, allowing me to skip the update Excel function and prevent the script from just stopping completely. It doesn't solve the problem, but it will keep the script running til I can find a better solution. I appreciate your help. I'd like to find out why the script is failing so I can avoid future conflicts. Edited June 28, 2012 by AmbiguousJoe 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