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
Excel COM Function Library
-
- Posts: 139
- Joined: 26 Jan 2016, 16:05
- oldbrother
- Posts: 273
- Joined: 23 Oct 2013, 05:08
Re: Excel COM Function Library
It's great! thanks for sharing.
Re: Excel COM Function Library
[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!
-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!
-
- Posts: 139
- Joined: 26 Jan 2016, 16:05
Re: Excel COM Function Library
[UPDATE]
Added new functions:
XL_GetHPageBreaks()
XL_GetVPageBreaks()
Return objects containg columns/rows of Vertical and Horizontal Pagebreaks.
Thanks!
Matt
Added new functions:
XL_GetHPageBreaks()
XL_GetVPageBreaks()
Return objects containg columns/rows of Vertical and Horizontal Pagebreaks.
Thanks!
Matt
Re: Excel COM Function Library
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
Here's the error's, I'm not really sure what I'm doing wrong
Spoiler
Spoiler
Re: Excel COM Function Library
Could you post your proposed code and not just the errors, please?
Re: Excel COM Function Library
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.
But, like sinkfaze said, please post your actual code so we can see whats wrong.
Re: Excel COM Function Library
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%
}
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.
-
- Posts: 139
- Joined: 26 Jan 2016, 16:05
Re: Excel COM Function Library
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
Someone can delete my extra post above this one...Sorry(it has the wrong answer anyway i think lol)!
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%
}
Re: Excel COM Function Library
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?
How do I properly use XL_ListWorkbooks() to specify which workbooks to close and activate?
Re: Excel COM Function Library
Great library, thanks for sharing.
I found that the following Excel Constants you define in your library are not according the Microsoft definition:
should be
and
should be
I found this here:
https://msdn.microsoft.com/en-us/librar ... e.14).aspx
https://msdn.microsoft.com/en-us/librar ... e.14).aspx
I found that the following Excel Constants you define in your library are not according the Microsoft definition:
Code: Select all
xlLeft := -4159
Code: Select all
xlLeft := -4131
xlToLeft := -4159
Code: Select all
xlRight := -4161
Code: Select all
xlRight := -4152
xlToRight := -4161
https://msdn.microsoft.com/en-us/librar ... e.14).aspx
https://msdn.microsoft.com/en-us/librar ... e.14).aspx
Re: Excel COM Function Library
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!!
#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!!
- Thoughtfu1Tux
- Posts: 125
- Joined: 31 May 2018, 23:26
Re: Excel COM Function Library
This is awesome stuff! can't believe i've never seen this before.
Re: Excel COM Function Library
Nice work. I will test the code later. Thank you!
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: Excel COM Function Library
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.)
14.3 & 1.3.7
Return to “Scripts and Functions (v1)”
Who is online
Users browsing this forum: No registered users and 110 guests