Excel to Excel Copy and Paste

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
dpardinas
Posts: 11
Joined: 29 Sep 2020, 19:08

Excel to Excel Copy and Paste

Post by dpardinas » 25 Jan 2022, 01:27

I thought this would be pretty easy. I've created scripts before to copy data from Excel and paste into another application by using Window Spy and clicking on pixels that have worked great!

This however, has me stumped. My problem is I have 2 spreadsheets where there isn't a unique key in each order to do a formula. There is a name in both spreadsheets, however, one is in three columns and the other is in one. And to top that off, they aren't the same exactly. One contains some middle names and the other middle initials. So I am going to have to visually look down the column in one spreadsheet and glance at the other spreadsheet for a match. Once I see a match, I make sure my focus is on the cell I want to paste the data to in the destination spreadsheet and then click on the cell I want to copy from the source spreadsheet. At that point, I simply want to copy the data in the active cell, switch to the other spreadsheet (Send, {AltTab}), paste the data into that cell and return to the original spreadsheet.

I've done a lot of searching in the posts and have tried numerous methods. My code gets messy as I comment things out and change the code. Below is most of my code with comments. I have one destination spreadsheet and several source spreadsheets to go through. Any help to speed up the copy/paste function would be helpful. Thank you in advance!

Dianne

Code: Select all

#SingleInstance force

^z:: 

file2open :="path of my source file" ; destination file is already open
XL := ComObjCreate( "Excel.Application" )

Clipboard:= ;to clear the clipboard
clipwait ; to wait for it to empty
Sendinput, ^C ; copy the email address in the active cell
clipwait
Send, {AltTab} ; move to the destination spreadsheet
ComObjActive("Excel.Application").ActiveCell.value := clipboard ; email address to excel active cell
Sendkey, {right} ;this is to move one cell to the right to accept the next step
Send, {AltTab} ; move back to the source spreadsheet
Sendkey {right} ; move to the next cell in the source spreadsheet to get the phone number

Clipboard:= ;to clear the clipboard
clipwait ; to wait for it to empty
Sendinput, ^C ; copy the email address in the active cell
clipwait
Send, {AltTab} ; move to the destination spreadsheet
ComObjActive("Excel.Application").ActiveCell.value := clipboard ; phone number to excel active cell
Send, {AltTab} ; move back to the source spreadsheet

; Visually scan down the column looking for the next match. I know this is ugly but using the script would speed up copying/pasting back and forth

When I find another match, click ^z to run it again

I've also tried using different code to do the copy/paste: 

Send {lcontrol down}c{lcontrol up}
Send {lalt down}{tab}{lalt up}
Send {lcontrol down}v{lcontrol up}
etc.
[Mod edit: [code][/code] tags added.]
Last edited by gregster on 25 Jan 2022, 01:30, edited 1 time in total.
Reason: Topic moved from AHK_H help, since the question doesn't seem specific to the AHK_H fork.

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel to Excel Copy and Paste

Post by Xeo786 » 25 Jan 2022, 07:16

You need to understand and learn what is COM object
viewtopic.php?t=29232
you will be confuse doing stuff unless you understand the difference between ComObjCreate and ComObjActive

then You need to understand and learn what is Excel COM object
viewtopic.php?t=63032

following example might help you

Code: Select all

XL := ComObjCreate( "Excel.Application" )

/*
	you can create as many xl app as many you want
	XL2 := ComObjCreate( "Excel.Application" )
	
	now XL2 can have 
*/
XL.Visible := True ; making that new XL visible 

; https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open
; workbooks
Book1 := XL.workbooks.open(xlsx_address1)
Book2 := XL.workbooks.open(xlsx_address2)

; https://docs.microsoft.com/en-us/office/vba/api/excel.sheets
; sheet 1 in book 1 by number
sheet1 := book1.sheets(1)

; sheet 2 in book 2 by name
sheet2 := book2.sheets("Sheet2")

;https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)
; access cell A1 from sheet1 book 1
cell_A1 := sheet1.range("a1")

; access cell A2 from sheet2 book 2
cell_A2 := sheet2.range("a2")

