copy function from excel to autoit

Daferra · July 8, 2008

Hi All,

Can someone give me a start to make a script that can act on some values that are selected in excel.

Maybe is it possible that within excel u can select some values en then run a macro with will send the selected values to a autoit array.

Or a copy past functionality to a autoit gui.

I dit read a lot of post but nothing in the right directions.

With kind regards,

Ferdi

Airwolf · July 8, 2008

You'll need to use the ExcelCOM UDF Library:

http://www.autoitscript.com/forum/index.ph...amp;hl=excelcom

And there are examples on that topic that go along with it.

Daferra · July 8, 2008

Hi,

Im reading and reading, nothing that get me into the good direction.

Maybe a other selection is it possible to get the sellected cells to a text file from within excel, or a hidden copy paste function while there are selected cells in excel..

Really need some start here guys.

many thanks

Ferdi

Kerros · July 8, 2008

From that Excel_UDF check out

_ExcelReadCell

_ExcelReadArray

They will both select at least one cell and put it into a variable in AutoIt.

$Score = _ExcelReadCell($oExcel, A1)

aslani · July 9, 2008

Hi,

Im reading and reading, nothing that get me into the good direction.

Maybe a other selection is it possible to get the sellected cells to a text file from within excel, or a hidden copy paste function while there are selected cells in excel..

Really need some start here guys.

many thanks
Ferdi

Here's an example:

