Hlaurent

Excel - Filling cells from radio button groups on autoit form - help me please

17 posts in this topic

#1 ·  Posted (edited)

hi. i tried so much and at the end here i am writing this:

my problem is that i cant find a way to assign "WRITE'EM" button to send radio group values (as text or number) to a specific cell (column(x) and already selected row(selected y)) in already opened (active excel document) .

process:

WHO button :  Entries to the excel table will be one after another. So i will select the first cell manually. then i start gui and  I will press WHO. (i made it already and it works. so help that i need is not here) 

SHEET, DAYS, STARTS, WEEKEND selections will be clicked by user (me) and WRITE'EM button should send all radio-button values at once to the cells (selected row number(y) which i chose as i activate WHO button.

for example : SHEET VALUE = 43 should fill "43" cell(6,$y)
and $y is active cell row number. 

DAYS Value = 30 should fill "30" cell(7,$y)
STARTS value = "15.06.2015" cell(8,$y)
WEEKEND value ="YOK" cell(9,$y)
 

as i hit the WRITE'EM

tumblr_nop61lhUdT1rt0f83o1_500.jpg

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <MsgBoxConstants.au3>
#Region ### START Koda GUI section ### Form=C:\Users\hloo\Documents\koda_1.7.3.0\Forms\staj_go.kxf
Global $Form1 = GUICreate("Form1", 398, 287, 445, 275)
Global $Edit1 = GUICtrlCreateEdit("", 48, 24, 329, 73)
GUICtrlSetData(-1, "Edit1")
Global $Button1 = GUICtrlCreateButton("WRITE'EM", 96, 216, 105, 33) ; sends all radio button group values to specific cells at once
Global $Button3 = GUICtrlCreateButton("WHO", 8, 24, 33, 225)
Global $Group1 = GUICtrlCreateGroup("SHEET", 48, 112, 57, 81)
Global $Radio1 = GUICtrlCreateRadio("22", 56, 136, 49, 17)
GUICtrlSetState(-1, $GUI_CHECKED)
Global $Radio2 = GUICtrlCreateRadio("43", 56, 160, 49, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
Global $Group2 = GUICtrlCreateGroup("DAYS", 112, 112, 65, 81)
Global $Radio3 = GUICtrlCreateRadio("30", 128, 136, 33, 17)
GUICtrlSetState(-1, $GUI_CHECKED)
Global $Radio4 = GUICtrlCreateRadio("60", 128, 160, 41, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
Global $Group3 = GUICtrlCreateGroup("STARTS", 184, 112, 105, 81)
Global $Radio5 = GUICtrlCreateRadio("15.06.2015", 200, 136, 81, 17)
Global $Radio6 = GUICtrlCreateRadio("29.06.2015", 200, 160, 81, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
Global $Group4 = GUICtrlCreateGroup("WEEKEND", 296, 112, 81, 81)
Global $Radio7 = GUICtrlCreateRadio("VAR", 312, 136, 49, 17)
Global $Radio8 = GUICtrlCreateRadio("YOK", 312, 160, 49, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

         Case $Button3
Local $i = 0
; take information from active excel, select 2 cell and send it to autoit form by simple keyboard keys - done
Do
   WinActivate("Microsoft Excel") ; go to Excel
                WinWaitActive("Microsoft Excel")
                Sleep(500)
                Send("+{RIGHT 2}")
                Sleep(500)
                Send("^c")
                Send("!{TAB}") ; back
                Sleep(500)
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("^a")
                Send("^v")
Until GUICtrlRead($Button3) = $i
 ; copies 


;Case $Radio1 ------------------below, that i couldnt manege to make it right
;   if $Radio1 = 1 then
;     Local $belge = "22"
;   ElseIf $Radio2 = 1 then
;     Local $belge = "43"
;   Case $Radio3
;     if $Radio3 = 1 then
;     Local $gun = "30"
;   ElseIf $Radio2 = 1 then
;     Local $gun = "60"


;Case $Button1
;.cells($i, $j).value = $belge ----------------- this part that i need help most.
           EndSwitch

WEnd

i put ";" marks to my failures. if you want to help this code-poor person which is happen to be me, please don't bother to write all. just a clue would be enough.

ps: i don't need MSGBOX in any section of this. But all i find as function in examples is msgbox. 
i dont need clean excel book to open. i have students information in existed excel file and all i need to automate some specific cells again and again.

thx thx thx.

 

sry i forgot  here is the piece of excel file that i work on.

https://docs.google.com/spreadsheets/d/1Gins_bYw143qn_voZQBsCAwPMQDbwDyttg1l_qKZ5HU/edit?usp=sharing

 

Edited by Hlaurent

Share this post


Link to post
Share on other sites



Have a look at the Excel UDF.


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

. with quote, answer is below 

Edited by Hlaurent
didn't see the quote thing at first

Share this post


Link to post
Share on other sites

Have a look at the Excel UDF.

i looked there. thx for quick response btw. But that is not the answer to my deadlock.

i need an example which uses _Excel_RangeWrite whom range is consist of already selected row number and constant x for radio groups.

 

Share this post


Link to post
Share on other sites

It looks like your trying to use dot notation on an object without a MS excel object.


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Hi,

 as MikahS Mentioned you will eventually have to get cosy with the excel UDF.

But in the mean time I threw together a down and dirty example that I think might answer your question of how to get your GUI variables; radio buttons etc into an excel sheet.

This is mostly CCP' ed from the helpfile with some small changes to illustrate the point.

#include <GUIConstantsEx.au3>
#include <Excel.au3>

Example()

Func Example()
    ; Create a GUI with various controls.
    Local $hGUI = GUICreate("Example")
    Local $idOK = GUICtrlCreateButton("OK", 310, 370, 85, 25)
    Local $idSend = GUICtrlCreateButton("Send", 200, 200, 85, 25)
    Global $Radio1 = GUICtrlCreateRadio("22", 56, 136, 49, 17)
    ; Display the GUI.
    GUISetState(@SW_SHOW, $hGUI)

    ; Loop until the user exits.
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE, $idOK
                ExitLoop
            Case $idSend ;---------------Send Button Calls Case $iDSend----------Case calls Function named ""SendToExcel()"" below
                SendToExcel()

        EndSwitch
    WEnd

    ; Delete the previous GUI and all controls.
    GUIDelete($hGUI)
EndFunc   ;==>Example



Func SendToExcel()

    ; Create application object and create a new workbook
    Local $oAppl = _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($oAppl)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oAppl)
        Exit
    EndIf

    ;--------------------------------------------------
    $radioVar = GUICtrlRead($Radio1)

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $radioVar, "A3")

    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

EndFunc
Edited by l3ill
effed up

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Hi,

 as MikahS Mentioned you will eventually have to get cosy with the excel UDF.

But in the mean time I threw together a down and dirty example that I think might answer your question of how to get your GUI variables; radio buttons etc into an excel sheet.

This is mostly CCP' ed from the helpfile with some small changes to illustrate the point.

 what a active forum! thank you firstly. 

in your example you decided the source cell right? = A3.  

$radioVar = GUICtrlRead($Radio1)

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $radioVar, "A3")

but i need something like this: 

i click -for randomly example- D36 cell on already open excel doc.

can i have this :

$activeCellRow = _excelGetActiveRowValue() ; which i made up - which is my question
$constantColomn1 = 6
(then your way)

$radioVar = GUICtrlRead($Radio1)

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $radioVar, "$oExcel.activesheet.Cells( $constantColomn1, $activeCellRow)") <<<----instead of "A3"