; putting data to Cell A1
cell_A1.value := "xyz"
;of you can do long way  both above and below ways are doing exactly same 
XL.workbooks(1).sheets(1).range("a1").value := "xyz"

; putting data to Cell A1
cell_A2.value := 123
;of you can do long way  both above and below ways are doing exactly same 
Book2.sheets(2).range("a2").value := 1234

;https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.cells
;exchange data from cell_A2 to cell_A1
cell_A1.value := cell_A2.value

Code: Select all

; this will brought access to already existing active application 
xlapp := ComObjActive( "Excel.Application" )
/*
	now matter how many times you do 
	xlapp1 := ComObjActive( "Excel.Application" )
	xlapp2 := ComObjActive( "Excel.Application" )
	
	it will alway put handles to already existing active application 
	; now don't need 'XL.Visible := True ; as already active application might be visible unless you create and do not make it visible. simply over complicate stuff 
*/

;this is how you can know my many workbooks xlapp has applications
msgbox, % xlapp.workbooks.length()

;you already how you can acees book one 
mybook1 := xlapp.workbooks(1)

; and if you know what is for loop you can do something like this with objects

for book in xlapp.workbooks
{
	msgbox, % "this is book " a_index " name: " book.name
}

;same for sheets 
for sheets in xlapp.workbooks(1).sheets
{
	msgbox, % "this is book 1's sheet " a_index  " name: " sheet.name
}
I hope you can now exchange data between more than one excel apps and between their all opened books and all having sheets and their entire cells
Last edited by Xeo786 on 26 Jan 2022, 01:08, edited 1 time in total.
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel to Excel Copy and Paste

Post by flyingDman » 25 Jan 2022, 12:22

I agree with @Xeo786 that you need to have a fundamental understanding of COM and in particular com for Excel. Xeo pointed to a tutorial for Com for Excel that also refers to a library of functions. This may only may be confusing the matter more for you. Get the basics first! There is nothing in the library that cannot be done without it.
@Xeo786 you mention several times wokbooks rather than workbooks. Also, xl.workbooks.count rather than xl.workbooks.length()
14.3 & 1.3.7

RussF
Posts: 1242
Joined: 05 Aug 2021, 06:36

Re: Excel to Excel Copy and Paste

Post by RussF » 25 Jan 2022, 12:38

Just a quick observation, @dpardinas, your code:

Code: Select all

Clipboard:= ;to clear the clipboard
clipwait ; to wait for it to empty
Sendinput, ^C ; copy the email address in the active cell
clipwait
Send, {AltTab} ; move to the destination spreadsheet
Should probably be:

Code: Select all

Clipboard := "" ;to clear the clipboard
Sendinput, ^C ; copy the email address in the active cell
clipwait, 1
Send, {AltTab} ; move to the destination spreadsheet
:= expects an expression after it, so give it one with ""
The clipwait after clearing the clipboard waits for something to be put INTO the clipboard, and so without a timeout, will sit there indefinitely - you don't really need it. Finally, add a timeout to the second clipwait, or it could get hung up as well if nothing was copied.

Russ

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel to Excel Copy and Paste

Post by flyingDman » 25 Jan 2022, 12:56

I would recommend not sending simulated keystrokes to Excel.
14.3 & 1.3.7

RussF
Posts: 1242
Joined: 05 Aug 2021, 06:36

Re: Excel to Excel Copy and Paste

Post by RussF » 25 Jan 2022, 13:32

@flyingDman , point taken. I have automated a number of different programs, but never had the need to mess with Excel using AHK, so have no experience with it. I was simply pointing out issues with her clipboard commands. I'll leave the COM stuff to the experts.

Russ

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel to Excel Copy and Paste

Post by Xeo786 » 26 Jan 2022, 01:07

flyingDman wrote:
25 Jan 2022, 12:22
@Xeo786 you mention several times wokbooks rather than workbooks. Also, xl.workbooks.count rather than xl.workbooks.length()
sorry I do misspell a lot in COM magic spell, I am going to correct them :think: right now
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

