Excel and arrays

[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

expandcollapse popup

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

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

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

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

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

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

[water]

Another question:

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

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

[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?

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

[Clark]

Excellent!  It works.

Thank you.

[water]

Glad the problem could be solved.

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