Jump to content
Sign in to follow this  
Clark

Excel and arrays

Recommended Posts

Clark

I am having trouble understanding why the following code does not work.

THe way it is now, with this line commented out...

   ;$iRval = _EzMySql_GetTable2d($sSQL

...it works fine and populates the excel spreadsheet with the heading rows "System Impact" etc.

However, if I uncomment out the line I mention above, nothing goes to the Excel spreadsheet.

Where I have the messagebox reporting the value of Ubound on the array (for debugging purposes) it returns the correct value, i.e. the number of rows the sql has returned.

I have also included a screen snap of the array display, showing that the array does indeed contain data.

Here is the function

Func _Excel_Xport()
   local $cntr,$wf,$tempresult,$iSval,$iRval[10][10]
   local $sFilePath = @TempDir & "\Temp.xls"
   local $oExcel = _Excel_Open()
   If Not _FileCreate($sFilePath) Then
      MsgBox(4096, "Error", " Error Creating File - " & @error & " - " & @extended)
      return 1
   EndIf
   $oExcel = _Excel_BookOpen($oExcel,$sFilePath, Default, Default, True)
   if $oExcel = 0 Then
      MsgBox(0,"Error",@error)
      Return
   EndIf
   $oExcel = _Excel_BookAttach("Microsoft Excel - Temp.xls","Title")
   if $oExcel = 0 Then
      MsgBox(0,"Error","Unable to attach to spreadsheet")
      Return
   EndIf
   for $cntr = 1 to 8
      if GUICtrlRead($r_[$cntr]) = 1 Then
         Switch $cntr
            Case 1
               $wf="%"
            Case 2
               $wf="%5"
            Case 3
               $wf="1_0"
            Case 4
               $wf=20
            Case 5
               $wf=30
            Case 6
               $wf=40
            Case 7
               $wf=50
            Case 8
               $wf=3
         EndSwitch
         ExitLoop
      EndIf
   next
   $sSQL="SELECT * FROM rfc_manager.tbl_RFC WHERE RFC_Workflow_Status LIKE """ & $wf & """ AND RFC_Workflow_Status <> ""9999"";"
   ;$iRval = _EzMySql_GetTable2d($sSQL)
   $error = @error
   msgbox(0,"test",Ubound($iRval))
   $iRval[0][0]="Test"
   if uBound($iRval)=1 Then MsgBox(0, $sSQL & " error", $error)
   ReDim $iRval[UBound($iRval)][26]                             ; Add a few columns to the array
   $iRval[0][13]="System Impact"
   $iRval[0][14]="Tech Est Hours"
   $iRval[0][15]="Tech Quoted Hours"
   $iRval[0][16]="Func Est Hours"
   $iRval[0][17]="Func Quoted Hours"
   $iRval[0][18]="Admin Est Hours"
   $iRval[0][19]="Admin Quoted Hours"
   $iRval[0][20]="Payment Method"
   $iRval[0][21]="PO Number"
   $iRval[0][22]="CAB Priority"
   $iRval[0][23]="CAB Notes"
   $iRval[0][24]="Vendor Notes"
   $iRval[0][25]="Reason Notes"
   _ArrayDisplay($iRval)
   $iSval = _Excel_RangeWrite($oExcel,$oExcel.Activesheet, $iRval)
   if $iSval = 0 Then msgbox(0,"Error",@error)
EndFunc

And here is the array displayed

<a href="http://s111.photobucket.com/user/wongdai/media/array_display_zps9832c577.jpg.html" target="_blank"><img src="'>" border="0" alt=" photo array_display_zps9832c577.jpg"/></a>

I hope I have explained this clearly enough

Share this post


Link to post
Share on other sites
Jfish

Maybe because you are re-diming the $iRival var after the _EzMySql_GetTable2d($sSQL).   That may be erasing the array.  Try renaming the variable for that function or moving it down below _arrayDisplay().  See if that helps ...


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites
Clark

Thanks Jfish

I just tried removing the redim and the consequent statements that addressed the now out of bounds array values but this had no effect on the problem.

I'm pretty sure redim preserves the values in an array anyway.

Share this post


Link to post
Share on other sites
Bert

What happens when you try to display the array in a separate GUI? What I mean by that is rename the variable then use _arraydisplay to see if the array is available. If not then you got part of the problem figured out.

(Note: _ArrayDisplay will only show a 1D or 2D array in a list view)

Share this post


Link to post
Share on other sites
Clark

Thanks for your reply DarthCookieMonster

I'm not really understanding what you are suggesting.

When you say "rename the variable", do you mean copy the $iRval array to a new array variable and then try to display that with _arraydisplay?

I apologise in advance if I am being dumb.

Share this post


Link to post
Share on other sites
water

Why do you modify the array to insert header lines?

Create an array with the header lines and write them to Excel.

Then write the array to Excel starting in row 2.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Another question:

Why do you use _Excel_BookAttach after you have opened the Workbook using _Excel-BookOpen?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Another question:

Why do you create the Excel file using FileCreate? I'm not sure this always works as expected.

I would use:

If FileExists($sFilePath) Then
     $oExcel = _Excel_BookOpen($oExcel,$sFilePath, Default, Default, True)
Else
     $oExcel = _Excel_BookNew(...)
EndIf

BTW: I would use meaningful names for your avariables. $oExcel (iat least in my examples) is used for the Excel application. For the Workbook I use $oWorkbook.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Clark

Why do you modify the array to insert header lines?

Create an array with the header lines and write them to Excel.

Then write the array to Excel starting in row 2.

 

Thanks Water

THis is because the initial sql retrieve also brings back column headings, but only for columns 0-12. 

Nevertheless, in the interest of trying to solve this problem, I have removed the array rewrite and cleaned up the Excel code as per your suggestions above.

The sql retrieves are returning 2d arrays as confirmed by ubound, however, when I write the array out to Excel, nothing is appearing other than the headings which I manually insert.

Excel_Rangewrite does not return an error when writing out the retrieved 2d arrays.

Any ideas?

Share this post


Link to post
Share on other sites
water

Don't know why _Excel_RangeWrite doesn't write data to the workbook.

Could you please try this statement? It uses a less limited function to transpose the array:

$iSval = _Excel_RangeWrite($oExcel, $oExcel.Activesheet, $iRval, "A1", Default, True)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Clark

Excellent!  It works.

Thank you.

Share this post


Link to post
Share on other sites
water

Glad the problem could be solved.

It is strange that you do not get an error message with your original code :huh:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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
Sign in to follow this  

  • Similar Content

    • rm4453
      By rm4453
      Hello,
       
      I am currently writing a program that parses a massive table from a website, and need a way to add a progress bar while parsing.
      I am currently using the function _IETableWriteToArray($oObj, True) to parse the array. I need the progress bar to update as the table is parsed, not just at the end of the parsing.
      Any help at all would be very much appreciated!
       
      *EDIT --> The array I am left with after parsing is $array[0-50000][16]
    • nooneclose
      By nooneclose
      My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.
      This is what I have so far.
      Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill") _ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") _Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)  
      Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 
    • nooneclose
      By nooneclose
      I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 
      I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel
      I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.
      $OpenRange      = "A1:E200" $xlSum          = -4157 $Added_Array[2] = [2, 3] $OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True) I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.
    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
×