Excel COM Function Library

Post your working scripts, libraries and tools
Tre4shunter
Posts: 32
Joined: 26 Jan 2016, 16:05

Excel COM Function Library

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
User avatar
oldbrother
Posts: 136
Joined: 23 Oct 2013, 05:08

Re: Excel COM Function Library

27 Oct 2016, 04:53

It's great! thanks for sharing.
Tre4

Re: Excel COM Function Library

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!
Tre4shunter
Posts: 32
Joined: 26 Jan 2016, 16:05

Re: Excel COM Function Library

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
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: Excel COM Function Library

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


Spoiler
User avatar
sinkfaze
Posts: 413
Joined: 01 Oct 2013, 08:01

Re: Excel COM Function Library

08 Nov 2016, 11:44

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

Re: Excel COM Function Library

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.
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: Excel COM Function Library

11 Nov 2016, 08:41

Code: [Select all] [Download] GeSHi © Codebox Plus

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.
Tre4shunter
Posts: 32
Joined: 26 Jan 2016, 16:05

Re: Excel COM Function Library

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] [Download] GeSHi © Codebox Plus

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)!
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: Excel COM Function Library

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?
T-Rock
Posts: 15
Joined: 01 Feb 2015, 09:11

Re: Excel COM Function Library

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] [Download] GeSHi © Codebox Plus

xlLeft := -4159

should be

Code: [Select all] [Download] GeSHi © Codebox Plus

xlLeft := -4131
xlToLeft := -4159

and

Code: [Select all] [Download] GeSHi © Codebox Plus

xlRight := -4161

should be

Code: [Select all] [Download] GeSHi © Codebox Plus

xlRight := -4152
xlToRight := -4161

I found this here:
https://msdn.microsoft.com/en-us/library/office/ff197824(v=office.14).aspx
https://msdn.microsoft.com/en-us/library/office/ff820880(v=office.14).aspx

Return to “Scripts and Functions”

Who is online

Users browsing this forum: No registered users and 24 guests