Func _cList()
    WinActivate($title)
    
    Dim $aList[1][5]
    
    $i_path_1 = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)
    $i_path_2 = FileOpenDialog("Create List or Add to a List", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)")
    
    $aFiles = StringSplit($i_path_1, '|')
    
    ;_ArrayDisplay ($aFiles, "$aFiles") ; for debug
    
    $mousePos = MouseGetPos()
    
    _MouseTrap($mousePos[0], $mousePos[1], $mousePos[0], $mousePos[1])
    
    $prog_gui = GuiCreate("Please wait", 500, 70, -1, $guiloc_y-100)
    $prog_bar1 = GUICtrlCreateProgress ($offset_1, 5, 475, 10, $PBS_SMOOTH)
    $prog_bar2 = GUICtrlCreateProgress ($offset_1, 20, 475, 10, $PBS_SMOOTH)
    $prog_label = GUICtrlCreateLabel ("", $offset_1, 35, 475, 25)
    GUISetState (@SW_SHOW, $prog_gui)
    $prog_perc1 = 0
    $prog_perc2 = 0
    
    For $ia = 0 To Ubound($aFiles) - 3
        $cOrig = 26 ;26 or 28
        
        GUICtrlSetData ($prog_label, "Reading..." & @CRLF & $aFiles[1] & "\" & $aFiles[$ia+2])
        $aList[$ia][0] = _ExcelReadCell($rExcel, "C5")        ; description
        $aList[$ia][1] = _ExcelReadCell($rExcel, "C7")        ; manufacturer
        $aList[$ia][2] = _ExcelReadCell($rExcel, "C9")        ; mfg partnumber
        $aList[$ia][3] = _ExcelReadCell($rExcel, "C" & $cOrig)    ; originator name
        $ord = _ExcelReadCell($rExcel, "P" & $cOrig)                ; originator date
        _ExcelBookClose($rExcel)
        
        $ord = StringLeft(String($ord), 8)
        $ordm = StringRight(StringLeft($ord, 6), 2)
        $ordd = StringRight($ord, 2)
        $ordy = StringLeft($ord, 4)
        $aList[$ia][4] = $ordm & "/" & $ordd & "/" & $ordy
        
        ReDim $aList[UBound($aList) + 1][Ubound($aList, 2)]
        $prog_perc1 += 100 / (Ubound($aFiles) - 3)
        GUICtrlSetData ($prog_bar1, $prog_perc1)
    Next
    
    For $ib = 0 To UBound($aList) - 1
        $cl = 2
        GUICtrlSetData ($prog_label, "Writing..." & @CRLF & $i_path_2)
        Local $oExcel = _ExcelBookOpen($i_path_2, 0)
        
        While _ExcelReadCell($oExcel, "B" & $cl) <> ""
            $cl = $cl+1
        WEnd
        
        _ExcelWriteCell($oExcel, $aList[$ib][0], "B" & $cl)  ; description
        _ExcelWriteCell($oExcel, $aList[$ib][1], "C" & $cl)  ; manufacturer
        _ExcelWriteCell($oExcel, $aList[$ib][2], "D" & $cl)  ; mfg partnumber
        _ExcelWriteCell($oExcel, $aList[$ib][3], "J" & $cl)  ; originator name
        _ExcelWriteCell($oExcel, $aList[$ib][4], "K" & $cl)  ; originator date
        
        _ExcelBookClose($oExcel)
        $prog_perc2 += 100 / Ubound($aList)
        GUICtrlSetData ($prog_bar2, $prog_perc2)
    Next
    
    _MouseTrap()
    
    MsgBox(0, "Done.", "Task is complete.")
    GUISetState (@SW_HIDE, $prog_gui)
EndFunc

Not sure if this is something you're looking for to point you in a right direction. What this function does is, it opens multiple Excel Forms, read certain cells from each form, then it creates an Excel List. You can modify this to write into a text file instead using _FileWriteFromArray() function.

Edited July 9, 2008 by aslani

Daferra · July 9, 2008

Here's an example:

Func _cList()
    WinActivate($title)
    
    Dim $aList[1][5]
    
    $i_path_1 = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)
    $i_path_2 = FileOpenDialog("Create List or Add to a List", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)")
    
    $aFiles = StringSplit($i_path_1, '|')
    
    ;_ArrayDisplay ($aFiles, "$aFiles") ; for debug
    
    $mousePos = MouseGetPos()
    
    _MouseTrap($mousePos[0], $mousePos[1], $mousePos[0], $mousePos[1])
    
    $prog_gui = GuiCreate("Please wait", 500, 70, -1, $guiloc_y-100)
    $prog_bar1 = GUICtrlCreateProgress ($offset_1, 5, 475, 10, $PBS_SMOOTH)
    $prog_bar2 = GUICtrlCreateProgress ($offset_1, 20, 475, 10, $PBS_SMOOTH)
    $prog_label = GUICtrlCreateLabel ("", $offset_1, 35, 475, 25)
    GUISetState (@SW_SHOW, $prog_gui)
    $prog_perc1 = 0
    $prog_perc2 = 0
    
    For $ia = 0 To Ubound($aFiles) - 3
        $cOrig = 26 ;26 or 28
        
        GUICtrlSetData ($prog_label, "Reading..." & @CRLF & $aFiles[1] & "\" & $aFiles[$ia+2])
        $aList[$ia][0] = _ExcelReadCell($rExcel, "C5")        ; description
        $aList[$ia][1] = _ExcelReadCell($rExcel, "C7")        ; manufacturer
        $aList[$ia][2] = _ExcelReadCell($rExcel, "C9")        ; mfg partnumber
        $aList[$ia][3] = _ExcelReadCell($rExcel, "C" & $cOrig)    ; originator name
        $ord = _ExcelReadCell($rExcel, "P" & $cOrig)                ; originator date
        _ExcelBookClose($rExcel)
        
        $ord = StringLeft(String($ord), 8)
        $ordm = StringRight(StringLeft($ord, 6), 2)
        $ordd = StringRight($ord, 2)
        $ordy = StringLeft($ord, 4)
        $aList[$ia][4] = $ordm & "/" & $ordd & "/" & $ordy
        
        ReDim $aList[UBound($aList) + 1][Ubound($aList, 2)]
        $prog_perc1 += 100 / (Ubound($aFiles) - 3)
        GUICtrlSetData ($prog_bar1, $prog_perc1)
    Next
    
    For $ib = 0 To UBound($aList) - 1
        $cl = 2
        GUICtrlSetData ($prog_label, "Writing..." & @CRLF & $i_path_2)
        Local $oExcel = _ExcelBookOpen($i_path_2, 0)
        
        While _ExcelReadCell($oExcel, "B" & $cl) <> ""
            $cl = $cl+1
        WEnd
        
        _ExcelWriteCell($oExcel, $aList[$ib][0], "B" & $cl)  ; description
        _ExcelWriteCell($oExcel, $aList[$ib][1], "C" & $cl)  ; manufacturer
        _ExcelWriteCell($oExcel, $aList[$ib][2], "D" & $cl)  ; mfg partnumber
        _ExcelWriteCell($oExcel, $aList[$ib][3], "J" & $cl)  ; originator name
        _ExcelWriteCell($oExcel, $aList[$ib][4], "K" & $cl)  ; originator date
        
        _ExcelBookClose($oExcel)
        $prog_perc2 += 100 / Ubound($aList)
        GUICtrlSetData ($prog_bar2, $prog_perc2)
    Next
    
    _MouseTrap()
    
    MsgBox(0, "Done.", "Task is complete.")
    GUISetState (@SW_HIDE, $prog_gui)
EndFunc

Not sure if this is something you're looking for to point you in a right direction. What this function does is, it opens multiple Excel Forms, read certain cells from each form, then it creates an Excel List. You can modify this to write into a text file instead using _FileWriteFromArray() function.

Aslani,

this looks like a good start, many thanks for it.

Can you help me out with the implementation for the function, eg how and with hat options do i need call it.

Many thanks,

Ferdi

Daferra · July 9, 2008

Okay guys,

The above was to hard to now for me, i start more simpler now.....

#include <ExcelCOM_UDF.au3>
#include <array.au3> ; Only for _ArrayDisplay()
#include <GUIConstants.au3>
Global $oExcel, $XLArray
$iFile = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)
$oExcel = _ExcelBookOpen($iFile)
$XLArray = _ExcelReadArray($oExcel, 2, 6, 100, 1)
_ExcelBookClose($oExcel)
#Region ### START Koda GUI section ### Form=F:\Documents and Settings\fvissers\Desktop\koda_1.7.0.1\Forms\dailer.kxf
$Form1 = GUICreate("Form1", 633, 454, 359, 309)
$Group1 = GUICtrlCreateGroup("Group1", 32, 80, 529, 121)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$XLArray = GUICtrlCreateList(" Id|Title", 32, 232, 201, 137)
GUICtrlSetData(-1, $XLArray)
$Input1 = GUICtrlCreateInput("Input1", 248, 232, 313, 21)
$Label1 = GUICtrlCreateLabel("Label1", 32, 200, 36, 17)
$Label2 = GUICtrlCreateLabel("Label2", 248, 200, 36, 17)
$Button1 = GUICtrlCreateButton("Button1", 416, 400, 65, 33, 0)
$Button2 = GUICtrlCreateButton("Button2", 496, 400, 57, 33, 0)
$Button3 = GUICtrlCreateButton("Button3", 32, 16, 169, 41, 0)
$Button4 = GUICtrlCreateButton("Button4", 32, 384, 105, 33, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
While 15
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Exit
Case $Button1
$iFile = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)
Case $Button4
_read()
Case $Button2

