How to organize data from excel in word using headers? Topic is solved

[aaabbbccc180]

Hi all,

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:

image.png (6.45 KiB) Viewed 727 times

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

My problem is that when this code executes, it frequently triggers VBA copying errors of some kind, at which point the program presses "enter," goes into debug mode, and everything goes off the rails.

Any suggestions? I need the code to execute reliably.

Thanks in advance!

[flyingDman]

This is not a basic question and actually it requires some knowledge of COM / VBA. The code that you proposed is prone to errors. Operating on word and excel data with simulated keystrokes is therefore not a very good idea (in a pinch it may work .... if you're lucky). For an introduction in COM refer to these:
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

In it's most basic form, the code is short enough. Let's assume that your 2 columns occupy the range A1:B18 and that in Word we want one column of data instead of paragraphs with headings:

Code: Select all

xl := comobjactive("excel.application")
oword := comobjactive("word.application")
for c in xl.activesheet.range("A1:B18")
	if c.text
		oWord.activedocument.content.insertafter(c.text "`n")

Just run the code having both Excel and Word open. The activesheet of Excel should be what you show above in A1:B18. The next step involves 1) changing the range to any range starting with the activecell and 2) insert formatted headings and paragraphs instead of plain rows.

The reason this works is because VBA reads ranges as follows.

20211129_195051.jpg (17.23 KiB) Viewed 643 times

[aaabbbccc180]

Amazing - thank you so much! It executes quickly enough that it seems I can just set the range to a large number so I don't have to worry about checking for blank cells. With slight edits I landed on this:

Code: Select all

^`::

xl := comobjactive("excel.application")
oword := comobjactive("word.application")

Send ^{Enter}									; exit edit mode

for c in xl.range(xl.ActiveCell.Address, xl.ActiveCell.Offset(500, 1))
	if c.text
		oWord.activedocument.content.insertafter(c.text "`n")

return

How would I go about fixing the formatting so that the data appears with headers? Based on how you've described the range function it seems like this can't really be done on the fly. Maybe something that compares what has just been inserted to the data in xl.range(xl.ActiveCell.Address, xl.ActiveCell.Offset(500, 0) (the column of the starting cell), selects that, and applies a style?

Thanks again for the help.

[flyingDman]

You can format in Word with Styles. Here is an example.

Code: Select all

xl := comobjactive("excel.application")
oword := comobjactive("word.application")
tl := xl.activecell.address(0,0)
br := xl.activecell.offset(0,1).end(-4121).address(0,0)
for c in xl.activesheet.range(tl ":" br)
	{
	style := (c.text && c.column = xl.activecell.column) ? -3 : -158
	oWord.Selection.Style := oWord.ActiveDocument.Styles(style)
	if c.text
		oWord.Selection.TypeText(c.text "`n")
	}

[aaabbbccc180]

That works great, except that the header style carries over to the rest of the text, so all the text ends up appearing as a header. I guess I want to apply a different style to the rest of the text. Here's my attempt at doing this but I am assuming something about this syntax is wrong:

Code: Select all

^`::	; when Control + ` is pressed

xl := comobjactive("excel.application")
oword := comobjactive("word.application")

Send ^{Enter} ; exit edit mode

for c in xl.range(xl.ActiveCell.Address, xl.ActiveCell.Offset(200, 1))
	{
	style := (c.text && c.column = xl.activecell.column) ; if the cell is in the active cell's column...
	oWord.Selection.Style := oWord.ActiveDocument.Styles("Block")	; ...apply the header style
	style := (c.text && c.column = xl.activecell.offset(0,1).column) ; if the cell is in the adjacent column...
	oWord.Selection.Style := oWord.ActiveDocument.Styles("analytic real") ; ...apply the non-header style
	if c.text
		oWord.Selection.TypeText(c.text "`n")
	}

Msgbox Done!

return

[flyingDman]

style := (c.text && c.column = xl.activecell.column) ? -3 : -158 will apply style -3 for the headings (1st col) and style -158 to the text / body (2nd col). I believe only the codes will be understood. See here where to find the codes: https://docs.microsoft.com/en-us/office/vba/api/word.wdbuiltinstyle

style := (c.text && c.column = xl.activecell.column) in your code does not do anything. Neither does this: style := (c.text && c.column = xl.activecell.offset(0,1).column).

Example of how it shows here (changed the headings). The style does not carry over to the rest of the text.

20211130_105120.jpg (15 KiB) Viewed 503 times

[aaabbbccc180]

Awesome - here's the final version.

Code: Select all

^`::											; when Control + ` is pressed

xl := comobjactive("excel.application")
oword := comobjactive("word.application")

Send ^{Enter}									; exit edit mode

for c in xl.range(xl.ActiveCell.Address, xl.ActiveCell.Offset(200, 1))
	{
	style := (c.text && c.column = xl.activecell.column) ? -4 : "analytic real"
	oWord.Selection.Style := oWord.ActiveDocument.Styles(style)
	if c.text
		oWord.Selection.TypeText(c.text "`n")
	}

Msgbox Done!

return

Really appreciate your help, you're a life-saver!

[flyingDman]

So, it seems it reads the names of the styles not just the codes. Styles that are "built-in" like "Body Text 2" and those that are custom, like your "analytic real" are recognized. Good to know.