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-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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

    • TheDcoder
      By TheDcoder
      Hello Guys! I wanted to share all my knowledge on arrays! Hope may enjoy the article , Lets start!
      Declaring arrays!
      Declaring arrays is a little different than other variables: 
      ; Rules to follow while declaring arrays: ; ; Rule #1: You must have a declarative keyword like Dim/Global/Local before the declaration unless the array is assigned a value from a functions return (Ex: StringSplit) ; Rule #2: You must declare the number of dimensions but not necessarily the size of the dimension if you are gonna assign the values at the time of declaration. #include <Array.au3> Local $aEmptyArray[0] ; Creates an Array with 0 elements (aka an Empty Array). Local $aArrayWithData[1] = ["Data"] _ArrayDisplay($aEmptyArray) _ArrayDisplay($aArrayWithData) That's it
      Resizing Arrays
      Its easy! Just like declaring an empty array! ReDim is our friend here:
      #include <Array.au3> Local $aArrayWithData[1] = ["Data1"] ReDim $aArrayWithData[2] ; Change the number of elements in the array, I have added an extra element! $aArrayWithData[1] = "Data2" _ArrayDisplay($aArrayWithData) Just make sure that you don't use ReDim too often (especially don't use it in loops!), it can slow down you program.
      Best practice of using "Enum"
      You might be wondering what they might be... Do you know the Const keyword which you use after Global/Local keyword? Global/Local are declarative keywords which are used to declare variables, of course, you would know that already by now , If you check the documentation for Global/Local there is a optional parameter called Const which willl allow you to "create a constant rather than a variable"... Enum is similar to Const, it declares Integers (ONLY Integers):
      Global Enum $ZERO, $ONE, $TWO, $THREE, $FOUR, $FIVE, $SIX, $SEVEN, $EIGHT, $NINE ; And so on... ; $ZERO will evaluate to 0 ; $ONE will evaluate to 1 ; You get the idea :P ; Enum is very useful to declare Constants each containing a number (starting from 0) This script will demonstrate the usefulness and neatness of Enums :
      ; We will create an array which will contain details of the OS Global Enum $ARCH, $TYPE, $LANG, $VERSION, $BUILD, $SERVICE_PACK Global $aOS[6] = [@OSArch, @OSType, @OSLang, @OSVersion, @OSBuild, @OSServicePack] ; Now, if you want to access anything related to the OS, you would do this: ConsoleWrite(@CRLF) ConsoleWrite('+>' & "Architecture: " & $aOS[$ARCH] & @CRLF) ConsoleWrite('+>' & "Type: " & $aOS[$TYPE] & @CRLF) ConsoleWrite('+>' & "Langauge: " & $aOS[$LANG] & @CRLF) ConsoleWrite('+>' & "Version: " & $aOS[$VERSION] & @CRLF) ConsoleWrite('+>' & "Build: " & $aOS[$BUILD] & @CRLF) ConsoleWrite('+>' & "Service Pack: " & $aOS[$SERVICE_PACK] & @CRLF) ConsoleWrite(@CRLF) ; Isn't it cool? XD You can use this in your UDF(s) or Program(s), it will look very neat!
      Looping through an Array
      Looping through an array is very easy! . There are 2 ways to loop an array in AutoIt!
      Simple Way:
      ; This is a very basic way to loop through an array ; In this way we use a For...In...Next Loop! Global $aArray[2] = ["Foo", "Bar"] ; Create an array ; This loop will loop 2 times because our $aArray contains 2 elements. For $vElement In $aArray ; $vElement will contain the value of the elements in the $aArray... one element at a time. ConsoleWrite($vElement & @CRLF) ; Prints the element out to the console Next ; And that's it! Advanced Way:
      ; This is an advanced way to loop through an array ; In this way we use a For...To...Next Loop! Global $aArray[4] = ["Foo", "Bar", "Baz", "Quack"] ; Create an array ; This loop will loop 2 times because our $aArray contains 2 elements. For $i = 0 To UBound($aArray) - 1 ; $i is automatically created and is set to zero, UBound($aArray) returns the no. of elements in the $aArray. ConsoleWrite($aArray[$i] & @CRLF) ; Prints the element out to the console. Next ; This is the advanced way, we use $i to access the elements! ; With the advanced method you can also use the Step keyword to increase the offset in each "step" of the loop: ; This will only print every 2nd element starting from 0 ConsoleWrite(@CRLF & "Every 2nd element: " & @CRLF) For $i = 0 To UBound($aArray) - 1 Step 2 ConsoleWrite($aArray[$i] & @CRLF) Next ; This will print the elements in reverse order! ConsoleWrite(@CRLF & "In reverse: " & @CRLF) For $i = UBound($aArray) - 1 To 0 Step -1 ConsoleWrite($aArray[$i] & @CRLF) Next ; And that ends this section! For some reason, many people use the advance way more than the simple way . For more examples of loops see this post by @FrancescoDiMuro!
      Interpreting Multi-Dimensional Arrays
      Yeah, its the most brain squeezing problem for newbies, Imagining an 3D Array... I will explain it in a very simple way for ya, so stop straining you brain now! . This way will work for any array regardless of its dimensions...
      Ok, Lets start... You can imagine an array as a (data) mine of information:

      ; Note that: ; Dimension = Level (except the ground level :P) ; Element in a Dimension = Path ; Level 2 ----------\ ; Level 1 -------\ | ; Level 0 ----\ | | ; v v v Local $aArray[2][2][2] ; \-----/ ; | ; v ; Ground Level ; As you can see that $aArray is the Ground Level ; All the elements start after the ground level, i.e from level 0 ; Level 0 Contains 2 different paths ; Level 1 Contains 4 different paths ; Level 2 Contains 8 different paths ; When you want too fill some data in the data mine, ; You can do that like this: $aArray[0][0][0] = 1 $aArray[0][0][1] = 2 $aArray[0][1][0] = 3 $aArray[0][1][1] = 4 $aArray[1][0][0] = 5 $aArray[1][0][1] = 6 $aArray[1][1][0] = 7 $aArray[1][1][1] = 8 ; Don't get confused with the 0s & 1s, Its just tracing the path! ; Try to trace the path of a number with the help of the image! Its super easy! :D I hope you might have understand how an array looks, Mapping your way through is the key in Multi-Dimensional arrays, You take the help of notepad if you want! Don't be shy!
      Frequently Asked Questions (FAQs) & Their answers
      Q #1. What are Arrays?
      A. An Array is an datatype of an variable (AutoIt has many datatypes of variables like "strings", "integers" etc. Array is one of them). An Array can store information in a orderly manner. An Array consist of elements, each element can be considered as a variable (and yes, each element has its own datatype!). AutoIt can handle 16,777,216 elements in an Array, If you have an Array with 16,777,217 elements then AutoIt crashes.
      Q #2. Help! I get an error while declaring an Array!?
      A. You tried to declare an array like this:
      $aArray[1] = ["Data"] That is not the right way, Array is a special datatype, since its elements can be considered as individual variables you must have an declarative keyword like Dim/Global/Local before the declaration, So this would work:
      Local $aArray[1] = ["Data"] Q #3. How can I calculate the no. of elements in an array?
      A. The UBound function is your answer, Its what exactly does! If you have an multi-dimensional Array you can calculate the total no. of elements in that dimension by specifying the dimension in the second parameter of UBound
      Q #4. Why is my For...Next loop throwing an error while processing an Array?
      A.  You might have done something like this:
      #include <MsgBoxConstants.au3> Local $aArray[10] = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] Local $iMyNumber = 0 For $i = 0 To UBound($aArray) ; Concentrate here! $iMyNumber += $aArray[$i] Next MsgBox($MB_OK, "Sum of all Numbers!", $iMyNumber) Did you notice the mistake? UBound returns the no. of elements in an array with the index starting from 1! That's right, you need to remove 1 from the total no. of elements in order to process the array because the index of an array starts with 0! So append a simple - 1 to the statment:
      #include <MsgBoxConstants.au3> Local $aArray[10] = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] Local $iMyNumber = 0 For $i = 0 To UBound($aArray) - 1 $iMyNumber += $aArray[$i] Next MsgBox($MB_OK, "Sum of all Numbers!", $iMyNumber) Q #5. Can an Array contain an Array? How do I access an Array within an Array?
      A. Yes! It is possible that an Array can contain another Array! Here is an example of an Array within an Array:
      ; An Array can contain another Array in one of its elements ; Let me show you an example of what I mean ;) #include <Array.au3> Global $aArray[2] $aArray[0] = "Foo" Global $aChildArray[1] = ["Bar"] $aArray[1] = $aChildArray _ArrayDisplay($aArray) ; Did you see that!? The 2nd element is an {Array} :O ; But how do we access it??? ; You almost guessed it, like this: ; Just envolope the element which contains the {Array} (as shown in _ArrayDisplay) with brackets (or parentheses)! :D ConsoleWrite(($aArray[1])[0]) ; NOTE the brackets () around $aArray[1]!!! They are required or you would get an syntax error! ; So this: $aArray[1][0] wont work!  
      More FAQs coming soon!
    • robertocm
      By robertocm
      I'm using this for replacing text strings in the VBProject of all excel files in a folder and subfolders.
      I have the same text string in several lines and those lines could have some differences between files: then not feasible for .ReplaceLine method
      I'm not interested in placing all the code in a xla AddIn, because the code is similar but not exactly the same in all the files.
       
      Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase #include <File.au3> #include <WinAPIFiles.au3> #include <Excel.au3> $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. Global Const $sMessage = "Select Folder" Global $sFileSelectFolder = FileSelectFolder($sMessage, "") If @error Then MsgBox(0, "", "No folder was selected.") Exit EndIf Global $bFileOpen ;Look for excel files in selected directory and all subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then Local $oAppl = _Excel_Open(Default, Default, False, Default, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) For $i = 1 To $aFileList[0] $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then ;ShellExecute($aFileList[$i]) Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i]) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oProject = $oWorkbook.VBProject ;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html ;2.7.2 Macromodule delete With $oProject .VBComponents.Remove(.VBComponents("SplashText")) If $iEventError Then Consolewrite("SplashText Form not found: " & $aFileList[$i] & @CRLF) $iEventError = 0 ; Reset after displaying a COM Error occurred EndIf .VBComponents.Import("C:\Documents and Settings\XP\Escritorio\PLANTILLAS_EXPORT\SplashText.frm") EndWith ;3.2.1.8 Macro: delete With $oProject.VBComponents("Actual").CodeModule ;3.2.1.2 Macro: find If .Find("Sub Check_NumPed(", 1, 1, -1, -1) Then ;Note: using '+ 1' at the end of the line because i'm used to add an empty line between procedures (see vba help for ProcCountLines) .DeleteLines( .ProcStartLine("Check_NumPed", 0), .ProcCountLines("Check_NumPed", 0) + 1) EndIf EndWith ;Check if range name exists. If not create named ranges If Not IsObj($oWorkbook.Sheets("DATOS").Evaluate("Booking_DestPort")) Then ;If Not IsObj($oWorkbook.Sheets("DATOS").Range("Booking_DestPort")) Then If $oWorkbook.Sheets("DATOS").Range("AC7").value = "DestPort" Then $oWorkbook.Names.Add("Booking_DestPort", "=DATOS!$AC$8") Else ConsoleWrite("-> Not: 'DestPort' in AC7" & @TAB & $aFileList[$i] & @CRLF) EndIf If $oWorkbook.Sheets("DATOS").Range("AD7").value = "FinalDest" Then $oWorkbook.Names.Add("Booking_FinalDest", "=DATOS!$AD$8") Else ConsoleWrite("-> Not: 'FinalDest' in AD7" & @TAB & $aFileList[$i] & @CRLF) EndIf EndIf ;Open VBE Editor (like Alt+F11) $oAppl.VBE.MainWindow.Visible = True ;$oAppl.VBE.Windows("Inmediato").Visible = True ;https://www.autoitscript.com/forum/topic/77545-resolved-vbaofficeexcel-experts/ ;Spiff59, Aug 2008 ;Local $oModules = $oProject.VBComponents ;Local $oModules = $oWorkbook.VBProject.VBComponents ;$oModules.Item(1).CodeModule.CodePane.Show ;$oModules.Item(1).Activate ; With $oModules.Item($y).CodeModule ; .ReplaceLine (1 , "Sub SpellCheck()") ; .DeleteLines (10, 1) ; .InsertLines (7 , "TEST") ; EndWith ;Wait 30 seconds for the window to appear. Local $hWnd = WinWait("Microsoft Visual Basic - ", "Proyecto - VBAProjec", 30) WinActivate($hWnd) WinWaitActive($hWnd, "", 30) If WinActive($hWnd, "") Then ;Sleep(100) ;Send("{F7}") $oProject.VBComponents("Actual").Activate ;Wait 30 seconds for the window to appear. Local $hWnd2 = WinWait(" - [Actual (Código)]", "Proyecto - VBAProject", 30) WinActivate($hWnd2) WinWaitActive($hWnd2, "", 30) ;First Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AC8")') Sleep(200) Send("{TAB}") Sleep(100) ;Send("{DEL}") Send('Range("Booking_DestPort")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 1: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Second Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AD8")') Sleep(200) Send("{TAB}") Sleep(100) Send('Range("Booking_FinalDest")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 2: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Close VBE Editor If WinActive($hWnd2, "") Then $oAppl.VBE.ActiveWindow.Close ;Send("^{F4}") ;Sleep(100) ;Send("^s") $oAppl.VBE.MainWindow.Visible = False ;Sleep(100) ;Send("!{F4}") EndIf EndIf _Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf _Excel_Close($oAppl) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;This is a custom error handler Func ErrFunc() $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc  
    • jasty
      By jasty
      In every other language doing   
      dim $a[3] = [1, 2, 3] $b = $a creates a reference to a but in autoit it always makes a copy.  I didn't realize this until I have performance problems and now I'm trying to switch over to the reference behavior.  How can I change the above statement so b is a reference to a and not a full copy? 
    • gahhon
      By gahhon
      Hi Guys,
      I was trying to read some data from the excel file and without opening the file. But I tried a lot of methods, it still open the file.
      And also, I am able to capture the ColumnA value but not Column B.
      Thanks for advance information.
      Global $oDataA, $oDataB Call ("ExcelRead", "B2", "C2") Func ExcelRead($oColumnA, $oColumnB) Local $oPath = @ScriptDir & "\MyFile.xlsx" Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, 1, 0) $oDataA = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnA) $oDataB = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnB) MsgBox(0, "Test Value", $oDataA & ", " & $oDataB) EndFunc  
    • ed973
      By ed973
      I really don't understand how to save as an open excel sheet.
      I run a script that at the end open the excel: just only need to save as the opened excel on my desktop (and overwrite it everytime).
      I'm trying to use .ActiveWorkBook.SaveAs("C:\Users\Enrico\Desktop\impegnato.xlsx") but...
       
      Thanks in advance for helping.
×