EndSwitch
WEnd
Func _read()
$oExcel = _ExcelBookOpen($iFile)
$XLArray = _ExcelReadArray($oExcel, 2, 6, 100, 1)
_ExcelBookClose($oExcel)
EndFunc

How can i get the array in the ctrllistview or do a refresh?

Many thanks

Ferdi

aslani · July 9, 2008

Like this?

#include <ExcelCOM_UDF.au3>
#include <array.au3> ; Only for _ArrayDisplay()
#include <GUIConstants.au3>

Global $oExcel, $XLArray

#cs - Commented these out because it's really not necessary since you can do this with your 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("Form1", 633, 454, 359, 309)
$Group1 = GUICtrlCreateGroup("Group1", 32, 80, 529, 121)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$GUIccl = GUICtrlCreateList("", 32, 232, 201, 137) ; Renamed variable name since it used to have the same name as the array variable
$Input1 = GUICtrlCreateInput("Input1", 248, 232, 313, 21)
$Label1 = GUICtrlCreateLabel("Label1", 32, 200, 36, 17)
$Label2 = GUICtrlCreateLabel("Label2", 248, 200, 36, 17)
$Button1 = GUICtrlCreateButton("Load Excel", 416, 400, 65, 33, 0) ; Renamed button 'coz I was getting confused
$Button2 = GUICtrlCreateButton("Button2", 496, 400, 57, 33, 0)
$Button3 = GUICtrlCreateButton("Button3", 32, 16, 169, 41, 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
    $nMsg = GUIGetMsg()
    
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
            
        Case $Button1
            $iFile = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)
            
        Case $Button4
            _read()
            ; added the following to propagate the list
            $list = ""
            For $x = 0 To UBound($XLArray) -1
                If $x == UBound($XLArray) -1 Then
                    $list &= $XLArray[$x]
                Else
                    $list &= $XLArray[$x] & "|"
                EndIf
            Next            
            GUICtrlSetData($GUIccl, $list)
            
        Case $Button2

    EndSwitch
WEnd

