Send entire Row from Excel using COM, how? Topic is solved

[Krd]

Hey everyone!

I have the following that I would like to get working, but how: ?

Code: Select all

Static count := 0
exl := ComObjActive("excel.application")
while (MyVar := exl.activesheet.range("A" . ++cnt).text)
	{
	Function(MyVar)	
        Send exl.activesheet.range("A1").text  ;How to send the entire Row A 1 which can be 10+ columns here not only column1
        Send exl.activesheet.range("A" . cnt).text    ;How to send the entire current Row A  which can be 10+ columns here not only column1
        }

[Datapoint]

Try this. If you uncomment the MsgBox it should show you what some of the individual components are doing.

Code: Select all

F3:: {
	static cnt := 0
	static xlToLeft := -4159

	exl := ComObjActive("Excel.Application")
	while (current_A_Cell := exl.ActiveSheet.Range("A" . ++cnt)).Text {
		/*
		MsgBox ">" current_A_Cell.Text "`n>" .
			current_A_Cell.Row "`n>" .
			exl.Columns.Count "`n>" .
			exl.Cells(current_A_Cell.Row, exl.Columns.Count).Address "`n>" .
			exl.Cells(current_A_Cell.Row, exl.Columns.Count).End(xlToLeft).Address
		*/
		currentRow := exl.Range(current_A_Cell, exl.Cells(current_A_Cell.Row, exl.Columns.Count).End(xlToLeft))
		myVar := ""
		for currentCell in currentRow
			myVar .= currentCell.Text
		MsgBox myVar
		;Send myVar
	}
}

[flyingDman]

If you want to copy a range look here https://learn.microsoft.com/en-us/office/vba/api/excel.range.copy.

Code: Select all

xl.activesheet.range(range).copy(xl.activesheet.range(targetrange))

the target range can be the upper left cell of the target range. For instance:

Code: Select all

xl.activesheet.usedrange.copy(xl.activesheet.range("I10"))

I you want to move a range, copy it and then delete the source:

Code: Select all

xl := ComObjActive("excel.application")
rng := xl.activesheet.usedrange
rng.copy(xl.activesheet.range("I10"))
rng.value := ""

Or if you want to send each row (with spaces rather than tabs in between cells) to another application:

Code: Select all

xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns("A").cells
	{
	c.entirerow.copy
	msgbox trim(strreplace(A_Clipboard,a_tab," "),"`n`r") " "		; use send instead of msgbox
	}

or, better, use a saferray:

Code: Select all

xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value			; usedrange or any other valid range

f3::{											; sends lines one at a time
	static cnt := 0
	if (++cnt <= sarr.maxindex(1))
		loop sarr.maxindex(2)
			send sarr[cnt, a_index] " "
	}

f4::{											; sends all lines at once
	loop sarr.maxindex(1)
		{
		cnt := a_index
		loop sarr.maxindex(2)
			send sarr[cnt, a_index] " "
		}
	}

[Krd]

Thanks guys!

This was the closest to what I want to achieve here:

Code: Select all

Test()
{
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns("A").cells
	{
	c.entirerow.copy
	msgbox trim(strreplace(A_Clipboard,a_tab," "),"`n`r") " "	

	}
}

What I am trying to do are the following values in Excel, for example:

image.png (18.89 KiB) Viewed 1218 times

What I want is to paste it in a Word file (Excel does support COM here, but Word is blocked as COM).

image.png (8.43 KiB) Viewed 1218 times

And so on to the next value in Row 3 in another document.

If you look at my example in the first post, I would always search from A2:=

Code: Select all

 Cnt:='1'

.
And then send that value with a function to find MyCase in a separate system and then start a new Word-doc, which Row A is storing.
Then I would use the A0 values as attached above.
And finally, add the current values in the current range.

So Flyingdman how do I use your code in my first example code?

[Krd]

It had to be done the pros way:

Code: Select all

