Excel COM Function Library

[Tre4shunter]

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

[oldbrother]

It's great! thanks for sharing.

[Tre4]

[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]

[UPDATE]

Added new functions:

XL_GetHPageBreaks()
XL_GetVPageBreaks()

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

Thanks!

Matt

[RNDLDVL]

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

---------------------------
plotter.ahk
---------------------------
Error in #include file "C:\Users\Michael\Desktop\XLFunctions-master\XLFuncs.ahk":
0x80020005 - Type mismatch.


Specifically: Find

Line#
259: }
294: {
295: xla := XLCheck(Name)
296: if xla = False
297: Return
298: Sht := ((sht="") ? xla.activesheet.name : sht)
300: Rarr := []
---> 301: RTS := xla.sheets(Sht).range(Range).Find(What,xla.sheets(Sht).range(After),LookIn,LookAt,Order,direction,1)
302: First := RTS.row
303: Loop
304: {
305: Rarr[A_Index] := RTS[Dtype]
306: RTS := xla.sheets(Sht).range(Range).FindNext(RTS)
307: }
307: Until,(RTS.row=First)

Continue running the script?
---------------------------
Yes No
---------------------------

Spoiler

---------------------------
plotter.ahk
---------------------------
Error in #include file "C:\Users\Michael\Desktop\XLFunctions-master\XLFuncs.ahk":
0x800A03EC -
Source: Microsoft Excel
Description: Unable to get the FindNext property of the Range class
HelpFile: xlmain11.chm
HelpContext: 0

Specifically: FindNext

Line#
298: Sht := ((sht="") ? xla.activesheet.name : sht)
300: Rarr := []
301: RTS := xla.sheets(Sht).range(Range).Find(What,xla.sheets(Sht).range(After),LookIn,LookAt,Order,direction,1)
302: First := RTS.row
303: Loop
304: {
305: Rarr[A_Index] := RTS[Dtype]
---> 306: RTS := xla.sheets(Sht).range(Range).FindNext(RTS)
307: }
307: Until,(RTS.row=First)
309: Return,Rarr
310: }
334: {
335: xla := XLCheck(Name)
336: if xla = False

Continue running the script?
---------------------------
Yes No
---------------------------

[sinkfaze]

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

[tr34shunter]

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]

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.

[Tre4shunter]

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)!

[RNDLDVL]

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]

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

[MGGowtham]

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!!

[Thoughtfu1Tux]

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

[Haan]

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

[flyingDman]

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.)