Excel COM Help Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
MaxAstro
Posts: 552
Joined: 05 Oct 2016, 13:00

Excel COM Help

26 Jun 2017, 15:48

I've used the Excel COM object for reading from a worksheet before, but that's the extent of my understanding of it and I've just hit a rather more complicated project that I'm hoping someone can talk me through.

I have a CSV file that consists of a few hundred lines of simply Item#,Quantity. I also have an Excel worksheet that this information needs to go into. Basically, for each line in the CSV, I want to do the following:

1) Find the row in which the value of column E matches Item#
2) Set the value of column L in that row to Quantity

I don't know how to do either of these things with my current level of expertise. ^^;
FanaticGuru
Posts: 1317
Joined: 30 Sep 2013, 22:25

Re: Excel COM Help  Topic is solved

26 Jun 2017, 19:17

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := true
CSV := Xl.Workbooks.Open(A_Desktop "\Test\Test.CSV").ActiveSheet
Main := Xl.Workbooks.Open(A_Desktop "\Test\Test.xlsx").ActiveSheet
for Cell in CSV.UsedRange.Columns("A").Cells
	if (CellFound := Main.Columns("E").Find(Cell.Value))
		Main.Cells(CellFound.Row,12).Value := Cell.Offset(0,1).Value
	else
		MsgBox % Cell.Value " NOT FOUND"
FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
MaxAstro
Posts: 552
Joined: 05 Oct 2016, 13:00

Re: Excel COM Help

27 Jun 2017, 09:18

Wow, that works incredibly well! The Excel file I'm searching is some 13,000 lines long, and your script still only takes ~3 seconds to finish. That's awesome! I wish I had a better understanding of how it works, but it certainly got the job done. Thanks, that saved hours of work!

So from trying to pick this apart... UsedRange.Columns.Cells is an array that contains all cells in a given column that have a value? I assume UsedRange.Rows.Cells is a thing you can do also. Is .Cells the only valid thing to end it with or are there other things you can reference?

I think I understand how .Find works, and that it returns the cell that it found. I haven't seen that Main.Cells() call before, though. I've been using xl.Range to reference specific cells; how is that different? I've been using ComObjActive instead of ComObjCreate - is it related to that?

Finally, .Offset just shifts a certain number of cells in the selected x and y, I assume? In this case 0 rows and 1 columns.
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Excel COM Help

27 Jun 2017, 09:58

Most of your questions can be answered by reading the MS Office docs. (see MS Office COM Basics section 2)
I always have the Object Browser and Help open when writing any Office COM code.
The key to understanding is knowing what objects you are dealing with. For example, in Fanatic Guru's code, 'Main' and 'CSV' are worksheet objects. If you search for 'worksheet' in the object browser you can see all the members of a worksheet object. Note that 'UsedRange' is listed among the members.
So from trying to pick this apart... UsedRange.Columns.Cells is an array that contains all cells in a given column that have a value?
  • Main.UsedRange returns a range object.
  • Range.Columns("A") returns a range object (this object contains the cells in column A).
  • Unless I am mistaken, Range.Cells is redundant. It returns a Range object representing all the cells in the range (which we had already). You could use cells to further restrict the range object. For example, Range.Cells(2) gets the second cell in the range. BTW, cells are Range objects. (A range can contain many cells or only one.)
I haven't seen that Main.Cells() call before, though. I've been using xl.Range to reference specific cells; how is that different? I've been using ComObjActive instead of ComObjCreate - is it related to that?
'Main' is a worksheet object. Based on your description I would guess that the 'xl' you have been using is an Application object.
MaxAstro
Posts: 552
Joined: 05 Oct 2016, 13:00

Re: Excel COM Help

27 Jun 2017, 10:21

Thanks, that COM basics link is super helpful, it looks like it's way more comprehensive than the guide I had been using before.

...I also did not know about the object browser. That is ALSO super helpful, just by itself.
User avatar
derz00
Posts: 497
Joined: 02 Feb 2016, 17:54
GitHub: derz00
Location: Middle of the round cube

Re: Excel COM Help

27 Jun 2017, 12:20

Reading this has also been helpful to me. I also take an interest in COM, though I have a couple scripts so far that use it. I just found your page yesterday, Kon, linked on Joe Glines website. Your careful explanations are really helpful to beginners!
try it and see
...
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Excel COM Help

27 Jun 2017, 12:27

@ MaxAstro, yeah, the object browser is the best!
@ derz00 That's great! Glad to hear it.
FanaticGuru
Posts: 1317
Joined: 30 Sep 2013, 22:25

Re: Excel COM Help

27 Jun 2017, 20:24

kon wrote:Unless I am mistaken, Range.Cells is redundant. It returns a Range object representing all the cells in the range (which we had already).
Sometimes the Cells is redundant and sometimes it is not. This is a case where it is required. If you take it out the script does not work.

So I tend to always put Cells at the end of a for Cell in. It makes the logic clear to me as I often use singular and plural when dealing with object loops. for Item in Items.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
FanaticGuru
Posts: 1317
Joined: 30 Sep 2013, 22:25

Re: Excel COM Help