Test()
{
    Static cnt := 1
    xl := ComObjActive("excel.application")
    while (ValuInA := xl.activesheet.range("A" . ++cnt).text)
    {
    MsgBox ValuInA
        
    MsgBox xl.ActiveSheet.Range('A' . 1).Text A_Space xl.ActiveSheet.Range('B' . 1).Text A_Space xl.ActiveSheet.Range('C' . 1).Text A_Space xl.ActiveSheet.Range('D' . 1).Text A_Space xl.ActiveSheet.Range('E' . 1).Text A_Space xl.ActiveSheet.Range('F' . 1).Text A_Space xl.ActiveSheet.Range('G' . 1).Text A_Space xl.ActiveSheet.Range('H' . 1).Text A_Space xl.ActiveSheet.Range('I' . 1).Text A_Space xl.ActiveSheet.Range('J' . 1).Text
    
    MsgBox xl.ActiveSheet.Range('A' . cnt).Text A_Space xl.ActiveSheet.Range('B' . cnt).Text A_Space xl.ActiveSheet.Range('C' . cnt).Text A_Space xl.ActiveSheet.Range('D' . cnt).Text A_Space xl.ActiveSheet.Range('E' . cnt).Text A_Space xl.ActiveSheet.Range('F' . cnt).Text A_Space xl.ActiveSheet.Range('G' . cnt).Text A_Space xl.ActiveSheet.Range('H' . cnt).Text A_Space xl.ActiveSheet.Range('I' . cnt).Text A_Space xl.ActiveSheet.Range('J' . cnt).Text

    }
}

That is the working example for what I am asking for. However, as soon as the number of cells expands to the right, I have to expand the code as well.

This is the order in which I would process the text from Excel.

A pro out there would make this code look like chicken's code.. Please do it!

[Xeo786]

there is a example Pasting excel table to Outlook using MS word inspector so you can change this code according to your need
https://github.com/Xeo786/Excel2Email

[Krd]

If only I knew how to. :=)

@flyingDman, maybe you know how to use this:

Code: Select all

for c in xl.activesheet.usedrange.columns("A").cells
	{
	c.entirerow.copy
	msgbox trim(strreplace(A_Clipboard,a_tab," "),"`n`r") " "	
	}

Into this, specially the last MsgBox values:

Code: Select all

Test()
{
    Static cnt := 1
    xl := ComObjActive("excel.application")
    while (ValuInA := xl.activesheet.range("A" . ++cnt).text)
    {
    MsgBox ValuInA
        
    MsgBox xl.ActiveSheet.Range('A' . 1).Text A_Space xl.ActiveSheet.Range('B' . 1).Text A_Space xl.ActiveSheet.Range('C' . 1).Text A_Space xl.ActiveSheet.Range('D' . 1).Text A_Space xl.ActiveSheet.Range('E' . 1).Text A_Space xl.ActiveSheet.Range('F' . 1).Text A_Space xl.ActiveSheet.Range('G' . 1).Text A_Space xl.ActiveSheet.Range('H' . 1).Text A_Space xl.ActiveSheet.Range('I' . 1).Text A_Space xl.ActiveSheet.Range('J' . 1).Text
    
    MsgBox xl.ActiveSheet.Range('A' . cnt).Text A_Space xl.ActiveSheet.Range('B' . cnt).Text A_Space xl.ActiveSheet.Range('C' . cnt).Text A_Space xl.ActiveSheet.Range('D' . cnt).Text A_Space xl.ActiveSheet.Range('E' . cnt).Text A_Space xl.ActiveSheet.Range('F' . cnt).Text A_Space xl.ActiveSheet.Range('G' . cnt).Text A_Space xl.ActiveSheet.Range('H' . cnt).Text A_Space xl.ActiveSheet.Range('I' . cnt).Text A_Space xl.ActiveSheet.Range('J' . cnt).Text

    }
}

I just can't make this:

Code: Select all

for c in xl.activesheet.usedrange.columns("A").cells

to work with this:

Code: Select all

while (ValuInA := xl.activesheet.range("A" . ++cnt).text)

[flyingDman]

As mentioned above, it's better to use a safe array. This is just a small variation of the script above (viewtopic.php?f=82&t=120491&p=535601#p534826):

