Sign in to follow this  
Followers 0
Daferra

Use first excel row to compute the array

16 posts in this topic

Hey Guys,

Can someone help me with this, i just cant figure out where to start!

In a gui i would like to have a button that kan load a excel sheet.

When that is done, there needs to be a listview or combo that has the the value of the first row A1, B1,C1 etc.

the value needs to be selected for the next function.

When the button "do" is used, the script will compute the array of the value that where under the selected row field.

So when in the GUI B1 was selected, the button "do" has the array with value B2,B3,B4,B5 etc.

Is this making any sens!

Greetz

Ferdi

Share this post


Link to post
Share on other sites



$oExcel.WorkBooks.Open($FileName) That line opens an excel workbook.

Then you need to do:

Sheets("Sheet1").Select

Columns("B").Select

Selection.Copy

After that i didnt get what exactly you want to do...

Share this post


Link to post
Share on other sites

Ok guys,

Thanks for the directions, this is what i have so far

Kan someone help me with the next stage:

After i load the combobox with the values from a excel sheet A3,A4,A4 etc

I choice the value A3

push Do

read the values that are under A3 (B3,C3,D3 etc)

And make for every value af file cald that same value (B3.txt B4.txt) with the text that was typed into the $as_body

CODE
#include <Excel.au3>

#include <array.au3>

#include <GUIConstants.au3>

#include <GuiScrollBars.au3>

#include <file.au3>

#include <misc.au3>

#include <GUIConstantsEx.au3>

#include <WindowsConstants.au3>

#include <ScrollBarConstants.au3>

#include <EditConstants.au3>

Global $oExcel, $aArray1

#cs - Niet meer nodig, worden nu gebruikt in gui buttons.

$iFile = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)

$oExcel = _ExcelBookOpen($iFile)

$XLArray = _ExcelReadArray($oExcel, 2, 6, 100, 1)

_ExcelBookClose($oExcel)

#ce

#Region ### START Koda GUI section ### Form=F:\Documents and Settings\fvissers\Desktop\koda_1.7.0.1\Forms\dailer.kxf

$Form1 = GUICreate("LoadaArrayandDo", 310, 400, 359, 309)

$as_Body = GUICtrlCreateEdit("", 5, 20, 300, 100, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_WANTRETURN))

$numchar = GUICtrlCreateLabel("Characters Left: 160", 5, 130, 100, 20)

$Button1 = GUICtrlCreateButton("Load Excel", 5, 150, 70, 17, 0)

$GUIccl = GUICtrlCreateCombo("", 5, 172, 201, 137) ; Renamed variable name since it used to have the same name as the array variable

$Do = GUICtrlCreateButton("Do", 240, 172, 70, 17, 0)

;$Button4 = GUICtrlCreateButton("Propagate List", 32, 384, 105, 33, 0) ; Renamed button 'coz I was getting confused

GUISetState(@SW_SHOW)

#EndRegion ### END Koda GUI section ###

While 15

;Sleep(10)

;$msg = GUIGetMsg()

;$readtxt = GUICtrlRead($as_Body)

;$length = StringLen($readtxt)

;$actualchar = Execute(160 - $length)

;GUICtrlSetData($numchar, "Characters Left: " & $actualchar)

$nMsg = GUIGetMsg()

Switch $nMsg

Case $GUI_EVENT_CLOSE

Exit

Case $Button1

$iFile = FileOpenDialog("Load Forms", @ScriptDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)

_read()

; Hierna zal de lijst gevuld worden

$list = ""

For $x = 0 To UBound($aArray1) -1

If $x == UBound($aArray1) -1 Then

$list &= $aArray1[$x]

Else

$list &= $aArray1[$x] & "|"

EndIf

Next

GUICtrlSetData($GUIccl, $list)

Case $Do

EndSwitch

WEnd

Func _read()

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray1 = _ExcelReadArray($oExcel1, 1, 3, 5)

_ExcelBookClose($oExcel1)

EndFunc

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Ok guys,

Thanks for the directions, this is what i have so far

Kan someone help me with the next stage:

After i load the combobox with the values from a excel sheet A3,A4,A4 etc

I choice the value A3

push Do

read the values that are under A3 (B3,C3,D3 etc)

And make for every value af file cald that same value (B3.txt B4.txt) with the text that was typed into the $as_body

CODE
#include <Excel.au3>

#include <array.au3>