ok?

i need variables comes from active-manually-clicked cell's row information instead of determinative "A3" or any thing like that. 

so i need to automate the cell ingredients. x and y from outside of user entry. 

 

Edited by Hlaurent
"column is first" fix

Share this post


Link to post
Share on other sites

Quick example of how to get the active cell using dot notation. Don't know if it's a very elegant solution, but it gives you an idea of what to search for etc.

#include <Excel.au3>

HotKeySet("{ESC}", "Quit")

Local $oWorkbook = _Excel_Open()

_Excel_BookNew($oWorkbook)
_Excel_SheetAdd($oWorkbook)

Local $oRange = $oWorkbook.Application.Activecell.Address

Local $oAddress = StringReplace($oRange, "$", "")

MsgBox(0, "", $oAddress)

while 1

WEnd

Func Quit()
    _Excel_Close($oWorkbook)
    Exit
EndFunc

 


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Sorry, I am not positive what you are going for but as mentioned before you will have to get in to the help file and play around with the examples and find the function closes to what you want to achieve and then change it accordingly.

That's how I learned :D the little bit I know...

Specifically I would start with _Excel_RangeRead

The keywords you mentioned seem to be going this direction.

BTW: There are other ways of directing data into the sheet besides A1 Range; array, 2D array, Range Object...

