Sign in to follow this  
Followers 0
Daferra

copy function from excel to autoit

10 posts in this topic

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

Share this post


Link to post
Share on other sites



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.


Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by aslani

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by aslani

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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

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