Code: Select all

xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value			; usedrange or any other valid range
cnt := 0
while cnt < sarr.maxindex(1)
	{
	lst := "", ++cnt
	loop sarr.maxindex(2)
		lst .= sarr[cnt, a_index] " "
	msgbox trim(lst)
	}

[Krd]

Thanks for killing have of my pro brain cells.

Finally I got here and closer to what I want:

Code: Select all

Test()
{
xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value
cnt := 0
	loop sarr.maxindex(2)
        AlwaysFirst .= sarr[1, a_index] "  "
while cnt < sarr.maxindex(1)
	{
	lst := "", ++cnt
	loop sarr.maxindex(2)
        {
		lst .= sarr[cnt, a_index] "  "
        }
    msgbox ValuInA := xl.activesheet.range("A" . cnt).text
    msgbox trim(AlwaysFirst)
    msgbox trim(lst)
	}
}

The non-working part is that when I send the contents of lines 17 and 18 (variables AlwaysFirst/lst), a cell's value changes from, for example, '1 000' to '1000.0,' which is a bit of a nightmare
Also, there are line breaks:
This becomes:
123 Text 4567 Some Name
To this:
123 Text
4567 Some Name

I want to eliminate line breaks while preserving the original formatting with no additional zeros!
How to fix this issue?

[flyingDman]

try:

Code: Select all

xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value			; usedrange or any other valid range
cnt := 0
while cnt < sarr.maxindex(1)
	{
	lst := "", ++cnt
	loop sarr.maxindex(2)
		{
		v := RegExReplace(sarr[cnt, a_index],"\v+"," ")
		lst .= (isnumber(v) ? round(v) : v) " "  		; you can also use format() or trim(trim(var,"0"),".")
		}
	msgbox trim(lst)
	}

[Krd]

flyingDman, Thank you so much for keeping up and not giving up on me. I really appreciate it!

image.png (4.99 KiB) Viewed 982 times

When sent to a Word doc, it looks like this:

image.png (2.78 KiB) Viewed 982 times

Is there a hope to make it more like this:

image.png (2.34 KiB) Viewed 982 times

Variable AlwaysFirst is the header. And it would always be the same amount of columns as the rest of the file.

What can I do to align the cells in the row below with the cells in the row above, so they have the correct relative positions when sent, in the following example: ?

Code: Select all

Test()
{
xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value
cnt := 1
	loop sarr.maxindex(2)
        AlwaysFirst  .= sarr[1, a_index] " "
while cnt < sarr.maxindex(1)
	{
	lst := "", ++cnt
	loop sarr.maxindex(2)
        {
		v := RegExReplace(sarr[cnt, a_index],"\v+"," ")
		lst .= (isnumber(v) ? round(v) : v) " "
        }
    ;ValueInA := xl.activesheet.range("A" . cnt).text
    FinalValue := AlwaysFirst  '`n' lst
    MsgBox FinalValue
	}
}

[flyingDman]

AFAIK you can only do that with COM on the Word side as well as on the Excel side. One way is to use PasteExcelTable (which uses the clipboard):

Code: Select all

oWord := ComObjActive("word.application")
xl := ComObjActive("excel.application")
xl.activesheet.usedrange.copy
oWord.selection.PasteExcelTable(0,0,0)
return

An other COM method uses HTML, but it creates a temp file:

Code: Select all

oWord := ComObjActive("word.application")
xl := ComObjActive("excel.application")
rng := xl.activesheet.usedrange.address()
xl.ActiveWorkbook.PublishObjects.Add(4, A_Temp "\xl2html.htm", "Sheet1", rng, 0).publish(1)      ;~ xlSourceRange=4 xlHtmlStatic=0
oWord.Selection.MoveDown(5)
oWord.selection.InsertFile(A_temp "\xl2html.htm")
FileDelete(A_Temp "\xl2html.htm")
return

As can be expected both methods create a table in Word.

[Krd]

Interesting code, I will return if Word supports COM in my system in the future.

Meanwhile, using a pipe was not so bad.

Thank you for your time and great support.