dpardinas
Posts: 11
Joined: 29 Sep 2020, 19:08

Re: Excel to Excel Copy and Paste

Post by dpardinas » 27 Jan 2022, 18:41

I've put in at least 3 hours reading and trying different things.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.

XL := ComObjCreate( "Excel.Application" )

;books to open
Dest := XL.workbooks.open("C:\agents.xlsx")
Source := XL.workbooks.open("C:\ROne.xlsx")

; https://docs.microsoft.com/en-us/office/vba/api/excel.sheets
; sheet 1 in book 1 by number
sheet1 := Source.sheets(1)
sheet2 := Dest.sheets(1)

Clipboard := ""

Activecell.Select.CopyCell     ;This line will copy the active cell in my source speadsheet and show it in my debug window
;Now I have to select the dest file

Sheets("Sheet2").Select    ; Call to nonexistent function
Destination:=Worksheets("Sheet2").Range.ActiveCell     ; Call to nonexistent function

;I've tried using Send, {AltTab}
;I tried making my Activecell.Select.Copycell into a variable and using 

Sheet2.ActiveCell.Paste
Sheet2.ActiveCell := var1 ;from doing var1 := activecell.select.copycell
XL.Dest.Activate
In the end, because this is going to be a visual scan, I simply want to use a hotkey to copy the value in source.activecell to dest.activecell

I've really done some research and still stuck. I would appreciate some additional guideance.

Thank you, Dianne
Last edited by BoBo on 27 Jan 2022, 18:50, edited 1 time in total.
Reason: Added [code][/code]-tags.

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel to Excel Copy and Paste

Post by Xeo786 » 28 Jan 2022, 02:15

dpardinas wrote:
27 Jan 2022, 18:41
I've put in at least 3 hours reading and trying different things.

Code: Select all

~............
In the end, because this is going to be a visual scan, I simply want to use a hotkey to copy the value in source.activecell to dest.activecell

I've really done some research and still stuck. I would appreciate some additional guideance.

Thank you, Dianne
Please read about variables and objects first

"ComObjActive" work like ....! there is already application, and you need to access / handle
"ComObjCreate" will create a new application you can use it in background or you can set is visible

Code: Select all

; idk how following line is working as I can't find its origin
Activecell.Select.CopyCell

; for example 
; following code has also missing its origin 
Sheets("Sheet2").Select 

;sheet function originate from workbook but following line would not work because workbooks method originate from excel.application
workbooks(1).Sheets("Sheet2").Select

;for example here we are putting "Excel.Application" into variable XL
XL := ComObjCreate( "Excel.Application" )

; here we are using XL method .workbooks() and .open() is also method from .workbooks()
;.open() opens a file using file address 
 
Dest := XL.workbooks.open("C:\agents.xlsx")
; dest is the variable which contain the workbook handle

; if we need to access sheet 2 of dest we need to call .sheets() method from .workbooks() and dest has workbook which we need
sheet2 := Dest.sheets("sheet2")

; .range() is the method from .sheets()  which grant access one of more cells 

; if we want to access cell B3 from from Sheet2 of workbook Dest so... we can do this in short way 
b3cell := sheet2.range("B3")

; as we know Sheet2 is from Dest which contains Workbook we opened and Dest is from Xl which contain excel app
; so both following lines are same
b3cell := Dest.sheets("sheet2").range("B3")
b3cell := sheet2.range("B3")
I hope I am able to define stuff properly as I am also a self taught guy :)

well following example will exchange data between two workbooks

Code: Select all

XL := ComObjCreate( "Excel.Application" )

;books to open
Dest := XL.workbooks.open("C:\agents.xlsx")
Source := XL.workbooks.open("C:\ROne.xlsx")

sheet1 := Source.sheets(1)
sheet := Dest.sheets(1)

; here we are maving value of source's sheet 1 cell a5 to dest sheet 1 cell a1
sheet.range("a1").value := sheet1.range("a5").value

; copy paste is a manual way to move data like value color formatting and so many other information between but we can also automate copy paste as these are also methods .copy() .paste() 
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

Post Reply

Return to “Ask for Help (v1)”