Using COM to put array into Excel Topic is solved

[derz00]

Hello AHK Experts:

Moderators, you may delete this if I am overlooking a better way to find this out.

I have a script to compile data in a certain order, ending up like this:

Code: Select all

June 22 12:00 PM,AB,1,2,3,4,5,6,7,8,9,0,11

I would like this to be imported into an Excel file. I would like each item in the array to go in individual cells in a column. Also, I would like the data in the right column for the day of the month. E.g. On July 1, in column B; on July 2, in column C; on July 3, in column D; On July 10, in column K; and so forth.

I have programmed the script to concatenate the data with commas. If there would be a better way to do it, I wouldn't care. :)

Learning how to do this would really advance my knowledge of how to code AHK, and specifically with COM in Excel.

Thanks in advance for your help.

[BoBo]

If searching for "Excel com put" you'll get [this] as a starting point.
Good luck

Spoiler

... where kon kindly provided this ...

Code: Select all

; http://www.autohotkey.com/board/topic/56987-com-object-reference-autohotkey-v11/page-7#entry394713
; http://www.autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/
SafeArray := ComObjArray(12, 2, 2)
SafeArray[0, 0] := 5
SafeArray[0, 1] := 4
SafeArray[1, 0] := 3
SafeArray[1, 1] := 2

x := ComObjCreate("Excel.Application")
x.visible := true
book := x.workbooks.add
x.Range("A1:B2").value := SafeArray

[derz00]

Hello,
Thanks BoBo. Sure enough, I should have done more searching.

This is the code I came up with. This does not work. So that means I am not understanding something. Someone please help. :)

I want an array with 12 rows, and 1 column.

Spoiler

Code: Select all

SafeArray := ComObjArray(12, 12, 1)
SafeArray[1, 0] := timeLog
SafeArray[2, 0] := initLog
SafeArray[3, 0] := meatLog
SafeArray[4, 0] := meatwiLog
SafeArray[5, 0] := wifLog
SafeArray[6, 0] := adfLog
SafeArray[7, 0] := bwiLog
SafeArray[8, 0] := dmcdLog
SafeArray[9, 0] := icnLog
SafeArray[10, 0] := icsLog
SafeArray[11, 0] := prodisLog
SafeArray[12, 0] := rifLog

[BoBo]

; http://www.autohotkey.com/board/topic/5 ... ntry394713
; http://www.autohotkey.com/board/topic/6 ... for-excel/

var := "<varContent>"
SafeArray := ComObjArray(VT_VARIANT:=12,3,1)
SafeArray[0,0] := 10
SafeArray[1,0] := var
SafeArray[2,0] := """text"""

x := ComObjCreate("Excel.Application")
x.visible := true
book := x.workbooks.add
x.Range("A1:A3").value := SafeArray

[FanaticGuru]

This is the code I came up with. This does not work. So that means I am not understanding something. Someone please help.

I want an array with 12 rows, and 1 column.

Spoiler

Code: Select all

SafeArray := ComObjArray(12, 12, 1)
SafeArray[1, 0] := timeLog
SafeArray[2, 0] := initLog
SafeArray[3, 0] := meatLog
SafeArray[4, 0] := meatwiLog
SafeArray[5, 0] := wifLog
SafeArray[6, 0] := adfLog
SafeArray[7, 0] := bwiLog
SafeArray[8, 0] := dmcdLog
SafeArray[9, 0] := icnLog
SafeArray[10, 0] := icsLog
SafeArray[11, 0] := prodisLog
SafeArray[12, 0] := rifLog

A SafeArray seems like overkill in this case.

This simple function will put the data in an active Excel sheet starting at the range indicated.

Code: Select all

Excel_Put("A1","June 22 12:00 PM","AB",1,2,3,4,5,6,7,8,9,0,11) ; could be variables instead of static values

; Excel_Put(Range, timeLog, initLog, meatLog, meatwiLog, wifLog, adfLog, bwiLog, dmcdLog, icnLog, icsLog, prodisLog, rifLog)
Excel_Put(rng, values*)
{
	Xl := ComObjActive("Excel.Application")
	for key, value in values
		Xl.Range(rng).Offset(key-1,0).Value := value 
}

How best to pass the information to the function would depend on how the data is stored by your script before calling the function.

You could figure out where to put the data before calling the function or you could include in the function the calculations for the location based on the date passed to the function.

Also this is just a simple example with an active worksheet. You could of course create a new worksheet or open a specific worksheet. It can also all be done in the background without ever displaying Excel.

FG

[BoBo]

FG, you made my day. Thx

[derz00]

