Simple Excel Copy function error out

[meatsack]

I looked through the sample scripts but just can't figure it out.

Is it a failure to include or have in my files the "ExcelCOM_UDF.au3" module?

#include <Excel.au3>
#include <ExcelCOM_UDF.au3>

$sFilePath = "c:\test.xls"
$sFile1 = "test.xls"

$oExcel = _ExcelBookOpen($sFilePath, 0, 0)
  
_ExcelCopy($oExcel, A1:C5)

Or I could use the old way, right?

#include <ExcelCOM_UDF.au3>

$oExcel.Application.Workbooks($sFile1).Sheets("Sheet1").Select

$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy

but this doesn't get it on the clipboard, right? At least it hasn't for me after it runs w/o errors there is nothing on the clipboard.

If it can go to a msg box why can't it go to the clipboard?

THanks in advance for any help.

Edited February 15, 2010 by meatsack

[meatsack]

Here is a draft, non working version of what I am trying to do. I just can't get Excel cell values to copy to the clipboard so that I can than dump the clipboard contents into the body of an email. This is going to be fun. No searching through Word Sample scripts I found out how to copy and paste from a word document but unfortunately for me word and excel have different command names.

"

expandcollapse popup

;

;##################################

; Include

;##################################

#Include<file.au3>

;##################################

; Variables

;##################################

#include <Excel.au3>



$sFilePath = "c:\test.xls"
$sFile1 = "test.xls"
Global Const $oSheetFic1 = "Sheet1"


$oExcel = _ExcelBookOpen($sFilePath, 0, 0)



$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Select
$oExcel.ActiveWorkbook.Sheets($oSheetFic1).Copy

_ExcelCopy()

$Temp =  ClipGet ()

 

$SmtpServer = "smtp.gmail.com"              ; address for the smtp-server to use - REQUIRED

$FromName = "JohnDoe@gmail.com"                      ; name from who the email was sent

$FromAddress = "JohnDoe@gmail.com" ; address from where the mail should come

$ToAddress = "JaneDoe@gmail.com"   ; destination address of the email - REQUIRED

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

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

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

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

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

$Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"

$Username = "JohnDoe"                    ; username for the account used from where the mail gets sent - REQUIRED

$Password = "mypassword"                  ; password for the account used from where the mail gets sent - REQUIRED

$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

 

;##################################

; Script

;##################################

Global $oMyRet[2]

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

$rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)

If @error Then

    MsgBox(0, "Error sending message", "Error code:" & @error & "  Description:" & $rc)

EndIf

;

; The UDF

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

    Local $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])

            ConsoleWrite('@@ Debug(62) : $S_Files2Attach = ' & $S_Files2Attach & @LF & '>Error code: ' & @error & @LF) ;### Debug Console

            If FileExists($S_Files2Attach[$x]) Then

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

            Else

                ConsoleWrite('!> File not found to attach: ' & $S_Files2Attach[$x] & @LF)

                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

    If Number($IPPort) = 0 then $IPPort = 25

    $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

    ; Set email Importance

    Switch $s_Importance

        Case "High"

            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High"

        Case "Normal"

            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal"

        Case "Low"

            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low"

    EndSwitch

    $objEmail.Fields.Update

    ; Sent the Message

    $objEmail.Send

    If @error Then

        SetError(2)

        Return $oMyRet[1]

    EndIf

    $objEmail=""

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

 


"

[enaiman]

You are complicating and over-complicating a simple matter.

There are function already made to suit such purposes: _ExcelReadCell and _ExcelReadSheetToArray (included in Excel.au3)

One of them returns the data from that cell, the other one returns an array. You DON'T NEED to have anything put in the clipboard and paste it from there - you have the data in a variable ready to be used wherever you need.

I don't have time to debug your script and even if I had the time I wouldn't do it. Your script is simply too big and messy to clearly understand your issue. Consider building a small sample script to replicate your problem whenever you're going to ask for help; sometimes that even manages to solve the matter.

Have a look at the examples in the help associated with these 2 functions; they contain everything you need to get your script working.

I can see you've spent some time working on this issue and that's why I decided to help - your script "evolution" shows that you are thinking "inside the box"; you're way too focused to write your script the way you started to even consider alternate way.

About the way you want to "dump everything in the clipboard" ... try instead to build a string to hold all the info, then use that string.

Basically your script needs to do:

- open excel file

- select active sheet

- read desired cell

- use the data returned to fill your email body field

- close Excel workbook

If you need a range of cells, simply read the whole sheet to an array and select the values you need out of that array.

Good luck

Edited February 15, 2010 by enaiman

[meatsack]

You are complicating and over-complicating a simple matter.

There are function already made to suit such purposes: _ExcelReadCell and _ExcelReadSheetToArray (included in Excel.au3)

One of them returns the data from that cell, the other one returns an array. You DON'T NEED to have anything put in the clipboard and paste it from there - you have the data in a variable ready to be used wherever you need.

Ok, thanks that will be great. Based on what your saying I will just play around with making a variable that is filled with the data then dump the data out using that same variable into the body of the email. I can just avoid the clipboard. BTW is there a way to see what data my script has in that specific variable in real time? Is that some part of the debugging tools, like a window where I can see it fill the variable, zero it out, fill it again, zero it out, manipulate it around? Or do I just have to use a message box and then delete the message box after I have finalized my script?

[enaiman]

You don't have a real-time monitor for variables but you can use plenty of Message Boxes or ConsoleWrite (when running the script from editor) to display the content of that variable.

That's how the debugging is done