#include <GUIConstants.au3>

#include <GuiScrollBars.au3>

#include <file.au3>

#include <misc.au3>

#include <GUIConstantsEx.au3>

#include <WindowsConstants.au3>

#include <ScrollBarConstants.au3>

#include <EditConstants.au3>

Global $oExcel, $aArray1

#cs - Niet meer nodig, worden nu gebruikt in gui buttons.

$iFile = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)

$oExcel = _ExcelBookOpen($iFile)

$XLArray = _ExcelReadArray($oExcel, 2, 6, 100, 1)

_ExcelBookClose($oExcel)

#ce

#Region ### START Koda GUI section ### Form=F:\Documents and Settings\fvissers\Desktop\koda_1.7.0.1\Forms\dailer.kxf

$Form1 = GUICreate("LoadaArrayandDo", 310, 400, 359, 309)

$as_Body = GUICtrlCreateEdit("", 5, 20, 300, 100, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_WANTRETURN))

$numchar = GUICtrlCreateLabel("Characters Left: 160", 5, 130, 100, 20)

$Button1 = GUICtrlCreateButton("Load Excel", 5, 150, 70, 17, 0)

$GUIccl = GUICtrlCreateCombo("", 5, 172, 201, 137) ; Renamed variable name since it used to have the same name as the array variable

$Do = GUICtrlCreateButton("Do", 240, 172, 70, 17, 0)

;$Button4 = GUICtrlCreateButton("Propagate List", 32, 384, 105, 33, 0) ; Renamed button 'coz I was getting confused

GUISetState(@SW_SHOW)

#EndRegion ### END Koda GUI section ###

While 15

;Sleep(10)

;$msg = GUIGetMsg()

;$readtxt = GUICtrlRead($as_Body)

;$length = StringLen($readtxt)

;$actualchar = Execute(160 - $length)

;GUICtrlSetData($numchar, "Characters Left: " & $actualchar)

$nMsg = GUIGetMsg()

Switch $nMsg

Case $GUI_EVENT_CLOSE

Exit

Case $Button1

$iFile = FileOpenDialog("Load Forms", @ScriptDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)

_read()

; Hierna zal de lijst gevuld worden

$list = ""

For $x = 0 To UBound($aArray1) -1

If $x == UBound($aArray1) -1 Then

$list &= $aArray1[$x]

Else

$list &= $aArray1[$x] & "|"

EndIf

Next

GUICtrlSetData($GUIccl, $list)

Case $Do

EndSwitch

WEnd

Func _read()

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray1 = _ExcelReadArray($oExcel1, 1, 3, 5)

_ExcelBookClose($oExcel1)

EndFunc

GuiCtrlRead() to get the values selected in the Combo and Edit controls.

Determine the row you want (i.e. 3).

_ExcelReadArray() again to read the row. Note that this function can read either a row or a column based on the $direction parameter (see help file).

FileWrite() to write the files.

All basic stuff in the help file.

:)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

GuiCtrlRead() to get the values selected in the Combo and Edit controls.

Determine the row you want (i.e. 3).

_ExcelReadArray() again to read the row. Note that this function can read either a row or a column based on the $direction parameter (see help file).

FileWrite() to write the files.

All basic stuff in the help file.

:)

Hi PsaltyDS,

I've read and read but cannot find how to...

I know how to read the value from the combo box, but how to use that to know where i need to read the array value.

When I read the cells A1,B1,C1 etc and in cell B1 I have the value "list 03"

How to use the value "list 03" to now that I want the values of B2,B3,B4 etc

regards,

Ferdi

Share this post


Link to post
Share on other sites

Hi PsaltyDS,

I've read and read but cannot find how to...

I know how to read the value from the combo box, but how to use that to know where i need to read the array value.

When I read the cells A1,B1,C1 etc and in cell B1 I have the value "list 03"

How to use the value "list 03" to now that I want the values of B2,B3,B4 etc

regards,

Ferdi

You have the values of that column in an array already. So you just _ArraySearch() to find out which element it is, which tells you which row to read with the second _ExcelReadArray().

Start coding it and post what you've got if you get stuck.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Hey,

never new that i already have the value :)

I think the code is as bugy as Hell, bur this part is working now.

Can i use a loop to do something with all the array value's ?

CODE
#include <Excel.au3>

#include <array.au3>

#include <GUIConstants.au3>

#include <GuiScrollBars.au3>

