how to paste values of an excel range iteratively rather than all at once Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
JKnight_xbt33
Posts: 112
Joined: 18 Sep 2019, 02:06

how to paste values of an excel range iteratively rather than all at once

Post by JKnight_xbt33 » 30 Oct 2020, 12:04

Hi all,
I have a script below.

The idea is a matching PDF file is opened if it contains the same number from range A2:A3

The 2nd part of the script where I am stuck. There is supposed to be a ctrl f search in the file for a serial no which is from D2:D3

The problem is when it comes to pasting this range D2:D3 into the ctrl f search bar, it pastes all the serial numbers in the range.

I only want it to paste the serial number in cell D2 rather than pasting both D2 and D3 serial numbers. Then the script would loop back to open the pdf matching A3 and repeat the action by pasting D3

Is there a way to paste each cell in the D-range one at a time?

Code: Select all

file2open := "C:\test\test excel data.xlsx"
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)

for Cell in XL.ActiveSheet.range("A2:A3")
{
	run, % "C:\test\" . Cell.Value . " Customer Complaint Report.pdf"
	sleep, 3000
	Send ^{f}
	sleep, 1000
	
{
	for Cell in XL.ActiveSheet.range("D2:D3")
	Paste(Cell.Value)          ;this is where I am stuck, I dont want all of the values from range D2:D3 to be pasted 
	sleep, 1000
	Sendinput, {Enter}
	sleep, 1000
	Sendinput, {Enter}
	sleep, 5000
}
}

return

Esc::ExitApp  ; Exit script with Escape key	

Paste(text) {
	Clipboard := text
	Send, {Control down}v{Control up}
	sleep, 500
	}
Appreciated
J
Last edited by JKnight_xbt33 on 11 Nov 2020, 06:12, edited 1 time in total.
JKnight_xbt33
Posts: 112
Joined: 18 Sep 2019, 02:06

Re: how to specify Excel Cell.Value to paste only one cell at a time from the range

Post by JKnight_xbt33 » 11 Nov 2020, 06:11

@boiler your advice has been invaluable in my learning about excel and AHK.

Is there a way to paste the values of an excel range iteratively? rather than all at once

best
JKnight_xbt33
Posts: 112
Joined: 18 Sep 2019, 02:06

Re: how to paste values of an excel range iteratively rather than all at once

Post by JKnight_xbt33 » 13 Nov 2020, 06:58

An update on this I found a script on the forum: https://www.autohotkey.com/boards/viewtopic.php?t=48981
which I incorporated principles from. I am now able paste 1 row rather than all rows from column D by using "break"

All that's left is for me to try and make a loop after each iteration so that the next row in column D is copied and pasted.

Code: Select all


file2open := "C:\test\test excel data.xlsx"
arr := {}
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)


for Cell in XL.ActiveSheet.range("A1:A2")
{
	run, % "C:\test\" . Cell.Value . " Customer Complaint Report.pdf"
	sleep, 3000
	Send ^{f}
	sleep, 1000

{    
	For Cell in XL.Columns("D").Cells
	if Cell.Value
	break
	Paste(Cell.Value)
	sleep, 1000
	Sendinput, {Enter}
	sleep, 1000
	Sendinput, {Enter}
	sleep, 5000
}

}

User avatar
boiler
Posts: 6592
Joined: 21 Dec 2014, 02:44

Re: how to paste values of an excel range iteratively rather than all at once

Post by boiler » 13 Nov 2020, 08:57

Sorry. I haven't really understood what you are looking to accomplish, which is why I was leaving it for others who might suggest a solution.
JKnight_xbt33
Posts: 112
Joined: 18 Sep 2019, 02:06

Re: how to paste values of an excel range iteratively rather than all at once

Post by JKnight_xbt33 » 13 Nov 2020, 09:45

Sorry yeah its quite a complex script.

In case anyone else sees the thread this is what I am trying to do with an example:
test excel data.PNG
test excel data.PNG (51.13 KiB) Viewed 75 times
1. Cell A1 contains the number 2913
2. The script looks for a pdf report in the test folder which has 2913 in its filename
test folder.PNG
test folder.PNG (34.52 KiB) Viewed 75 times
3. the script opens this pdf and does a ctrl f search
4. cell D1 contains the serial number 333444555
5. The script pastes D1 into the ctrl f search bar in mumber 3 above
6. The script sends enter twice to see if there is a 2nd instance of this serial number

7. The script then loops back to repeat the process with cell A2 and D2 and so on...

The goal of the script is to automate the process of going back and forth between the excel file and ctrl f searching the serial no. Especially as I am doing it for hundreds of cases.

best
J

p.s. @flyingDman I don't know what you think of this excel script.
thanks
User avatar
boiler
Posts: 6592
Joined: 21 Dec 2014, 02:44

Re: how to paste values of an excel range iteratively rather than all at once

Post by boiler » 13 Nov 2020, 10:33

It sounds like what would help you is to use .offset as in this example which will display the values of the A and D columns for rows 1 to 3:

Code: Select all

xl := ComObjActive("Excel.Application")

for cell in xl.Range("A1:A3")
	MsgBox, % "Value of " cell.address ": " cell.value "`nValue of " cell.offset(0,3).address ": " cell.offset(0,3).value
Tyrer
Posts: 15
Joined: 05 Oct 2013, 02:03

Re: how to paste values of an excel range iteratively rather than all at once  Topic is solved

Post by Tyrer » 13 Nov 2020, 12:40

Following on from that, does this work?

Code: Select all

file2open := "C:\test\test excel data.xlsx"
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)


for Cell in XL.ActiveSheet.range("A1:A3")
{
	run, % "C:\test\" . Cell.Value . " Customer Complaint Report.pdf"
	sleep, 3000
	Send ^{f}
	sleep, 1000
	Clipboard := cell.offset(0,3).value
        sleep, 1000
	send, ^v
	sleep, 1000
	Sendinput, {Enter}
	sleep, 1000
	Sendinput, {Enter}
	sleep, 5000
}

}
JKnight_xbt33
Posts: 112
Joined: 18 Sep 2019, 02:06

Re: how to paste values of an excel range iteratively rather than all at once

Post by JKnight_xbt33 » 16 Nov 2020, 03:53

Thank you both for your answers :bravo:

That's amazing, I've learnt about this offset concept and it will be immensely helpful in future.

I really appreciate it
J
Post Reply

Return to “Ask For Help”