How to copy text from a cell in a excel spreadsheet and paste into a different window

[les317]

Hi all. This is my first post here and I am very new to AHK. I find it extremely fascinating as I discover how powerful a tool it is. I have a task.

I would like to write a hotkey script that is used when active in any text box on any webform. Below is just an example of how it should work.

The hotkey will only be triggered when in a text field on a webform. Let us say #a is the hotkey.
When triggered, an excel window (Test - Excel) becomes active (which is already open) .
The specific sheet is selected "Sheet1".
A particular cell A1 is selected, and it''s contents copied to a clipboard.
The active window then switches back to the text box, to where the hotkey was originally triggered, and then pastes the contents of A1 into the text box.

For example, A1 = John (First Name), A2 = Smith (Surname), A3 = [email protected]

Thus, creating 3 hotkeys, one for each field. I am not trying to create something like Keepass, but rather trying to understand the scripting that achieves the objectives, which is to find a particular excel window, select a cell, copy the contents of that cell, change the active window back to the text window and then finally paste the contents into the text window.

It seems like a simple task, and yet through all the reading that I have done over the past week on this forum, and youtube videos, I have not found a solution.
I hope that someone here will be able to shed some light on this.

Thank you for your help in advance.

[kon]

It's possible to do what you describe by using the WinActivate command to switch windows. The window title / class etc can be found using the included Window Spy utility. You could select the cell by sending keystrokes like arrow keys, Home etc. Then you could use Send, ^c to copy the cell, Switch back to the other window, and Send, ^v to paste the value.

Alternatively this could be done without the need to switch windows or use the clipboard. This reads the value directly from the workbook file.

Code: Select all

#a::
; Gets a reference to the workbook
; Change the path to the path of your workbook
wbk := ComObjGet("C:\MyFolder\Book1.xlsx")

; Gets the value of cell 1, 1 (also known as cell A1) from Sheet1
A1Val := wbk.Sheets("Sheet1").Cells(1, 1).Value

SendInput, % A1Val
return

[les317]

Hi..
I have just tried the script, and it works perfectly. So simple and works without having to even open the xls file.
Thank you muchly.

[aandrewb]

Hi Kon,

I would like if possible to automate copying cells from Excel to a web based application using ComObjGet.
Basically I got a worksheet with two columns A & B.
I want to copy a number in cell A2 and then paste it into a web based application and then copy a postcode from cell I would like if possible to automate copying cells from Excel to a web based application using ComObjGet.
Basically I got a worksheet with two columns A & B.
I want to copy a number in cell A2 and then paste it into a web based application and then copy a postcode from cell B2 and paste it in to the same web application different field.
Then goto cell A3 and cell B3 and so on until a blank cell is encounted in column A and stop.d so on until a blank cell is encounted in column A and stop.

Thanks Andrew.

[kon]

Try this:

Code: Select all

; This script gets a range of cells in column A and B. (Like selecting cell A2 and then pressing Ctrl+Shift+Down and
; then Shift+Right.) Then each time the Ctrl+F12 hotkey is pressed it sends the next value.

; Usage:
; Press Ctrl+F12 to send the next value.

; Constants
xlDown := -4121

WorkbookPath := A_ScriptDir "\MyWorkbook.xlsx"       ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application")                            ; Create an instance of Excel
xlApp.Visible := true                                                          ; Make Excel visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath)                                ; Open the workbook
CellA2 := xlApp.Cells(2, 1)                                          ; Store a reference to cell A2
LastCell := CellA2.End(xlDown).Offset(0, 1)  ; Store the last cell. .End is like pressing Ctrl+Down
MyRange := xlApp.Range(CellA2, LastCell)               ; Store a reference to the Range A2:LastCell
CellNumber := 1                                   ; This variable will store the cell number to use
CellCount := MyRange.Cells.Count                            ; Store the count of cells in the range
return

^F12::                                                                            ; Ctrl+F12 hotkey
    SendRaw % MyRange.Cells(CellNumber).Text      ; Send the current cell specified by 'CellNumber'
    CellNumber++                                                     ; Increase 'CellNumber' by one
    if (CellNumber > CellCount) {    ; If 'CellNumber' is greater than the total amount of cells...
        MsgBox, 64, Info, Finished. No more cells.                                           ; Done
        CellNumber := 1
    }
return

; References
; https://autohotkey.com/boards/viewtopic.php?p=112648#p112648
; https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Cells_in_a_column.ahk