FanaticGuru, I tried that, but it threw an error about the Range(rng).Offset phaleth on IRC told me you can't offset a range.

I don't have time today, but later I think I'll work with this more. I just wanted to thank you for taking the time. I'm learning some things, I think!

Thanks again, and I'll be back, probably.

[Guest]

phaleth on IRC told me you can't offset a range.

He's wrong. You can ONLY offset a range.

expression A variable that represents a Range object.
https://msdn.microsoft.com/VBA/Excel-VB ... erty-excel

I tested it and it worked. It appears that BoBo did the same. I can't imagine FG posted without testing it himself at some point.
Did you copy-and-paste it exactly when you tested it? Or did you attempt to modify it?

but it threw an error about the Range(rng).Offset

Be specific. Post the code you tried and the EXACT error. (press Crtl+C in the error Msgbox to copy it)
Did you notice that the first parameter is "A1"?

[FanaticGuru]

FanaticGuru, I tried that, but it threw an error about the Range(rng).Offset phaleth on IRC told me you can't offset a range.

I don't have time today, but later I think I'll work with this more. I just wanted to thank you for taking the time. I'm learning some things, I think!

Thanks again, and I'll be back, probably.

You can definitely Offset a range. I do it in Excel scripts all the time.

I can tell you that exact code works fine for me. I rarely post any code without testing it.

Open an Excel worksheet, run that exact script, data goes in the spreadsheet.

FG

[derz00]

FanaticGuru:
Thanks very much for your help. This code will also help me to learn how to use functions. I have stuck with commands so far.
Guest: Thanks for your advice. I was in too much of a hurry when I replied.
Here is what the error message box says:

Code: Select all

---------------------------
script.ahk
---------------------------
Error:  0x80020006 - Unknown name.

Specifically: Range

	Line#
	255: Excel_Put(cell . "1","June 22 12:00 PM","AB",1,2,3,4,5,6,7,8,9,0,11)  
	259: {
	260: FullPath := "\\networkserver\Documents\MonthlyLog " . A_YYYY . " " . A_MM . " " . A_MMMM . ".xlsx"
	261: Xl := ComObjGet(FullPath)
	262: For key,value in values
--->	263: Xl.Range(rng).Offset(key-1,0).Value := value  
	264: }
	271: MsgBox,testing
	272: X1.Close(1)  
	273: X1 := ""
	293: ExitApp
	294: Exit
	295: Exit

Continue running the script?
---------------------------
Yes   No   
---------------------------

You are right, the code did work until I assigned it to open the file on the network machine. That works for me otherwise, but not with this function. Does this help you see my problem?

Thanks a lot in advance.

[BoBo]

FullPath := "\\networkserver\Documents\MonthlyLog " . A_YYYY . " " . A_MM . " " . A_MMMM . ".xlsx"
==> "\\networkserver\Documents\MonthlyLog 2017 06 June.xlsx"
I doubt that you've selected that kind of filename intentionally ??!

[Guest]

Ah, now that I can see where you modified the code it's simple. Although, I did ask that you post the code AND the error message... at least I can see the partial code from the error.

You changed the function! Xl := ComObjGet(FullPath)

ComObjGet returns a Workbook object. This object represents the file you opened.

ComObjActive returns an Application object. This object represents the Excel program.

Xl := ComObjGet(FullPath).Application might work.

The error "Unknown Name" tells you that the object stored in XL does not have a member called "Range". This is a big hint that you are not dealing with an Application object and are in fact using a Workbook object. That is to say, Workbook.Range does not exist. Application.Range does exist. (google them, or check if you can find "Range" on the pages I linked above)

[derz00]

Yes, that is the file name I want. That file does exist.

[Guest]

Xl := ComObjGet(FullPath).Application might work.

I should add that the above is untested.
So is this:

Code: Select all

wbk:= ComObjGet(FullPath)
For key,value in values
    wbk.Sheets(1).Range(rng).Offset(key-1,0).Value := value

will probably work unless I have made a typo.

Side note @FG: Instead of:
Xl.Range(rng).Offset(key-1,0).Value := value
this works too
Xl.Range(rng).Cells(key).Value := value

[IMEime]

ComObjGet is Not so good choice.
I, personally, do not use it.

For Excel, ComObjActive or ComObjCreate is good enough.

[derz00]

Hi all; This works, as a result of your help and phaleth's. Thought I'd let you know my status. Thanks again for taking the time to help. Mr. Anonymous, I wish you had just a name or something, would seem like I could thank you properly. I will reference this thread more in the future as I learn these basics.

Code: Select all

Excel_Put(cell . "1",timeLog,initLog,meatLog,meatwiLog,wifLog,adfLog,bwiLog,dmcdLog,icnLog,icsLog,prodisLog,rifLog) 

