Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Code Snippets / [DBP] - [STYX, Matrix1, Blue GUI] Excel interface via COM/ OLE

Author
Message
Manson_NS
19
Years of Service
User Offline
Joined: 4th May 2004
Location: Denmark
Posted: 21st Mar 2014 23:23 Edited at: 22nd Mar 2014 12:15
I have been looking back and forth for a reasonable interface with Excel, with little to no luck. So while it may be freely accessible on the forum elsewhere, apparently I felt the need to do this.
I do in no way claim this is the most efficient, but it works, and I wanted to share it in case others can't find such functionality.

It unfortunately* requires STYX for the COM-interface - since Styx is clashing with Matrix1, I had preferred to avoid STYX - so if anybody has an alternative interface for COM, it'll be highly appreciated! Also, the call for functions with multiple parameters does not return data with STYX.

There's an abundance of functions available in the COM interface, but I'm evidently not bright enough to get all of them to work, so again - if anybody feels like tweaking this, there's some obvious optimizations to be made

Edited, 22/03-2014:
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 22nd Mar 2014 00:19 Edited at: 22nd Mar 2014 00:20
Crashes for me during open_Excel_doc, on the line make com instance instance,WORKBOOK, because instance has the value 0.

Been looking for a good way to read/write excel files so I will watch this.

Manson_NS
19
Years of Service
User Offline
Joined: 4th May 2004
Location: Denmark
Posted: 22nd Mar 2014 10:30
Well, this code is completely undocumented, which is simply because there's some rather big improvements still to be made, so I've not added the fault checks and whatnot to make it work in every scenario.

I have rectified the code above to check for whether reference is made to an existing file - otherwise it'll produce a new sheet.

The element to toy with is the visibility flag - it can run completely in the back, feeding the data directly to DB
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 22nd Mar 2014 17:10
Got it to work with your new updates.

How much more do you have to do on it before you think you got everything covered? Have you planned for things like cell formatting, sizes, etc?

Pretty neat stuff though!

Manson_NS
19
Years of Service
User Offline
Joined: 4th May 2004
Location: Denmark
Posted: 22nd Mar 2014 19:38
Well through the COM interface, all of Excel is accessible, so there's really too much functionality to cover it all.
For my initial purpose, all I need from this is to read and write Excel documents, so I don't think I'll add tons more for the time being.
My focus now would be to speed it up, since this is now an obvious tool over VBA based macros.

Login to post a reply

Server time is: 2024-03-29 14:34:37
Your offset time is: 2024-03-29 14:34:37