Edited by l3ill
msp

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Very Cool MikahS !

So using this additional code I have updated my example.

Now you can click on your excel sheet where you want to insert and then click the send button on the GUI...

 

#include <GUIConstantsEx.au3>
#include <Excel.au3>

Example()

Func Example()
    ; Create a GUI with various controls.
    Local $hGUI = GUICreate("Example")
    Local $idOK = GUICtrlCreateButton("OK", 310, 370, 85, 25)
    Local $idSend = GUICtrlCreateButton("Send", 200, 200, 85, 25)
    Global $Radio1 = GUICtrlCreateRadio("22", 56, 136, 49, 17)
    ; Display the GUI.
    GUISetState(@SW_SHOW, $hGUI)

    ; Loop until the user exits.
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE, $idOK
                ExitLoop
            Case $idSend ;---------------------------------Send Button Calls Case $iDSend----------Case calls Function named ""SendToExcel()"" below
                SendToExcel()

        EndSwitch
    WEnd

    ; Delete the previous GUI and all controls.
    GUIDelete($hGUI)
EndFunc   ;==>Example



Func SendToExcel()

    $radioVar = GUICtrlRead($Radio1)
    Local $sWorkbook = @ScriptDir & "\_Excel1.xls"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
     Local $oRange = $oWorkbook.Application.Activecell.Address
     Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $radioVar, $oAddress)

    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)


EndFunc   ;==>SendToExcel

 

 

 

Edited by l3ill
additional info
1 person likes this

Share this post


Link to post
Share on other sites

Quick example of how to get the active cell using dot notation. Don't know if it's a very elegant solution, but it gives you an idea of what to search for etc.

Very Cool MikahS !

So using this additional code I have updated my example.

Now you can click on your excel sheet where you want to insert and then click the send button on the GUI...

 thank you both  MikahS and  I3ill very much.  i couldn't finish it today though, i feel that i m almost there with your help. 

I3ill 

your example send always "1" to the cell. but it s cool. now i know $oWorkbook.Application.Activecell.Address thing which is pretty good clue for me.  

my all au3s in my workplace. and i forgot to copy them for home. till monday,,, have a nice weekend to you.

thx again. 

Share this post


Link to post
Share on other sites

@Hlaurent My pleasure. Let us know how it goes. 


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

done. 

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>
#Region ### START Koda GUI section ### Form=C:\Users\hloo\Documents\koda_1.7.3.0\Forms\staj_go.kxf

Example()

