Jump to content

Copy content to Excel from Array Display


Recommended Posts

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

To copy the content of an array to Excel try _ExcelReadArray.

Edited 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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

  • 2 months later...

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 by AmbiguousJoe
Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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).

;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 by AmbiguousJoe
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...