#include <file.au3>

#include <misc.au3>

#include <GUIConstantsEx.au3>

#include <WindowsConstants.au3>

#include <ScrollBarConstants.au3>

#include <EditConstants.au3>

Global $oExcel, $aArray1

#cs - Niet meer nodig, worden nu gebruikt in gui buttons.

$iFile = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)

$oExcel = _ExcelBookOpen($iFile)

$XLArray = _ExcelReadArray($oExcel, 2, 6, 100, 1)

_ExcelBookClose($oExcel)

#ce

#Region ### START Koda GUI section ### Form=F:\Documents and Settings\fvissers\Desktop\koda_1.7.0.1\Forms\dailer.kxf

$Form1 = GUICreate("LoadaArrayandDo", 310, 400, 359, 309)

$menu1 = GUICtrlCreateMenu("File")

$as_Body = GUICtrlCreateEdit("", 5, 20, 300, 100, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_WANTRETURN))

$numchar = GUICtrlCreateLabel("Characters Left: 160", 5, 130, 100, 20)

$Button1 = GUICtrlCreateButton("Load Excel", 5, 150, 70, 17, 0)

GUICtrlSetState(-1, $GUI_FOCUS) ; zal de focus op deze knop zetten

$GUIccl = GUICtrlCreateCombo("", 5, 172, 201, 137) ; Renamed variable name since it used to have the same name as the array variable

$List1 = GUICtrlCreateList("", 5, 200, 201, 137)

$Do = GUICtrlCreateButton("Do", 220, 172, 70, 17, 0)

$make = GUICtrlCreateButton("Make", 220, 202, 70, 17, 0)

;$Button4 = GUICtrlCreateButton("Propagate List", 32, 384, 105, 33, 0) ; Renamed button 'coz I was getting confused

GUISetState(@SW_SHOW)

#EndRegion ### END Koda GUI section ###

While 15

;Sleep(10)

;$msg = GUIGetMsg()

;$readtxt = GUICtrlRead($as_Body)

;$length = StringLen($readtxt)

;$actualchar = Execute(160 - $length)

;GUICtrlSetData($numchar, "Characters Left: " & $actualchar)

$nMsg = GUIGetMsg()

Switch $nMsg

Case $GUI_EVENT_CLOSE

Exit

Case $Button1

$iFile = FileOpenDialog("Load Forms", @ScriptDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)

_read()

; Hierna zal de lijst gevuld worden

$list = ""

For $x = 0 To UBound($aArray1) -1

If $x == UBound($aArray1) -1 Then

$list &= $aArray1[$x]

Else

$list &= $aArray1[$x] & "|"

EndIf

Next

GUICtrlSetData($GUIccl, $list)

Case $Do

$sSearch = GUICtrlRead($GUIccl, 1) ; leest de gekozen optie uit de combobox

$tSend = GUICtrlRead($as_Body, 1) ; leest de text uit welke heraald moet worden

$iIndex = _ArraySearch($aArray1, $sSearch, 0, 0, 0, 1)

If @error Then

MsgBox(0, "Not Found", '"' & $sSearch & '" was not found in the array.')

Else

MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ".")

EndIf

;_ArrayDisplay($aArray1, "Array using Default Parameters")

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+1, 5, 1)

;_ArrayDisplay($aArray2, "Array using Default Parameters")

$list3 = ""

For $x = 0 To UBound($aArray2) -1

If $x == UBound($aArray2) -1 Then

$list3 &= $aArray2[$x]

Else

$list3 &= $aArray2[$x] & "|"

EndIf

Next

GUICtrlSetData($List1, $list3)

Case $make

EndSwitch

WEnd

Func _read()

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray1 = _ExcelReadArray($oExcel1, 1, 1, 5)

_ExcelBookClose($oExcel1)

EndFunc

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Hey,

never new that i already have the value :)

I think the code is as bugy as Hell, bur this part is working now.

Can i use a loop to do something with all the array value's ?

Of course. You already have that technique in your code:
$list = ""
            For $x = 0 To UBound($aArray1) - 1
                If $x == UBound($aArray1) - 1 Then
                    $list &= $aArray1[$x]
                Else
                    $list &= $aArray1[$x] & "|"
                EndIf
            Next
            GUICtrlSetData($GUIccl, $list)

You don't have to assemble a string of all the values to add, though. You can just loop through the array and add each item to the combo:

For $x = 0 To UBound($aArray1) - 1
                GUICtrlSetData($GUIccl, $aArray1[$x])
            Next

The same technique works for the list control, too.

:)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Hi,

just figured out how to make for every value a txt file with some text in it.

Trying your Ubound explanation now.

CODE
Case $make

$readtxt = GUICtrlRead($as_Body)

$i = 0

For $i = 0 To $aArray2[0]

$file = FileOpen($aArray2[$i] & ".txt", 1)

FileWrite($file, $readtxt)

Next

Share this post


Link to post
Share on other sites

Everything seems to work OK but only the count of the values in the array not.

Now he is only sending 1 email and if i change the value (5) he send 5, but i want to find out how many values there where in the array and use that.

someone knows how to do that?

Greets

Daferra

CODE
#include <Excel.au3>

#include <array.au3>

#include <GUIConstants.au3>

#include <GuiScrollBars.au3>

#include <misc.au3>

#include <GUIConstantsEx.au3>

#include <WindowsConstants.au3>

#include <ScrollBarConstants.au3>

#include <EditConstants.au3>

#Include <File.au3>

#include <String.au3>

Global $oExcel, $aArray1, $s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject, $s_Body, $s_AttachFiles, $s_CcAddress, $s_BccAddress, $s_Username, $s_Password, $IPPort, $ssl, $oMyRet[2], $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

;Global $oExcel, $aArray1, $WS_POPUP, $WS_BORDER, $ES_PASSWORD, $s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject, $s_Body, $s_AttachFiles, $s_CcAddress, $s_BccAddress, $s_Username, $s_Password, $IPPort, $ssl, $oMyRet[2], $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

$s_FromName = ""

$s_FromAddress = ""

$s_Username = ""

$s_Password = ""

$s_Subject = "sms2" ; subject from the email - can be anything you want it to be

$as_Body = "" ; the messagebody from the mail - can be left blank but then you get a blank mail

$s_AttachFiles = "" ; the file you want to attach- leave blank if not needed

$s_CcAddress = "" ; address for cc - leave blank if not needed

$s_BccAddress = "" ; address for bcc - leave blank if not needed

$IPPort = 465

$ssl = 1

$ini = @ScriptDir & "\" & "settings.ini"

$s_SmtpServer = IniRead($ini, "SMS_Sender", "smtp", "NotFound")

$s_mailfrom = IniRead($ini, "SMS_Sender", "mailfrom", "NotFound")

$s_ToAddress = IniRead($ini, "SMS_Sender", "rcpt_to", "NotFound")

$s_subject = IniRead($ini, "SMS_Sender", "subject", "NotFound")

#Region ###

$Width1 = 320

$Height1 = 220

$Form1 = GUICreate("SMS Sender", $Width1, $Height1, 359, 400, BitOR($WS_POPUP, $WS_BORDER))

$Close = GUICtrlCreateLabel("X", $Width1 - 15, 0, 11, 20)

GUICtrlSetFont(-1, 10, 800, 0, "Comic Sans MS")

GUICtrlSetColor(-1, 0xFFFFFF)

GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT)

GUICtrlSetCursor(-1, 0)

$Formtitle = GUICtrlCreateLabel(" SMS Sender ", 0, 4, @DesktopWidth, 20, -1, $GUI_WS_EX_PARENTDRAG)

GUICtrlSetColor(-1, 0xFFFFFF)

GUICtrlSetBkColor(-1, $GUI_BKCOLOR_TRANSPARENT)

$Formtitlebackground = GUICtrlCreateGraphic(0, 0, @DesktopWidth, 22)

GUICtrlSetBkColor(-1, 0x000000)

$as_Body = GUICtrlCreateEdit("", 5, 26, 310, 100, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_WANTRETURN))

$numchar = GUICtrlCreateLabel("Characters Left: 160", 5, 130, 100, 20)

$Button1 = GUICtrlCreateButton("Load Excel", 5, 150, 70, 17, 0)

GUICtrlSetState(-1, $GUI_FOCUS) ; zal de focus op deze knop zetten

$GUIccl = GUICtrlCreateCombo("", 5, 172, 201, 137)

$send = GUICtrlCreateButton("Send", 220, 172, 95, 20, 0)

#EndRegion ###

GUISetState(@SW_SHOW, $Form1)

While 15

;Sleep(10)

;$msg = GUIGetMsg()

