Page 1 of 1

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

Posted: 30 Oct 2020, 12:04
by 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

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

Posted: 11 Nov 2020, 06:11
by 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

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

Posted: 13 Nov 2020, 06:58
by 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
}

}


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

Posted: 13 Nov 2020, 08:57
by 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.

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

Posted: 13 Nov 2020, 09:45
by 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
test excel data.PNG (51.13 KiB) Viewed 88 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 88 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

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

Posted: 13 Nov 2020, 10:33
by 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

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

Posted: 13 Nov 2020, 12:40
by 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
}

}

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

Posted: 16 Nov 2020, 03:53
by JKnight_xbt33
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