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

[JKnight_xbt33]

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

[JKnight_xbt33]

@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]

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
}

}

[boiler]

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]

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 (51.13 KiB) Viewed 709 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 (34.52 KiB) Viewed 709 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

[boiler]

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]

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]

Thank you both for your answers

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

I really appreciate it
J