AutoHotkey Community

Posted: **25 Oct 2016, 19:48**

I released an Excel COM library a while ago - well this is simply an updated version with a few more functions, and a demo program which will (hopefully) help newbies out there use the functions.

The Code ispretty well documented i think....So just have a look and let me know what you think! Zip file includes Excel file for use with demo program.

Let me know what you think/give me more functions to code, etc.

Note: Not all available functions are part of demo program - will be adding them and updating here.

Thanks for looking!

GIF Demo: http://imgur.com/a/BVRYh ***********NOTE: Demo uses Tab3 control, not Tab2************

Source: https://github.com/tre4shunter/XLFunctions/

-Tre4

Posted: **27 Oct 2016, 04:53**

It's great! thanks for sharing.

Posted: **28 Oct 2016, 12:51**

[UPDATE]

-Modified XLCheck() to exit "Edit Mode" if the excel window is Active
-Added Functions:

XL_ListWorkBooks() - List all all books - even if you have multiple Excel Processes running. Courtesy of Lexikos.

XL_RemoveDuplicates(Name,Sht,Range,hdr)

XL_SortSingleCol(Name,sht,Col,AscDesc)

XL_SortColRange(Name,Sht,RangeToSort,SortKeys)

*****Some Notes*****

I have had issues with workbooks being 'Shared' Certain functions will not work properly. I am loooking into this further to see whats going on with that...Demo program for new functions will be updated soon as well.

Thanks!

Posted: **31 Oct 2016, 08:19**

[UPDATE]

Added new functions:

XL_GetHPageBreaks()
XL_GetVPageBreaks()

Return objects containg columns/rows of Vertical and Horizontal Pagebreaks.

Thanks!

Matt

Posted: **08 Nov 2016, 07:03**

I'm trying to use the FindAll function but I keep getting a Type Mismatch error with the generated function code from the Demo Script.

Here's the error's, I'm not really sure what I'm doing wrong

Spoiler

Posted: **08 Nov 2016, 11:44**

Could you post your proposed code and not just the errors, please?

Posted: **09 Nov 2016, 16:27**

I've got some changes coming soon, small updates, a few new functions etc, and an updated demo program hopefully...

But, like sinkfaze said, please post your actual code so we can see whats wrong.

Posted: **11 Nov 2016, 08:41**

Code: Select all

test() {
	z := srcXL[1]
	xlSrc := ComObjGet(z)
	;lstRow := xlSrc.Sheets(1).UsedRange.Rows.Count
	rowDates := XL_RangeFindAll(xlSrc,"","A1:A2000","*/*","A2000",xlvalues,xlwhole,xlbycolumns,xlnext,"Row")
	MsgBox, %rowDates%
}

srcXL[1] contains the file path to the excel file.

Basically, if I tried to call the function inside another function I get a type mismatch error, which doesn't happen if I use it outside a function.

Posted: **11 Nov 2016, 08:55**

Use Global inside of the function - the function does not know what all the excel constants are. Or, pass them as params to the function.

Also - XL_RangeFindAll returns an object so you wont see anything in %rowdates%, but rather msgbox, % Rowdates[1] etc etc etc

Code: Select all

test() {
	Global
	z := srcXL[1]
	xlSrc := ComObjGet(z)
	;lstRow := xlSrc.Sheets(1).UsedRange.Rows.Count
	rowDates := XL_RangeFindAll(xlSrc,"","A1:A2000","*/*","A2000",xlvalues,xlwhole,xlbycolumns,xlnext,"Row")
	MsgBox, %rowDates%
}

Someone can delete my extra post above this one...Sorry(it has the wrong answer anyway i think lol)!

Posted: **13 Nov 2016, 10:35**

Thanks, managed to get it to work by declaring them global variables. Oh, lol, that MsgBox line was left there when I was testing with other stuff.

How do I properly use XL_ListWorkbooks() to specify which workbooks to close and activate?

Posted: **25 Nov 2016, 05:30**

Great library, thanks for sharing.

I found that the following Excel Constants you define in your library are not according the Microsoft definition:

Code: Select all

xlLeft := -4159

should be

Code: Select all

xlLeft := -4131
xlToLeft := -4159

and

Code: Select all

xlRight := -4161

should be

Code: Select all

xlRight := -4152
xlToRight := -4161

I found this here:
https://msdn.microsoft.com/en-us/librar ... e.14).aspx
https://msdn.microsoft.com/en-us/librar ... e.14).aspx

Posted: **18 Oct 2019, 07:21**

Hi Tre4shunter, I am trying to Loop this

#Include <lib_XLFuncs>
val := XL_GetCell("","","b2+1","Text")
MsgBox % val

But "B(2+1)" command is not working, Can you please help me with this??

Thank you in Advance!!

Posted: **23 Oct 2019, 03:33**

This is awesome stuff! can't believe i've never seen this before.

Posted: **23 Aug 2022, 10:46**

Nice work. I will test the code later. Thank you!

Posted: **23 Aug 2022, 11:15**

These are wrappers and there is no new functionality. It might be helpful to some but they do not really simplify anything. Rather than making you focus on the original syntax, these wrappers make you "memorize different syntax that is at least as complex as the native syntax that is being wrapped" (memorable quote from @boiler from this post viewtopic.php?f=76&t=107151&p=476416&hilit=glines#p476425.)

AutoHotkey Community

Excel COM Function Library

Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library

Re: Excel COM Function Library