27 Jun 2017, 20:31

MaxAstro wrote:Wow, that works incredibly well! The Excel file I'm searching is some 13,000 lines long, and your script still only takes ~3 seconds to finish. That's awesome!
On task where speed is important you can also generally get a boost by using Xl.Application.ScreenUpdating

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := true
Xl.Application.ScreenUpdating := false
CSV := Xl.Workbooks.Open(A_Desktop "\Test\Test.CSV").ActiveSheet
Main := Xl.Workbooks.Open(A_Desktop "\Test\Test.xlsx").ActiveSheet
for Cell in CSV.UsedRange.Columns("A").Cells
	if (CellFound := Main.Columns("E").Find(Cell.Value))
		Main.Cells(CellFound.Row,12).Value := Cell.Offset(0,1).Value
	else
		MsgBox % Cell.Value " NOT FOUND"
Xl.Application.ScreenUpdating := true
This makes Excel not redo calculations and update the screen every time a cell is changed. It turns this off at the beginning and turns back on at the end.

It can also avoid the visual ugliness when lots of cells are changing and flickering.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
User avatar
jeeswg
Posts: 5939
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel COM Help

28 Jun 2017, 04:55

I believe the following to be true:
AHK: True:1, False:0
Excel (sheets): True:1, False:0
Excel (VBA/macro): True:-1, False:0 [note the minus sign]

In the example above you use True (= 1) for some VBA code.

Does it say anywhere that using True (= 1) is OK in Excel VBA? Thanks.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Excel COM Help

28 Jun 2017, 08:32

Related :arrow: https://autohotkey.com/boards/viewtopic ... 84#p111484
also Why is TRUE equal to -1 and not 1

In my experience, (numbers are roughly estimated)
  • 1 Works 90% of the time
  • -1 Works 99% of the time
  • ComObj(0xB,-1) works when the other two fail.
User avatar
jeeswg
Posts: 5939
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel COM Help

28 Jun 2017, 08:52

Great response, cheers. I was thinking about the two types of 'not' in AHK earlier today, it's a knotty issue. You might say that 'not 1' is not what you expect.

I'd come across that VBForums discussion before, and the reason for using -1 instead of 1, didn't sink in somehow, but now it seems really obvious.

Hmm, not even '-1' works 99% of the time. Yeah, you're quite the expert with Excel, you and FanaticGuru, do both of you use it a lot? I've got quite a lot of notes from Excel VBA I might translate to AHK and publish here, from years ago, although I hardly did anything with AHK *and* Excel.

Code: Select all

q:: ;'to be, or ! to be, or ~ to be, that is the question'
MsgBox, % !(-1) "`r`n" ~(-1) ;0 0
MsgBox, % !0 "`r`n" ~0 ;1 4294967295 (0xFFFFFFFF) (-1)
MsgBox, % !1 "`r`n" ~1 ;0 4294967294 (0xFFFFFFFE) (-2)

MsgBox, % !0xFFFFFFFF "`r`n" ~0xFFFFFFFF ;0 0
MsgBox, % !0xFFFFFFFE "`r`n" ~0xFFFFFFFE ;0 1
return
(I used to use Excel for all sorts of data things, but found using AHK and text files was much faster, I don't like how you can't turn off assumptions in Excel, and how it can mess up dates, and you have to unpick them.)

(One thing that's curious is that if you have a cell ="", copy it, and paste special as a value, you have what I call a 'ghost' cell, different from a typical blank cell, that you 'hit' when you use ctrl+arrows. It's as though it's a cell that contains a string, but that is an empty string. I eventually worked out how to identify these cells using Excel VBA, I'll publish all when I find my notes.)

Much appreciated.
Last edited by jeeswg on 03 Jul 2017, 20:40, edited 1 time in total.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
FanaticGuru
Posts: 1317
Joined: 30 Sep 2013, 22:25

Re: Excel COM Help

28 Jun 2017, 12:41

kon wrote:In my experience, (numbers are roughly estimated)
  • 1 Works 90% of the time
  • -1 Works 99% of the time
  • ComObj(0xB,-1) works when the other two fail.
This is a good point to keep in mind. I generally just assume 1 will work and correct if it does not but the first time I ran in to a need for ComObj(0xB,-1) as a parameter I spent a couple of hours thinking I was getting some other syntax wrong when 1 or -1 would not work before I got the code to work with ComObj(0xB,-1).

@jeeswg, I use MS Office (Outlook, Excel, Word) in general a lot. I do quite a bit of VBA macros in all three which translates pretty easily to AHK COM but if I am doing a lot of complicated stuff with the data from an Excel file, I tend to use AHK COM as I am more comfortable with string and array manipulation in AHK. My final product is usually a Word or Excel file without any macro or other dependencies that gets distributed to others. I end up creating a lot of fancy Word and Excel files that others have no idea how I get done so quickly.

Millions of people use MS Office regularly but 99% don't know how powerful of a scripting language is built in to it.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers

Return to “Ask For Help”

Who is online

Users browsing this forum: Albireo, Bing [Bot], eqv, esp901, JoeWinograd and 62 guests