;$readtxt = GUICtrlRead($as_Body)

;$length = StringLen($readtxt)

;$actualchar = Execute(160 - $length)

;GUICtrlSetData($numchar, "Characters Left: " & $actualchar)

$nMsg = GUIGetMsg()

Switch $nMsg

Case $GUI_EVENT_CLOSE

Exit

Case $Close

Exit

Case $Button1

$iFile = FileOpenDialog("Load Forms", @ScriptDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)

_read()

; Hierna zal de lijst gevuld worden

$list = ""

For $x = 0 To UBound($aArray1) -1

If $x == UBound($aArray1) -1 Then

$list &= $aArray1[$x]

Else

$list &= $aArray1[$x] & "|"

EndIf

Next

GUICtrlSetData($GUIccl, $list)

Case $send

$sSearch = GUICtrlRead($GUIccl, 1) ; leest de gekozen optie uit de combobox

$iIndex = _ArraySearch($aArray1, $sSearch, 0, 0, 0, 1)

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 1, 1)

_ExcelBookClose($oExcel1)

$readtxt = GUICtrlRead($as_Body)

For $i = 0 To UBound($aArray2) - 1

$s_Body = "SMS=0" & $aArray2[$i] & @CRLF & "TEXT=" & $readtxt & @CRLF

GUICtrlSetState($send, $GUI_DISABLE)

GUICtrlSetData($send, "Sending..." & $i )

$rc = _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject, $s_Body, $s_AttachFiles, $s_CcAddress, $s_BccAddress, $s_Username, $s_Password, $IPPort, $ssl)

If @error Then MsgBox(0, "Error sending message", "Error code:" & @error & " Rc:" & $Send)

GUICtrlSetData($send, "Send")

GUICtrlSetState($send, $GUI_ENABLE)

Next

EndSwitch

WEnd

Func _read()

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray1 = _ExcelReadArray($oExcel1, 1, 1, 5)

_ExcelBookClose($oExcel1)

EndFunc

Func _load()

$sSearch = GUICtrlRead($GUIccl, 1) ; leest de gekozen optie uit de combobox

$iIndex = _ArraySearch($aArray1, $sSearch, 0, 0, 0, 1)

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 5, 1)

_ExcelBookClose($oExcel1)

EndFunc

Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $s_Body = "", $s_AttachFiles = "", $s_CcAddress = "", $s_BccAddress = "", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0)

$objEmail = ObjCreate("CDO.Message")

$objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>'

$objEmail.To = $s_ToAddress

Local $i_Error = 0

Local $i_Error_desciption = ""

If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress

If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress

$objEmail.Subject = $s_Subject

If StringInStr($s_Body, "<") And StringInStr($s_Body, ">") Then

$objEmail.HTMLBody = $s_Body

Else

$objEmail.Textbody = $s_Body & @CRLF

EndIf

If $s_AttachFiles <> "" Then

Local $S_Files2Attach = StringSplit($s_AttachFiles, ";")

For $x = 1 To $S_Files2Attach[0]

$S_Files2Attach[$x] = _PathFull($S_Files2Attach[$x])

If FileExists($S_Files2Attach[$x]) Then

$objEmail.AddAttachment($S_Files2Attach[$x])

Else

$i_Error_desciption = $i_Error_desciption & @LF & 'File not found to attach: ' & $S_Files2Attach[$x]

SetError(1)

Return 0

EndIf

Next

EndIf

$objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

$objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer

$objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort

If $s_Username <> "" Then

$objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

$objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username

$objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password

EndIf

If $ssl Then

$objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

EndIf

$objEmail.Configuration.Fields.Update

$objEmail.Send

If @error Then

SetError(2)

Return $oMyRet[1]

EndIf

EndFunc ;==>_INetSmtpMailCom

Func MyErrFunc()

$HexNumber = Hex($oMyError.number, 8)

$oMyRet[0] = $HexNumber

$oMyRet[1] = StringStripWS($oMyError.description, 3)

ConsoleWrite("### COM Error ! Number: " & $HexNumber & " ScriptLine: " & $oMyError.scriptline & " Description:" & $oMyRet[1] & @LF)

SetError(1)

Return

EndFunc ;==>MyErrFunc

Share this post


Link to post
Share on other sites

Everything seems to work OK but only the count of the values in the array not.

Now he is only sending 1 email and if i change the value (5) he send 5, but i want to find out how many values there where in the array and use that.

someone knows how to do that?

Greets

Daferra

How many values in which array? You are already using Ubound() to loop on every element in $Array2:
For $i = 0 To UBound($aArray2) - 1
                $s_Body = "SMS=0" & $aArray2[$i] & @CRLF & "TEXT=" & $readtxt & @CRLF

            ; ...<snip>
                
                $rc = _INetSmtpMailCom($s_SmtpServer, ...)

            ; ...<snip>
            Next

That portion of your code will call _INetSmtpMailCom() for every element in $aArray2. Where is it that you "change the value (5)" to get more elements?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Its probably in the lines before the loop, its the 5 in the excelreadarray.

I've tried to find a "help" on that but not find it.

CODE
$sSearch = GUICtrlRead($GUIccl, 1) ; leest de gekozen optie uit de combobox

$iIndex = _ArraySearch($aArray1, $sSearch, 0, 0, 0, 1)

$oExcel1 = _ExcelBookOpen($iFile, 0)

$aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 1, 1)

_ExcelBookClose($oExcel1)

CODE
$aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 5, 1)

Grtz

Ferdi

Share this post


Link to post
Share on other sites

Its probably in the lines before the loop, its the 5 in the excelreadarray.

I've tried to find a "help" on that but not find it.

$sSearch = GUICtrlRead($GUIccl, 1); leest de gekozen optie uit de combobox
            $iIndex = _ArraySearch($aArray1, $sSearch, 0, 0, 0, 1)
            $oExcel1 = _ExcelBookOpen($iFile, 0)
            $aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 1, 1)
            _ExcelBookClose($oExcel1)

$aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 5, 1)

Grtz

Ferdi

Ah, I get it. You want to know the used range in that column so you know how much to read. The old ExcelCOM_UDF.au3 had a function called _ExcelSheetUsedRangeGet() that didn't make it into the new Excel.au3 that is shipped with the AutoIt Beta. The functionality can be achieved with your own object references, but if you are not comfortable with that it would be easier to read more cells than required and just skip or remove empty elements when you use the array:
; Read 100 rows of the selected column
            $aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 100, 1) 
            
        ; Remove unused elements from the end of the array
            For $n = UBound($aArray2) - 1 To 0 Step -1
                If StringStripWS($aArray2[$n], 8) = "" Then
                    _ArrayDelete($aArray2, $n)
                Else
                    ExitLoop
                EndIf
            Next

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Hi PsaltyDS,

Thats seems to work fine.

For $n = UBound($aArray2) - 1 To 0 Step -1

For $n = 101 - 1 To 0 Step -1

For $n = 100 To 0 Step -1

As far as i can see you are walking true the entire array, why is it not so that the empty rows between the values are not deleted.

grtz daferra

Ah, I get it. You want to know the used range in that column so you know how much to read. The old ExcelCOM_UDF.au3 had a function called _ExcelSheetUsedRangeGet() that didn't make it into the new Excel.au3 that is shipped with the AutoIt Beta. The functionality can be achieved with your own object references, but if you are not comfortable with that it would be easier to read more cells than required and just skip or remove empty elements when you use the array:

; Read 100 rows of the selected column
            $aArray2 = _ExcelReadArray($oExcel1, 2, $iIndex+2, 100, 1) 
            
    ; Remove unused elements from the end of the array
            For $n = UBound($aArray2) - 1 To 0 Step -1
                If StringStripWS($aArray2[$n], 8) = "" Then
                    _ArrayDelete($aArray2, $n)
                Else
                    ExitLoop
                EndIf
            Next

:)

Share this post


Link to post
Share on other sites

Hi PsaltyDS,

Thats seems to work fine.

For $n = UBound($aArray2) - 1 To 0 Step -1

For $n = 101 - 1 To 0 Step -1

For $n = 100 To 0 Step -1

As far as i can see you are walking true the entire array, why is it not so that the empty rows between the values are not deleted.

grtz daferra

The loop I posted was intended to delete empty rows only AFTER the last used row. As soon as it hits an element that is not empty the loop exits with ExitLoop. This may not be desirable and is easy to remove. It was included so that the used elements would not lose their index numbers, i.e. what was at [4] will still be at [4] after the loop is done. If [3] is empty and gets deleted, then what was in [4] is now in [3]. If that doesn't hurt anything, then by all means remove all unused elements.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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