Clipget() Question

[phils]

I'm a new user of AutoIT, not a programmer, and would appreciate some help.

The script I am working on takes an Excel mailing list (Record number is in column A), and loads it into a web form to search for the name. If it finds the name it marks the excel record so it can be later removed from the mailing list.

I'm using ClipGet() to copy the cell contents into a variable. For some reason the variable that ClipGet() is equal to retrieves the contents of the cell about 90% of the time. When it doesn't work the variable is set to 1. I've turned on the office clipboard in Excel (Edit, Office Clipboard) so I can see the clipboard contents. When it doesn't work, the clipboard is showing the correct information, but for some reason it is not being transferred to the variable. I've inserted the first part of the script below where the problem first appears. Here it finds the total number of records by going to the end of column A. I use the total record number to track the script process (i.e. working on record 6 of 1223 records, etc).

Any help would be appreciated. Any AutoIT experts in the Salt Lake City, Utah vicinity?

phil@farmersutah.com

;;Last Changes on 04-06-2006

Global $ExcelFname

Global $ExcelTitle

Local $totalrec=0

$ExcelFname = "11-2005Phil.xls" ; Excel filename used by script.

$ExcelTitle = "Microsoft Excel - " & $ExcelFname ; Used by Winwait, etc.

ClipPut("") ;Clears Clipboard memory

opt("WinWaitDelay", 100)

opt("WinTitleMatchMode", 4)

opt("WinDetectHiddenText", 1)

opt("MouseCoordMode", 0)

; Press Esc to terminate script, Pause/Break to "pause"

Global $Paused

HotKeySet("{PAUSE}", "TogglePause")

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

;;;;;;;START EXCEL WORKSHEET ROUTINE;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;ExcelTitle variable set at program beginning.

WinWait($ExcelTitle, "Worksheet Menu Bar")

If Not WinActive($ExcelTitle, "Worksheet Menu Bar") Then WinActivate($ExcelTitle, "Worksheet Menu Bar")

WinWaitActive($ExcelTitle, "Worksheet Menu Bar")

;Gets number of records from last row of column A.

Send("^{home}") ;Goes to Upper Leftmost cell.

Send("{END}{DOWN}") ;Goes to last row.

Send("{CTRLDOWN}c{CTRLUP}") ;Copies record number to clipboard.

;Send("{ALTDOWN}e{ALTUP}c") ;Another method of copying to clipboard)

;######## Occasionally $totalrec isn't set equal to the clipboard contents, it is set to 1. WHY???????

$totalrec = ClipGet() ;Loads clipboard into variable $totalrec.

$totalrec = StringStripWS($totalrec, 8) ;Removes trailing spaces

Send("^{home}") ;Goes back to Upper Leftmost cell.

ToolTip("" & $totalrec,25,255) ;H, V Displays $totalrec contents for troubleshooting purposes.

sleep(2000)

exit 0

[pecloe]

a silly question, what is the difference between cells that fail and those that don't? can you give examples?

Edited April 6, 2006 by pecloe

[seandisanti]

I'm a new user of AutoIT, not a programmer, and would appreciate some help.

The script I am working on takes an Excel mailing list (Record number is in column A), and loads it into a web form to search for the name. If it finds the name it marks the excel record so it can be later removed from the mailing list.

I'm using ClipGet() to copy the cell contents into a variable. For some reason the variable that ClipGet() is equal to retrieves the contents of the cell about 90% of the time. When it doesn't work the variable is set to 1. I've turned on the office clipboard in Excel (Edit, Office Clipboard) so I can see the clipboard contents. When it doesn't work, the clipboard is showing the correct information, but for some reason it is not being transferred to the variable. I've inserted the first part of the script below where the problem first appears. Here it finds the total number of records by going to the end of column A. I use the total record number to track the script process (i.e. working on record 6 of 1223 records, etc).

Any help would be appreciated. Any AutoIT experts in the Salt Lake City, Utah vicinity?

phil@farmersutah.com

;;Last Changes on 04-06-2006

Global $ExcelFname

Global $ExcelTitle

Local $totalrec=0

$ExcelFname = "11-2005Phil.xls" ; Excel filename used by script.

$ExcelTitle = "Microsoft Excel - " & $ExcelFname ; Used by Winwait, etc.

ClipPut("") ;Clears Clipboard memory

opt("WinWaitDelay", 100)

opt("WinTitleMatchMode", 4)

opt("WinDetectHiddenText", 1)

opt("MouseCoordMode", 0)

; Press Esc to terminate script, Pause/Break to "pause"

Global $Paused

HotKeySet("{PAUSE}", "TogglePause")

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

;;;;;;;START EXCEL WORKSHEET ROUTINE;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;ExcelTitle variable set at program beginning.