Func Example()
Global $Form1 = GUICreate("Form1", 398, 287, 445, 275)
Global $Edit1 = GUICtrlCreateEdit("", 48, 24, 329, 73)
GUICtrlSetData(-1, "Edit1")
Global $Button1 = GUICtrlCreateButton("YAZ", 96, 216, 105, 33)
Global $Button2 = GUICtrlCreateButton("NEXT", 224, 216, 57, 33)
Global $Button3 = GUICtrlCreateButton("BILGI", 8, 24, 33, 225)
Global $Group1 = GUICtrlCreateGroup("BELGE", 48, 112, 57, 81)
Global $Radio1 = GUICtrlCreateRadio("22", 56, 136, 49, 17)
GUICtrlSetState(-1, $GUI_CHECKED)
Global $Radio2 = GUICtrlCreateRadio("43", 56, 160, 49, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
Global $Group2 = GUICtrlCreateGroup("SÜRE", 112, 112, 65, 81)
Global $Radio3 = GUICtrlCreateRadio("30", 128, 136, 33, 17)
GUICtrlSetState(-1, $GUI_CHECKED)
Global $Radio4 = GUICtrlCreateRadio("60", 128, 160, 41, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
Global $Group3 = GUICtrlCreateGroup("BASLAMA", 184, 112, 105, 81)
Global $Radio5 = GUICtrlCreateRadio("15.06.2015", 200, 136, 81, 17)
Global $Radio6 = GUICtrlCreateRadio("29.06.2015", 200, 160, 81, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
Global $Group4 = GUICtrlCreateGroup("CUMARTESI", 296, 112, 81, 81)
Global $Radio7 = GUICtrlCreateRadio("VAR", 312, 136, 49, 17)
Global $Radio8 = GUICtrlCreateRadio("YOK", 312, 160, 49, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
            Case $Button3
   WinActivate("Microsoft Excel") ; go to Excel
                WinWaitActive("Microsoft Excel")
                Sleep(300)
                Send("+{RIGHT 2}")
                Sleep(300)
                Send("^c")
                Send("{RIGHT}")
                Send("{RIGHT}")
                Send("{RIGHT}")
                Send("!{TAB}") ; back
                Sleep(300)
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("^a")
                Send("^v")

          Case $Button1 ;------Send Button Calls Case $iDSend------Case calls Function named ""SendToExcel()"" below
             SendToExcel()
          Case $Button2
             WinActivate("Microsoft Excel") ; -------------------if it s wrong next
                WinWaitActive("Microsoft Excel")
                Sleep(300)
                Send("^h"); triggers excel macro to paint the cell red
                Send("{DOWN}")
                Send("{LEFT}")
                Send("{LEFT}")
                Send("{LEFT}")
                Sleep(300)
                Send("+{RIGHT 2}")
                Sleep(300)
                Send("^c")
                Send("{RIGHT}")
                Send("{RIGHT}")
                Send("{RIGHT}")
                Send("!{TAB}") ; back
                Sleep(300)
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("^a")
                Send("^v") ; back
             EndSwitch
WEnd

 EndFunc



   Func SendToExcel()
    if GUICtrlRead($Radio1) = "1" Then ; ------------------------------------------------first part
Local $setradio1 = "22"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio1, $oRange)

ElseIf GUICtrlRead($Radio2) = "1" Then
    Local $setradio2 = "43"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio2, $oRange)
 EndIf
 WinActivate("Microsoft Excel") ; go to Excel
                WinWaitActive("Microsoft Excel")
                Sleep(300)
                Send("{RIGHT}")
                if GUICtrlRead($Radio3) = "1" Then ;----------------------------------------second part
Local $setradio3 = "30"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio3, $oRange)

ElseIf GUICtrlRead($Radio4) = "1" Then
    Local $setradio4 = "60"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio4, $oRange)
 EndIf
 WinActivate("Microsoft Excel") ; go to Excel
                WinWaitActive("Microsoft Excel")
                Sleep(300)
                Send("{RIGHT}")
                Send("{RIGHT}")
                Send("{RIGHT}");-----------------------------------------------------3th part
                if GUICtrlRead($Radio5) = "1" Then
                Local $setradio5 = "15.06.2015"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio5, $oRange)

ElseIf GUICtrlRead($Radio6) = "1" Then
    Local $setradio6 = "29.06.2015"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio6, $oRange)
 EndIf

 WinActivate("Microsoft Excel") ; go to Excel
                WinWaitActive("Microsoft Excel")
                Sleep(300)
                Send("{LEFT}");-----------------------------------------------------4th part
                if GUICtrlRead($Radio7) = "1" Then
                Local $setradio7 = "VAR"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio7, $oRange)

