Sign in to follow this  
Followers 0
Clark

Excel and arrays

12 posts in this topic

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



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

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

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

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

Excellent!  It works.

Thank you.

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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  
Followers 0

  • Similar Content

    • Eli_jahbot
      By Eli_jahbot
      My esteemed Autoits I need your help once again.
      I'm trying to figure out how to create a loop that gets 1 value from an array and repeats until each value from the array has been used. I have never used arrays before and I know once I learn more things should get easier for me. 
      Here is what Im trying to do:
      -Have an array of values that determine what application i log into. ex: app1, app2, app3, app4 etc.
      -Have a loop that repeats a process sequentially using each value in the array to finish the process for each app1, app2, app 3 and so forth. I have 30 apps that I need to update on a regular basis and getting this sorted out is what I perceive to be the best way to do it.
      Here is my feeble attempt that obviously fails:
      #include <msgboxconstants.au3>
      #include <Constants.au3>
      #include <array.au3>
      Login()
      Func Login()
          local $array[30] = ["10", "11", "12",etc etc]
          ;;Local $site = InputBox("ERx Site","What site do you want to login as?","","")
          Local $userid = InputBox("ERx Login", "What is your username?", "", "")
          Local $Passwd = InputBox("Security Check", "Enter your UAT password.", "", "*")
      for $1 = 1 to 30(I need to do the same steps in 30 different apps)
      run("Z:launch.exe")
      WinWaitActive("Input")
      Send (Sequential ARRAY VALUE HERE)
      Send("{ENTER}")
      WinWaitActive("window")
      Send($userid)
      Send("{TAB}")
      send($Passwd)
      Send("{ENTER}")
      WinWaitActive("[CLASS:SunAwtDialog]")
      Sleep(500)
      WinClose("Home Page")
      Next
      EndFunc
       
      your help is greatly appreciated.
      Thanks for your time
    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd