Sign in to follow this  
Followers 0
AutoITPimp

MS Excel UDF Support

11 posts in this topic

I believe there is another thread around here about using Excel UDF, however, I found the functions therein very clunky for my taste. Nothing against the person who created them, he did great work and deserves credit. I just wanted to try and create some simpler utility functions for working with the Excel for my purposes and I'm posting them here for all to share.

If you have suggestions, tweaks, or functions of your own to add. Let me know and I will add them and give you credit.

I used Dale Holm's format for the IE.au3 file as a template, and his creation of that file and functions inspired me to create my XLS.au3 file. I will be adding funtions as I have a need, and/or time to work on it. I alsoplan on adding all of the Excel constants to the file, I have them in a spreadsheet and just need to import them into the .au3 file.

Anyway, hope this helps someone out.

I can post examples of how to use them later if anyone needs them.

XLS.au3

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

I am more interested in opening multiple excel files and would like to re-activate an excel file in single document interface(SDI)...it works on MDI but have trouble in SDI B)

would there be UDF for Words and PowerPoint too?

Edited by proyb2

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

in case you want to be more compatible with UDF standard coding

Edited by jpm

Share this post


Link to post
Share on other sites

in case you want to be more compatible with UDF standard coding

Hi jp,

[Note: I'm reluctant to jump in here because I don't want to hijack someone else's thread -- so if this discussion ends up warranting it, let's please take it somewhere else.]

I'm curious about the changes you made to make these functions "more compliant" since this uses essentially the same conventions I used in IE.au3

For IE.au3, I used the standards published by JdeB here (more spfifically, User Defined Functions Standards)

In particular, I noticed that you changed his boolean variables from $f_ to $b prefixes, his strings from $s_ to $sz (as well as taking the underscores out of the names). According to the template, $f is for boolean and $b is for binary, $s is for string and 'z' is not defined. The underscore is used in the template examples.

So if the changes you made are what we want, then the template needs to be updated and I would be anxious to comply to the correct standards as well.

Dale

p.s. @AutoIt Pimp -- you've created this thread in the Developers Forum just as I did with IE.au3... I'll admit that I've always regreted creating my thread here instead of in Scripts and Scraps. It made some sense to me when I first created the thread, but in retrospect the other forum would have been more appropriate. You may want to consider redirecting your thread before this one gets too much activity.


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

First, great work! I plan to use this.

I've always wondered why anyone would start with 3 worksheets in a new workbook other than Microsoft assumed noobs would be "lost" if they only had one tab and needed another one. I'd rather see the default number of sheets set to 1 and have a function to add sheets, which I don't see in the code.

Below is a list of functions I'd like to see added. I would look into it myself if I had time right now.

  • Maybe change _XLSNew to _XLSNewWB or _XLSNewWorkbook
  • Create new worksheets, maybe as _XLSNewWS or _XLSNewWorksheet
  • Delete worksheets, maybe as _XLSDelWS or _XLSDeleteWorksheet
  • Reorder worksheets, maybe as _XLSMoveWS or _XLSMoveWorksheet
Again, nice addition!

My UDFs: ExitCodes

Share this post


Link to post
Share on other sites

Hi jp,

[Note: I'm reluctant to jump in here because I don't want to hijack someone else's thread -- so if this discussion ends up warranting it, let's please take it somewhere else.]

I'm curious about the changes you made to make these functions "more compliant" since this uses essentially the same conventions I used in IE.au3

For IE.au3, I used the standards published by JdeB here (more spfifically, User Defined Functions Standards)

In particular, I noticed that you changed his boolean variables from $f_ to $b prefixes, his strings from $s_ to $sz (as well as taking the underscores out of the names). According to the template, $f is for boolean and $b is for binary, $s is for string and 'z' is not defined. The underscore is used in the template examples.

So if the changes you made are what we want, then the template needs to be updated and I would be anxious to comply to the correct standards as well.

Dale

p.s. @AutoIt Pimp -- you've created this thread in the Developers Forum just as I did with IE.au3... I'll admit that I've always regreted creating my thread here instead of in Scripts and Scraps. It made some sense to me when I first created the thread, but in retrospect the other forum would have been more appropriate. You may want to consider redirecting your thread before this one gets too much activity.

You right I was C convention oriented assuming the UDF are the same . Sorry for the bad post did I will remove it.

In waht I post I correct too a missing parameter definition. That's the only improvement I can suggest.

