Sorry if this question is a little basic - I'm a complete novice.
I'm looking to move data from an excel sheet and organize it in an already opened word window with headers. For any cursor starting position in the excel sheet, I want the column the cursor is in to become a header, and put the data directly to the right underneath that header, until an empty cell is reached in excel.
In other words, from the following in excel:
To the following in word:
1
a
b
c
2
a
b
c
3
a
b
c
4
a
b
c
... and so on.
This has to work in any column (if the cursor starts in D, then D provides header data, and E provides subheader data, and so on), and with different amounts of subheader data (there might be two items between 1 and 2, or four items, or twenty).
I attempted to do this without COM objects - that seemed to be the more complicated route. I came up with this:
Code: Select all
^!9:: ; when Control + Alt + 9 is typed
oldClipboard := ClipboardAll ; save the (real) clipboard
Clipboard = ; erase the clipboard first, or else ClipWait does nothing
Loop, ; start an infinite loop
{
Sleep, 100
Send, ^c ; copy header data
ClipWait, 2, 1 ; wait 1s until the clipboard contains any kind of data
if (Clipboard = "`r`n") ; if the last copied item contains only empty excel data, break the loop
break
WinActivate, ahk_exe winword.exe
Send, {Enter}
Send, {F6} ; trigger macro for header style in word
Sleep, 100
Send, {F2} ; trigger paste with merged formatting macro in word
Send, {F12} ; trigger clear formatting macro in word
WinActivate, ahk_exe excel.exe
Sleep, 100
Send, ^+{Down} ; select header data and blank cells below it
Send, +{Up} ; deselect the next header
Sleep, 100
Send,!2 ; trigger excel macro to shift selection to the right
Sleep, 100
Send, ^c
WinActivate, ahk_exe winword.exe
Send, {Enter}{Enter}
Send, {F7} ; trigger macro for subheader style in word
Send, {F2} ; trigger paste with merged formatting macro in word
Send, {Enter}{Enter}
WinActivate, ahk_exe excel.exe
Send, {Escape}
Send, {Left}
Send, ^{Down} ; move cursor to next header data
Send, ^c ; copy it
ClipWait, 2, 1 ; wait 1s until the clipboard contains any kind of data
if (Clipboard = "`r`n") ; if the last copied item contains only empty excel data, break the loop
break
}
Clipboard := oldClipboard ; restore old (real) clipboard
return
Any suggestions? I need the code to execute reliably.
Thanks in advance!