WinWait($ExcelTitle, "Worksheet Menu Bar")

If Not WinActive($ExcelTitle, "Worksheet Menu Bar") Then WinActivate($ExcelTitle, "Worksheet Menu Bar")

WinWaitActive($ExcelTitle, "Worksheet Menu Bar")

;Gets number of records from last row of column A.

Send("^{home}") ;Goes to Upper Leftmost cell.

Send("{END}{DOWN}") ;Goes to last row.

Send("{CTRLDOWN}c{CTRLUP}") ;Copies record number to clipboard.

;Send("{ALTDOWN}e{ALTUP}c") ;Another method of copying to clipboard)

;######## Occasionally $totalrec isn't set equal to the clipboard contents, it is set to 1. WHY???????

$totalrec = ClipGet() ;Loads clipboard into variable $totalrec.

$totalrec = StringStripWS($totalrec, 8) ;Removes trailing spaces

Send("^{home}") ;Goes back to Upper Leftmost cell.

ToolTip("" & $totalrec,25,255) ;H, V Displays $totalrec contents for troubleshooting purposes.

sleep(2000)

exit 0

there is a better way to do what you want, COM. i can help you with that, it will make everything easier, and cut your code alot. another thing i've done alot of times that works well when migrating data from excel to another app, and vice versa, is integrating VBA scripts in excel with autoit scripts. an example is a script i did for our QA department, they have a list of agent names in excel, they hit a hotkey in excel, and then for each name in the list, an autoit script is called, passing the name, and the date needed etc, then the VBA script grabs the data it needs from the clipboard, and summary reports are created for lists of agents based on data that has to be retrieved per agent. you can eliminate VBA completely by just using COM, but they wanted to have the formatting of the spreadsheet done in vba, so i just wrote up half the code there.

***edit***

oh yeah and as far as proximity to SLC, i'm about 10 hours away in vegas.

***edit***

p.p.s. welcome to the forum.

Edited April 6, 2006 by cameronsdad

[phils]

there is a better way to do what you want, COM. i can help you with that, it will make everything easier, and cut your code alot. another thing i've done alot of times that works well when migrating data from excel to another app, and vice versa, is integrating VBA scripts in excel with autoit scripts. an example is a script i did for our QA department, they have a list of agent names in excel, they hit a hotkey in excel, and then for each name in the list, an autoit script is called, passing the name, and the date needed etc, then the VBA script grabs the data it needs from the clipboard, and summary reports are created for lists of agents based on data that has to be retrieved per agent. you can eliminate VBA completely by just using COM, but they wanted to have the formatting of the spreadsheet done in vba, so i just wrote up half the code there.

***edit***

oh yeah and as far as proximity to SLC, i'm about 10 hours away in vegas.

***edit***

p.p.s. welcome to the forum.

You are way over my head. What is COM?

[phils]

a silly question, what is the difference between cells that fail and those that don't? can you give examples?

Here's the way it is supposed to work. Once the excel cell contents are in a variable I can use the Send command to paste it into another application.

Cell Contents > Clipboard > $totalrec

1223 1223 1223

10% or more of the time the cell contents make it to the clipboard and stops. The data doesn't get transferred from the clipboard to the variable and for some reason the variable is set to 1.

Cell Contents > Clipboard | $totalrec

1223 1223 1

Doesn't make for a very useful application where I occasionally get 1's instead of the data I want.

[neogia]

@phils: I've had a similar problem with retrieving data from a program using the clipboard. The problem lies between these two lines:

Send("{CTRLDOWN}c{CTRLUP}")
$totalrec = ClipGet()

Think of it this way: when the first line sends Ctrl+C (which btw can be shortened to Send("^c")), the program Excel then has to catch and interpret those keys to copy it's data to the clipboard. Meanwhile, your script has already sent the keys, and frankly doesn't care what Excel has done with them, so your script moves to the next line, grabbing whatever is currently in the clipboard, which could be what was in there previously, or something totally different if the clipboard is currently being written to. Here's how I take care of that little problem.

ClipPut("<nil>");Something to check against
Send("^c");Copy text
$totalrec = ClipGet()
While $totalrec == "<nil>"
    $totalrec = ClipGet()
WEnd

That way, you're script pauses until Excel interprets the keys, and copies its contents to the clipboard. Now even though this should solve your direct problem, cameronsdad's recommendation of using COM is definitely faster, and worth learning. My solution should be used more generally, though, in cases where you can't use COM. Happy coding!

[seandisanti]

You are way over my head. What is COM?

idea. can you attach a copy of your spreadsheet, or even a sample sheet that's setup the same way. where do you want the data to go to? i mean what is the name of the program etc, or do you have that part already working if the data makes it to the clipboard?