I will remove my attached file B)

Share this post


Link to post
Share on other sites

Hi jp,

[Note: I'm reluctant to jump in here because I don't want to hijack someone else's thread -- so if this discussion ends up warranting it, let's please take it somewhere else.]

I'm curious about the changes you made to make these functions "more compliant" since this uses essentially the same conventions I used in IE.au3

For IE.au3, I used the standards published by JdeB here (more spfifically, User Defined Functions Standards)

In particular, I noticed that you changed his boolean variables from $f_ to $b prefixes, his strings from $s_ to $sz (as well as taking the underscores out of the names). According to the template, $f is for boolean and $b is for binary, $s is for string and 'z' is not defined. The underscore is used in the template examples.

So if the changes you made are what we want, then the template needs to be updated and I would be anxious to comply to the correct standards as well.

Dale

p.s. @AutoIt Pimp -- you've created this thread in the Developers Forum just as I did with IE.au3... I'll admit that I've always regreted creating my thread here instead of in Scripts and Scraps. It made some sense to me when I first created the thread, but in retrospect the other forum would have been more appropriate. You may want to consider redirecting your thread before this one gets too much activity.

More in can move your topic in Scripts and Scrpas if you want as I have been elected some weeks ago to administrator Level. B)

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

First, great work! I plan to use this.

I've always wondered why anyone would start with 3 worksheets in a new workbook other than Microsoft assumed noobs would be "lost" if they only had one tab and needed another one. I'd rather see the default number of sheets set to 1 and have a function to add sheets, which I don't see in the code.

Below is a list of functions I'd like to see added. I would look into it myself if I had time right now.

  • Maybe change _XLSNew to _XLSNewWB or _XLSNewWorkbook

  • Create new worksheets, maybe as _XLSNewWS or _XLSNewWorksheet

  • Delete worksheets, maybe as _XLSDelWS or _XLSDeleteWorksheet

  • Reorder worksheets, maybe as _XLSMoveWS or _XLSMoveWorksheet
Again, nice addition!

Historical note:

When Excel first came out for PCs, the default was 16 sheets. B)

Edit: Was going to but didn't.

Edited by Gene

[font="Verdana"]Thanks for the response.Gene[/font]Yes, I know the punctuation is not right...

Share this post


Link to post
Share on other sites

p.s. @AutoIt Pimp -- you've created this thread in the Developers Forum just as I did with IE.au3... I'll admit that I've always regreted creating my thread here instead of in Scripts and Scraps. It made some sense to me when I first created the thread, but in retrospect the other forum would have been more appropriate. You may want to consider redirecting your thread before this one gets too much activity.

If you would like Dale, I can move the thread to Scripts & Scraps and leave behind the link in this forum. It's your call, however.

Share this post


Link to post
Share on other sites

Thank you all for your responses, and to those with suggestions and requests, I will work on them. I've been crazy busy with work and have never had the chance to come back and revisit this thread. I'm hoping to have that free time in the very near future.

@jpm - I appologize if I was out of line with regard to style guidelines. I'm still quite new to this community and am learning as I go. I mainly built the functions using Dale's model and from some Excel work I've done using VB in the past. Saw there really wasn't anything on Excel out here, and decided to share. In retrospect, as Dale mentioned I probably should have this moved to Scripts & scraps. I posted here because this is where he put the IE UDF thread, so I figured this most be the place for these types of things. So if you could move it, and maybe leave a pointer from here that would be great. Can you also change the title to "MS Excel UDF Support"?

@c0deWorm - Great suggestions I will work on them.

@DaleHohm - Thanks for the compliments and advice.

@proyb2 - Great suggestions for additions to the UDF file. Thanks. I will try to work that in. As for Word and Power Point, I'd say Word should be coming in a little while as I have some VB functions for Word I should probably convert. I don't do a lot of Power Point mainly because I don't like it, but I'd venture to say I could tackle that job if there was a request for it.

Anyone else interested in contributing please feel free to do so.

Finally, if anyone with StdIn / StdOut knowledge or TCP connection could help me out on some stuff, I'd greatly appreciate it. Basically, I need to query data, files, processes, etc. across an SSH connection. I currently log in through PuTTY, but I can't read the replies so if I could use StdIn / StdOut through PLink that would be cool, or if I could create my own "terminal" for making these connections with some TCP support that would be awesome.

Share this post


Link to post
Share on other sites

Danka

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0