[aandrewb]

Thank you for your reply.

I won't be able to test your script until I get to work tomorrow, I will let you know if it works.

I run AHK from a USB memory stick and on my home computer I can right click on my desktop and create and edit scripts, on my work computer I can't do that, do you know any reason why?

[kon]

'Right-click>New>AutoHotkey Script' is only available if you install AHK (as opposed to using the portable version). The installer adds a script template to the %WINDIR%\ShellNew folder and also sets some registry keys.

[coolsam]

HI
am using ur script and it works fantastic.
i just wanted to know how to alter ur script if i want to copy cells A2 first, then C2 and then copy value of B2 ..?? and repeat the next value in the same order
thanks



Try this:

Code: Select all

; This script gets a range of cells in column A and B. (Like selecting cell A2 and then pressing Ctrl+Shift+Down and
; then Shift+Right.) Then each time the Ctrl+F12 hotkey is pressed it sends the next value.

; Usage:
; Press Ctrl+F12 to send the next value.

; Constants
xlDown := -4121

WorkbookPath := A_ScriptDir "\MyWorkbook.xlsx"       ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application")                            ; Create an instance of Excel
xlApp.Visible := true                                                          ; Make Excel visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath)                                ; Open the workbook
CellA2 := xlApp.Cells(2, 1)                                          ; Store a reference to cell A2
LastCell := CellA2.End(xlDown).Offset(0, 1)  ; Store the last cell. .End is like pressing Ctrl+Down
MyRange := xlApp.Range(CellA2, LastCell)               ; Store a reference to the Range A2:LastCell
CellNumber := 1                                   ; This variable will store the cell number to use
CellCount := MyRange.Cells.Count                            ; Store the count of cells in the range
return

^F12::                                                                            ; Ctrl+F12 hotkey
    SendRaw % MyRange.Cells(CellNumber).Text      ; Send the current cell specified by 'CellNumber'
    CellNumber++                                                     ; Increase 'CellNumber' by one
    if (CellNumber > CellCount) {    ; If 'CellNumber' is greater than the total amount of cells...
        MsgBox, 64, Info, Finished. No more cells.                                           ; Done
        CellNumber := 1
    }
return

; References
; https://autohotkey.com/boards/viewtopic.php?p=112648#p112648
; https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Cells_in_a_column.ahk

[Leon_Pence]

Hi Everyone,

I'm trying to make a script that will allow copy from an excel sheet onto a searchbox in a website then search the page for the specific word to stop the script if the word is found on the page and if not to continue to the next cell on excel sheet and to repeat the script.

Copy number from excel cell onto clipboard → Go to active webpage → Highlight & Delete content in search box → Paste clipboard content → Search by clicking enter → search page for "TAG NOT FOUND" → → If term not found return to excel sheet and move to next cell → repeat script
→ If term found stop/pause script

Is a script like this possible for AHK or is it too complex? Does anyone have a script similar to what I am looking to try to make?

[eblanc]

Hi Everyone,

I'm trying to make a script that will allow copy from an excel sheet onto a searchbox in a website then search the page for the specific word to stop the script if the word is found on the page and if not to continue to the next cell on excel sheet and to repeat the script.

Copy number from excel cell onto clipboard → Go to active webpage → Highlight & Delete content in search box → Paste clipboard content → Search by clicking enter → search page for "TAG NOT FOUND" → → If term not found return to excel sheet and move to next cell → repeat script
→ If term found stop/pause script

Is a script like this possible for AHK or is it too complex? Does anyone have a script similar to what I am looking to try to make?

I've been using Pixelgetcolor to check if a specific pixel in the screen has to change to a specific color. This way, the computer is able to have some "vision" on the screen and check if pages have been loaded.

I think depending on the browser you're using the options available you have for commands. STuff like reading certain text on the window tab.

This is what I've been using to check if pages are loaded. Make sure you get the right are pixel. As well as the correct RGB value in color.

Code: Select all

;Is page loaded?
sleep, 200
start := a_tickcount
while ((a_tickcount - start) < 5000)
{ 
    sleep, 200
    PixelGetColor,pColor,879,504,RGB
    if (pColor = "0xCCCCCC") 
	{
    break
    }
}

[Leon_Pence]

Thank you for your reply I will try this when I am able to get to my laptop with ahk!

[eblanc]

Thank you for your reply I will try this when I am able to get to my laptop with ahk!

how did it go?