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. ^^;
Excel COM Help Topic is solved
- FanaticGuru
- Posts: 1905
- Joined: 30 Sep 2013, 22:25
Re: Excel COM Help Topic is solved
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"
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: Excel COM Help
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.
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.
Re: Excel COM Help
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.
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.)
'Main' is a worksheet object. Based on your description I would guess that the 'xl' you have been using is an Application object.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?
Re: Excel COM Help
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.
...I also did not know about the object browser. That is ALSO super helpful, just by itself.
Re: Excel COM Help
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
...
...
Re: Excel COM Help
@ MaxAstro, yeah, the object browser is the best!
@ derz00 That's great! Glad to hear it.
@ derz00 That's great! Glad to hear it.
- FanaticGuru
- Posts: 1905
- Joined: 30 Sep 2013, 22:25
Re: Excel COM Help
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.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).
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
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
- FanaticGuru
- Posts: 1905
- Joined: 30 Sep 2013, 22:25
Re: Excel COM Help
On task where speed is important you can also generally get a boost by using Xl.Application.ScreenUpdatingMaxAstro 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!
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
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
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: Excel COM Help
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.
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
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
Re: Excel COM Help
Related https://autohotkey.com/boards/viewtopic ... 84#p111484
also Why is TRUE equal to -1 and not 1
In my experience, (numbers are roughly estimated)
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.
Re: Excel COM Help
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.
(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.
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
(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
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
- FanaticGuru
- Posts: 1905
- Joined: 30 Sep 2013, 22:25
Re: Excel COM Help
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).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.
@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
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Who is online
Users browsing this forum: morkovka18 and 178 guests