Jump to content

Recommended Posts

Rskm

hi, i am using commands like the below to print values to excel, the array has 20 values, do i have to manually type as a1, b1, c1, d1.... till t1 or is there a short cut(loop) by which i can get the 20 elements printed in a particular row(here row 1) of excel?

_Excel_RangeWrite($oWorkbook, 1, $array[1], "a1")

_Excel_RangeWrite($oWorkbook, 1, $array[2], "b1")

_Excel_RangeWrite($oWorkbook, 1, $array[3], "c1")

 

thanks

Share this post


Link to post
Share on other sites
JLogan3o13

Loop through it with a for loop, like so:

#include <Array.au3>
#include <Excel.au3>

Local $aArray[20] = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'January', 'February', 'March', 'April', _
                     'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', '2017', '2018']


Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

For $a = 0 To UBound($aArray) - 1
    _Excel_RangeWrite($oWorkbook, 1, $aArray[$a], "A" & $a)
Next

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Rskm
3 minutes ago, JLogan3o13 said:

Loop through it with a for loop, like so:

#include <Array.au3>
#include <Excel.au3>

Local $aArray[20] = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'January', 'February', 'March', 'April', _
                     'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', '2017', '2018']


Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

For $a = 0 To UBound($aArray) - 1
    _Excel_RangeWrite($oWorkbook, 1, $aArray[$a], "A" & $a)
Next

 

Hello JLogan,  thanks for the initiative, the above example you mentioned prints output in column A, ie; in cells A1, A2, A3 till A20... what i need is to print the 20 elements in rows ie; A1, B1, C1 .... T1

Share this post


Link to post
Share on other sites
JLogan3o13

@Rskm look through the help file, it is your friend in these situations. There is a function both for converting column letters to numbers and numbers to letters. Pretty easy to come up with a quick conversion that will suit your needs.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Bilgus

Just something simple to exemplify..

#include <Excel.au3>


ColRange("a", "qq")
ColRange("ZZ", "A", 5)

Func ColRange($sStart, $sEnd, $iStep = 1);iStep --Increment by this many columns -- defaults to 1
    Local $iSt = _Excel_ColumnToNumber ( $sStart )
    Local $iEnd = _Excel_ColumnToNumber ( $sEnd )

    If $iSt > $iEnd Then $iStep = -$iStep ;Need to step backwards

    For $i = $iSt To $iEnd Step $iStep
        consoleWrite(_Excel_ColumnToLetter ( $i ) & ", ") ; Convert the numbers back to letters and append ', '
    Next

    consoleWrite(@CRLF) ;Newline
EndFunc

 

Share this post


Link to post
Share on other sites
JLogan3o13

Or you can wait for someone to do it for you ;)

  • Like 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Rskm

_Excel_ColumnToLetter ( $iColumn ) - this was good enough.. 

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

  • Similar Content

    • JonnyQuy
      By JonnyQuy
      I need to save the text with excel, each save is 1 row and 2 columns, i did and it does not work please help me Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookNew($oExcel) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf For $i = 1 To 10 Local $aArray2D[1][2] = [[$i],[2222]] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, "A"&$i&"") Next error_excel.bmp
    • Paranthaman
      By Paranthaman
      Hi Everyone,
      I am a beginner and I am currently learning and practicing what Autoit can do, so kindly pardon if it sound's silly.
      What my program does ----> I had written a program where I have a FOR (i=0 to n) loop which is running for n times. Inside the FOR loop, contents of array is written into excel using _Excel_RangeWrite .
      _Excel_RangeWrite($oExcelDoc, $oExcelDoc.Activesheet, $arrayname, "A1") Problem ------> During every loop run the contents of column A is only altered
      What i intend to do ------> For every loop run (i=0,1,2,3...) I want to write the array contents into respective next adjacent excel columns
      i.e) For i=0 loop, every array content should be written in A Column of excel
      For i = 1 loop, every array content should be written in B Column of excel.
       
      Can anyone give me an idea of  how can i do this? Thanks 
    • SorryButImaNewbie
      By SorryButImaNewbie
      Hello dear forum user!
      I try to make an algorithm that reads values from 2 predefined columns until it founds 2 empty cells in that column, if that happens, It should write a SUM function in to the cell after the last read value. It should do this until it founds 3 empty cells. (I order my excel before this part, so these criteriums should work, and they do while I'm testing).
      Now, I just found a little logical bump on the $SUMsor variable, but I corrected it. Since MsgBox shows me that the readings are good, and the string it should write are also good, and no error flags are detected, I'm not sure what to do next.
      I'm pretty sure it is attached to the right excel, since every other things works, and it also reads in from the excel It should.
      Here is my code snippet:
      ;CONSTRUCTION----------------------------------- ;TO DO: ;Range insert to SULY and HUF, put it in Bold $CellaOlvasoSzamlalo = $CellNumb + 1 Do Local $SUMSor = $CellNumb + 1 ;will be used to determine the "top of the area which it should SUM, CellNumb is the row where the headers/titels were found Local $SULYolvaso1 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo) ;Read MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso1:" & $SULYolvaso1 & "") ;Check what is read Local $SULYolvaso2 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo + 1) MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso2:" & $SULYolvaso2 & "") Local $SULYolvaso3 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo + 2) MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso3:" & $SULYolvaso3 & "") Select Case $SULYolvaso2 = "" and $SULYolvaso3 = "" _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=SUM(" & $SULYoszlop & $SUMSor & ":" & $SULYoszlop & $CellaOlvasoSzamlalo & ")") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Értesítés", "Suly oszlop: " & "=SUM(" & $SULYoszlop & $SUMSor & ":" & $SULYoszlop & $CellaOlvasoSzamlalo & ")") _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=SUM(" & $sHUFBeszurOszlop & $SUMSor & ":" & $sHUFBeszurOszlop & $CellaOlvasoSzamlalo & ")") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Értesítés", "Suly oszlop: " & "=SUM(" & $sHUFBeszurOszlop & $SUMSor & ":" & $sHUFBeszurOszlop & $CellaOlvasoSzamlalo & ")") $SUMSor = $CellaOlvasoSzamlalo + 3 EndSelect $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1 Until $SULYolvaso1 = "" and $SULYolvaso2 = "" and $SULYolvaso3 = "" ;$ExcelObject.Activesheet.Range(String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo).Font.Bold = True ;----------------------------------------------- Also I would like to write it in bold, I have en example commented out at the end, but is there a way with range insert to insert in Bold?
      Thank you for your help and insight! and have a nice day
    • SorryButImaNewbie
      By SorryButImaNewbie
      Hello
      First, I thank you for all the help I received here with Excel UDFs and COM objects in the last few days/week.
      I would like to "pimp" my output excel a bit, and for that I would like to write some results Bold.
      How do I do that?
      I tried a few things but most return errors, or doesn't do a thing seemingly.
      My code right now, this does run, but doesn't make the inserted results bold. (by inserted I mean what I write with _Excel_RangeWrite)
      _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$DateArrayTimeIndex]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo) $ExcelObject.Activesheet.Range(String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo).Bold = True Thank you for the help!
      Edit: I leave this here, added the bold tag, maybe someone find it usefull
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.