Func _read()
    $oExcel = _ExcelBookOpen($iFile, 0) ; added the 0 flag so that the Excel file won't display.
    $XLArray = _ExcelReadArray($oExcel, 2, 2, 9, 1)
    _ExcelBookClose($oExcel)
EndFunc

Edited July 9, 2008 by aslani

Daferra · July 9, 2008

Aslani,

thats great, very great thanks.

dit you see my other topic http://www.autoitscript.com/forum/index.php?showtopic=75579

I started that a our ago because the think where i was afraid for is happened now.

First i fucked up the database @ work, than after a restore i dit a wrong query in the password field in the only copy i had.

I can really heart myself for it.

So i think that a can restore the database tonight, but i need to send a lot of new codes to our customers.

Any help is appreciated...

Daferra · July 9, 2008

What i have so far.

Thanks to Aslani muttley

#include <ExcelCOM_UDF.au3>
#include <array.au3>; Only for _ArrayDisplay()
#include <GUIConstants.au3>
#Include <file.au3>

Global $oExcel, $XLArray
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

#cs - Commented these out because it's really not necessary since you can do this with your 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

;##################################
; Variables
;##################################
$s_SmtpServer = "smtp.gmail.com"                ; address for the smtp-server to use - REQUIRED
$s_FromName = "Nombre"                          ; name from who the email was sent
$s_FromAddress = "email@gmail.com"            ;address from where the mail should come
$s_ToAddress = "youremail@gmail.com"            ;destination address of the email - REQUIRED
$s_Subject = "Hi!"                            ;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
$s_Username = "youremail@gmail.com"             ;username for the account used from where the mail gets sent  - Optional (Needed for eg GMail)
$s_Password = "yourpassword"                    ;password for the account used from where the mail gets sent  - Optional (Needed for eg GMail)
$IPPort = 465                                   ;port used for sending the mail
$ssl = 1                                        ;enables/disables secure socket layer sending - put to 1 if using httpS
;~ $IPPort=465                                  ;GMAIL port used for sending the mail
;~ $ssl=1                                   ;GMAILenables/disables secure socket layer sending - put to 1 if using httpS

#Region GUI
$Form1 = GUICreate("Send new codes", 633, 454, 393, 164)
$GUIccl = GUICtrlCreateList("", 16, 24, 281, 409)
GUICtrlSetData(-1, "Edit1")
$Edit2 = GUICtrlCreateEdit("", 320, 64, 289, 273)
GUICtrlSetData(-1, "Edit2")
$Label1 = GUICtrlCreateLabel("Text to send with imported codes", 320, 24, 36, 17)
$Button1 = GUICtrlCreateButton("Import XLS", 328, 400, 81, 33, 0)
$Button4 = GUICtrlCreateButton("Propagate List", 424, 400, 73, 33, 0)
$Button3 = GUICtrlCreateButton("Button3", 512, 400, 65, 33, 0)
GUISetState(@SW_SHOW)
#EndRegion GUI


While 15
    $nMsg = GUIGetMsg()
   
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
           
        Case $Button1
            $iFile = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)
           
        Case $Button4
            _read()
           ; added the following to propagate the list
            $list = ""
            For $x = 0 To UBound($XLArray) -1
                If $x == UBound($XLArray) -1 Then
                    $list &= $XLArray[$x]
                Else
                    $list &= $XLArray[$x] & "|"
                EndIf
            Next           
            GUICtrlSetData($GUIccl, $list)
           
       ;Case $Button2

    EndSwitch
WEnd

Func _read()
    $oExcel = _ExcelBookOpen($iFile)
    $XLArray = _ExcelReadArray($oExcel, 2, 2, 9, 1)
    _ExcelBookClose($oExcel)
EndFunc

;##################################
; Script
;##################################
Global $oMyRet[2]
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

;

Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_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($as_Body,"<") and StringInStr($as_Body,">") Then
        $objEmail.HTMLBody = $as_Body
    Else
        $objEmail.Textbody = $as_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
;Authenticated SMTP
    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
;Update settings
    $objEmail.Configuration.Fields.Update
; Sent the Message
    $objEmail.Send
    if @error then
        SetError(2)
        return $oMyRet[1]
    EndIf
EndFunc;==>_INetSmtpMailCom
;
;
; Com Error Handler
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); something to check for when this function returns
    Return
EndFunc;==>MyErrFunc

copy function from excel to autoit

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members