Loop with Excel Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
gauravrana762
Posts: 8
Joined: 19 Jan 2023, 06:24

Loop with Excel

Post by gauravrana762 » 19 Jan 2023, 07:00

Hi

I want to
copy one column at a time from excel
paste it in ERP software
come back to excel copy the next column
paste it in ERP software
and so on until a blank column appears

I have written this program but I am not able to put this in a loop

Code: Select all

Clipboard := ""
send, ^c
ClipWait
if (Clipboard = "`r`n")
break
send, {Alt down}{TAB}{Alt up}
Sleep 1000
Send, ^v
Sleep 1000
Send, {enter}
Sleep 1000
send, {Alt down}{TAB}{Alt up}
send, {enter}

[Mod actions: (1) Split post to its own topic from an unrelated topic (looping in a text file). (2) Added [code][/code] tags. Please use them yourself when posting code.]

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

Re: Loop with Excel

Post by flyingDman » 19 Jan 2023, 10:54

Autohotkey has the (native) capability to interface with Excel (and other MS applications) through what is called COM (https://en.wikipedia.org/wiki/Component_Object_Model). This means that instead of simulated keystrokes, your script can issue commands (similar to that of VBA) to accomplish many tasks.
This forum has many examples of what you are trying to accomplish. Search using Google: site:autohotkey.com loop through an Excel range and you'll get thousands of results. There are also some tutorials and other sites that are useful:
https://autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/
viewtopic.php?f=7&t=8978
viewtopic.php?f=6&t=77#p495
https://docs.microsoft.com/en-us/office/vba/api/overview/excel
https://github.com/ahkon/MS-Office-COM-Basics/tree/master/Examples/Excel
The following will paste one column at a time starting with the first column of the used range. The range can be something else than the usedrange. The hotkey will do nothing if there is no more data.

Code: Select all

col := 0
xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value				; sarr is a Safearray, a special kind of array

^+v::						
res := "", ++col
if (col > sarr.maxindex(2))							; sarr.maxindex(2) is the max number of columns in the range
	return
loop, % sarr.maxindex(1)							; sarr.maxindex(1) is the max number of rows in the range
	res .= sarr[a_index,col] "`n"
send % res
14.3 & 1.3.7

User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Loop with Excel

Post by FanaticGuru » 19 Jan 2023, 14:20

gauravrana762 wrote:
19 Jan 2023, 07:00
I want to
copy one column at a time from excel
paste it in ERP software
come back to excel copy the next column
paste it in ERP software
and so on until a blank column appears

This will paste all the used columns from an open Excel sheet into the current window when you hit F12.

Code: Select all

F12::
	xlApp := ComObjActive("Excel.Application")
	for Column in xlApp.ActiveSheet.UsedRange.Columns
	{
		Column.Copy
		Send ^v
	}
return
With COM you don't need to switch windows back and forth.

Might need to tinker with some timing issues depending on the amount of data and the application it is going into. A simple test with Notepad worked fine.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

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

Re: Loop with Excel

Post by flyingDman » 19 Jan 2023, 14:51

@gauravrana762 Please clarify whether you need to paste all columns in the same location one below the other, in which case the operation can be done in one action (that's what @FanaticGuru 's script does) or if each column needs to be pasted one by one, i.e. one column each time the hotkey is pressed (that is how I understood it and what my script does; the script below shows the method to paste all column in one fell swoop).
Also note that my script does not use the Clipboard. Depending on how you use this, using the Clipboard could be an issue and it's always better to avoid it.
Spoiler
14.3 & 1.3.7

User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Loop with Excel

Post by FanaticGuru » 19 Jan 2023, 16:42

flyingDman wrote:
19 Jan 2023, 14:51
Depending on how you use this, using the Clipboard could be an issue and it's always better to avoid it.

The main advantage of using the clipboard is that it maintains formatting. Even just keeping minor formatting like italics/underline/bold/superscript/subscript can sometimes be needed. Getting only rich text from Excel is a pain. It also is usually faster than Send. But it has its downsides too. It can be unpredictable how different applications handle the data. I generally also try to avoid using the clipboard.

My script is just a framework to start from. I would assume that maybe each columns data would be pasted in a separate place. Really it is more just a demonstration of looping through columns. A bare bones answer for a bare bones request.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

gauravrana762
Posts: 8
Joined: 19 Jan 2023, 06:24

Re: Loop with Excel

Post by gauravrana762 » 20 Jan 2023, 06:45

@flyingDman

@FanaticGuru

Thanks for the help.

The thing is I am using an excel sheet and JD Edwards(ERP software). In excel sheet I have some data that I need to paste in JDE and I can only paste one column at a time.

That's why in my script I am toggling back and forth to copy and paste the data.

And will "com object" work in this scenario as JDE is not MS application.

I need to run the program till the end of the list/till a blank appears.

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

Re: Loop with Excel  Topic is solved

Post by flyingDman » 20 Jan 2023, 13:29

It should work just fine as you are just pasting the string in ERP. There is no COM link needed to ERP. Try my script with a made-up spreadsheet and a text editor.
Slightly better/shorter version:

Code: Select all

col := 0
sarr := ComObjActive("excel.application").activesheet.usedrange.value	; sarr is a Safearray, a special kind of array

^+v::
res := "", ++col
while (col <= sarr.maxindex(2) and a_index <= sarr.maxindex(1))			; maxindex(1) = row count, maxindex(2) = column count
	res .= sarr[a_index,col] "`n"
send % Rtrim(res,"`n")
14.3 & 1.3.7

gauravrana762
Posts: 8
Joined: 19 Jan 2023, 06:24

Re: Loop with Excel

Post by gauravrana762 » 25 Jan 2023, 06:04

@flyingDman

I used the script that you gave but I am not getting the desired result. I am attaching the screenshot of the excel that I tried to paste and the data pasted in JDE.

I tried pasting

1
2
3
4
5
6

but I got

1.000000
.000000
0000
00000
000

The steps that I do manually to copy the data are:

copy one column from excel (ctrl+c)
change screen to JDE (alt+tab)
paste the column(ctrl+v)
hit enter
change screen to excel (alt+tab)
move to next column

and so on till the end.
image.png
image.png (108.3 KiB) Viewed 889 times

[Mod edit: Removed redundant image.]

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

Re: Loop with Excel

Post by flyingDman » 25 Jan 2023, 14:11

As suggested earlier, try pasting it first in a text editor. It is typical for you to see it pasted like 1.000000 etc. use the round function to correct that if it is unwanted: res .= round(sarr[a_index,col],0) "`n" (line 7)
14.3 & 1.3.7

gauravrana762
Posts: 8
Joined: 19 Jan 2023, 06:24

Re: Loop with Excel

Post by gauravrana762 » 27 Jan 2023, 00:35

@flyingDman

I used the round function and I am able to paste the data in notepad correctly using the script but in JDE only the first column is getting pasted.
image.png
Screenshot
image.png (107.52 KiB) Viewed 802 times
is there anything that can be done to correct this

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

Re: Loop with Excel

Post by flyingDman » 27 Jan 2023, 01:19

If you had a second column in your spreadsheet, would it paste correctly in notepad? (press ^+v for the 1st column and ^+v again for the second column)
14.3 & 1.3.7

gauravrana762
Posts: 8
Joined: 19 Jan 2023, 06:24

Re: Loop with Excel

Post by gauravrana762 » 27 Jan 2023, 04:32

flyingDman wrote:
27 Jan 2023, 01:19
If you had a second column in your spreadsheet, would it paste correctly in notepad? (press ^+v for the 1st column and ^+v again for the second column)
I am trying to copy the entire row in JDE but with this script I can only copy first column not the entire row.

gauravrana762
Posts: 8
Joined: 19 Jan 2023, 06:24

Re: Loop with Excel

Post by gauravrana762 » 27 Jan 2023, 07:00

gauravrana762 wrote:
27 Jan 2023, 04:32
flyingDman wrote:
27 Jan 2023, 01:19
If you had a second column in your spreadsheet, would it paste correctly in notepad? (press ^+v for the 1st column and ^+v again for the second column)
I am trying to copy the entire row in JDE but with this script I can only copy first column not the entire row.
I am able to perform the steps using the below mentioned code

I have kept 300 as the maximum limit right now but the loop will break if there is a blank column

Code: Select all

loop, 300
{
send, ^c
ClipWait
if (Clipboard = "`r`n")
break
send, {Alt down}{TAB}{Alt up}
Sleep 800
Send, ^v
Sleep 800
Send, {enter}
Sleep 800
send, {Alt down}{TAB}{Alt up}
Sleep 800
Send, {enter}
Sleep 800
Send, {enter}
Sleep 800
}




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

Re: Loop with Excel

Post by flyingDman » 27 Jan 2023, 10:33

I guess you're no longer interested in pursuing the COM method... I fold.
14.3 & 1.3.7

Post Reply

Return to “Ask for Help (v1)”