Excel_Put(Rng, timeLog, initLog, meatLog, meatwiLog, wifLog, adfLog, bwiLog, dmcdLog, icnLog, icsLog, prodisLog, rifLog)
{
	arr := [timeLog, initLog, meatLog, meatwiLog, wifLog, adfLog, bwiLog, dmcdLog, icnLog, icsLog, prodisLog, rifLog]
    ;~ arr := ["y1","June 22 12:00 PM","AB",1,2,3,4,5,6,7,8,9,10]

	ExcelFilePath := "\\networkstorage\MonthlyLog " . A_YYYY . " " . A_MM . " " . A_MMMM . ".xlsx"
	oExcel := ComObjCreate("Excel.Application") ;Create excel object
	oWorkbook := oExcel.Workbooks.Open(ExcelFilePath) ;Opens the selected specified file
	oExcel.Visible := false
	for key, val in arr
        oExcel.Worksheets(1).Range(rng).Offset(key-1,0).Value := val
    oWorkbook.Save() ; Save the changes
    oExcel.Quit() ;Close the excel window  
    oExcel := "" ;Release the reference
}

[derz00]

I have one more question. How would I set the above code to offset in a row instead of a column? This Excel COM stuff is new to me, and maybe I should be able to figure that out myself. Likely I would set "cell" to contain a number instead of a letter, and type it like this:

Code: Select all

Excel_Put("B" . cell,timeLog,initLog,meatLog,meatwiLog,wifLog,adfLog,bwiLog,dmcdLog,icnLog,icsLog,prodisLog,rifLog)

But I wouldn't know how to offset to the right instead of down. Likely tweaking something in this line?

Code: Select all

oExcel.Worksheets(1).Range(rng).Offset(key-1,0).Value := val ; from this
oExcel.Worksheets(1).Range(rng).Offset(key0,-1).Value := val ; to this?

I tried this, but it gave this error.

Thanks in advance for any advice!

[kon]

Offset 0 rows and key minus 1 columns:
oExcel.Worksheets(1).Range(rng).Offset(0, key - 1).Value := val

Key is a variable that will contain the numbers 1,2,3,4...

But the offset starts at 0. We want it to count 0,1,2,3...

So we subtract 1 from key.

For example,
>first loop (rng = "A1", key = 1)
oExcel.Worksheets(1).Range("A1").Offset(0, 0).Value := val ; Uses cell A1. (offset 0 rows and 0 columns)

>second loop (rng = "A1", key = 2)
oExcel.Worksheets(1).Range("A1").Offset(0, 1).Value := val ; Uses cell B1. (offset 0 rows and 1 columns)

>third loop (rng = "A1", key = 3)
oExcel.Worksheets(1).Range("A1").Offset(0, 2).Value := val ; Uses cell C1. (offset 0 rows and 2 columns)

>fourth loop (rng = "A1", key = 4)
oExcel.Worksheets(1).Range("A1").Offset(0, 3).Value := val ; Uses cell D1. (offset 0 rows and 3 columns)

[derz00]

Thanks a million for your kind explanation.

And my code was almost right. But almost doesn't count with coding. Now that I see that, I think I should have been able to figure that out myself. Anyway, I guess this is what the forum is for!

Ya, almost doesn't count, but when you do it right, bam, it works! I'm still a noob, so I get that thrill of everything working after many little lessons and "ding dong" moments.

[FanaticGuru]

Side note @FG: Instead of:
Xl.Range(rng).Offset(key-1,0).Value := value
this works too
Xl.Range(rng).Cells(key).Value := value

Yes, that is a handy trick to know that you can use Cells with a range to access a subset of a range. Cleaner code, more complicated concept.

Code: Select all

	Xl := ComObjActive("Excel.Application")
	rng := Xl.Range("B5:D10")
	rng.Cells(3,2).Value := "This is cell C7" ; 3rd cell down, 2nd cell over, within the range

You can do negative but then it can really get confusing:

Code: Select all

Xl := ComObjActive("Excel.Application")
	rng := Xl.Range("B5:D10")
	rng.Cells(-2,0).Value := "This is cell A2" ; 3 cells up, 1 cell left from the range

@IMEime, I also avoid using ComObjGet. Had a problem that I cannot even remember now but ever since just use Create and Open. ComObjGet creates a handle to a workbook not to Excel application. It is often useful to have a handle to application. If you really must you can get the handle backwards:

Code: Select all

oWorkbook := ComObjGet(A_Desktop "\Test\Test.xlsx")
Xl := oWorkbook.Parent

FG