ElseIf GUICtrlRead($Radio8) = "1" Then
    Local $setradio8 = "YOK"
    Local $sWorkbook = "C:\Users\hloo\Desktop\stajlar 2015 taslak\New folder\48.xlsx"
    Local $oWorkbook = _Excel_BookAttach($sWorkbook)
    Local $oRange = $oWorkbook.Application.Activecell.Address
    Local $oAddress = StringReplace($oRange, "$", "")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $setradio8, $oRange)
 EndIf
 WinActivate("Microsoft Excel") ;
                WinWaitActive("Microsoft Excel")
                Sleep(300)
                Send("{DOWN}")
                Send("{LEFT 6}")
                Sleep(100)
                Send("!{TAB}")
EndFunc

 

even if this s very very bad coding -i know i admit- now it's functioning very well for me. 

thx again, your contribution helped me a lot. 

Share this post


Link to post
Share on other sites

No problem, glad you found a solution. ^_^


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

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

    • anusha
      By anusha
      Hi I have jus started using auto-it . Please correct me if I'm wrong.
      I need to read data from an input in text box and search in excel file and return value in next column of matched cell on GUI.
      I have written below code but i cannot use variable which has data stored. it works only when search string is hard coded.
      Please help out.
       
      Example()
      Func Example()
      Local $GuiMain = GUICreate("EXCEL TEST", 399, 180) ;creates main GUI
      ;~ Local $idOK = GUISetOnEvent($GUI_EVENT_CLOSE, "Close")
      Local $iWidthCell = 70
      Local $idLabel = GUICtrlCreateLabel("PART NUMBER", 10, 30, $iWidthCell,50)
      Local $RUN_1 = GUICtrlCreateButton("OK", 70, 70, 85, 25)
      Local $Input_1 = GUICtrlCreateInput("PART NUMBER", 100, 20, 120, 20)
      Local $sMenutext = GUICtrlRead($Input_1, 1)
      GUISetState(@SW_SHOW, $GuiMain)

          While 1
          $MSG = GUIGetMsg()
          Select
              Case $MSG = $GUI_EVENT_CLOSE
                  Exit
              Case $MSG = $RUN_1
                  Local $oAppl = _Excel_Open()

      Local $sFilePath1 = "D:\Anu_WorkFolder\Components.xlsx"
      Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath1, Default, Default, True)
      Local $aResult = _Excel_RangeFind($oWorkbook, $sMenutext , Default, Default, $xlWhole)
    • Nareshm
      By Nareshm
      How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name.
      I Tried with
      Winactivate ("[CLASS:XLMAIN]") but not working
    • willichan
      By willichan
      Here is another one from my archives that filled a specific need.
       
      Here is the back story if you are interested.
       
      Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.
      #cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global $SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global $sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database") $SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError $sOccurenceName = StringReplace($sOccurenceName, "\", "") $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName) $lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] = $ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile, $vResult, $iErrLoop $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf $vResult = $SQLITE_IOERR $iErrLoop = 5 While $vResult = $SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF) $vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0, $MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery, $aCount, $iErrLoop, $vResult ConsoleWrite($strFileName & @CRLF) Local $iLoop, $iExists Local $aLinks Local Const $xlExcelLinks = 1 Local $oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local $err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " & $err & @CRLF) Exit EndIf EndIf $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For $iLoop = 0 To UBound($aLinks) - 1 If $aLinks[$iLoop] <> $strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop], $iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName, $aLinks[$iLoop]) If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult, $hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If $SQLITE_OK And UBound($vCount) > 1 Then $vCount = $vCount[1] + 1 Else $vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then $bReadOnly = False If $bVisible = Default Then $bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows If $aResult[($iLoop * 2) + 2] > 0 Then $sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)], $sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName, $sStyle = Default) If $sStyle = Default Then $sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle, $aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2, $iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs, $sText) Local $iLoop If $iTabs > 0 Then For $iLoop = 1 To $iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ") $sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName  
    • Dimmae
      By Dimmae
      Hello,
      at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future.
      Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array.
      I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array.
      The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4.
       
      Hope you can help me, and sry again for this 'unlucky illustration'.
       
      btw: how can i add code shown as code here, instead of posting it as a attached file?.
       
       
       
      autoit-select-column.au3
      defects.xlsx
    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have an error:
      ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR  
      My code is:
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution.
      But i didn't solve the issue.
      Has anyone an idea of what the problem might be?
      Thanks in advance.