Sign in to follow this  
Followers 0
dinodod

Anyone have Excel scripts for Office 2003 & 2007

8 posts in this topic

Does an yone have any working scripts for using excel 2003 & 2007? I found 1 script that I tried with office 2007 but I think it didn't work since it calls the 2003 object and 2007 might be different? I would be surprised if anyone was using 2007 office yet.

Thanks!


Digital Chaos - Life as we know it today.I'm a Think Tank. Problem is, my tank is empty.The Quieter you are, the more you can HearWhich would you choose - Peace without Freedom or Freedom without Peace?Digital Chaos Macgyver ToolkitCompletely Dynamic MenuSQLIte controlsAD FunctionsEXCEL UDFPC / Software Inventory UDFPC / Software Inventory 2GaFrost's Admin Toolkit - My main competitor :)Virtual SystemsVMWAREMicrosoft Virtual PC 2007

Share this post


Link to post
Share on other sites



I have had a chance now to use Office 2007 with my ExcelCOM UDF (originally written for 2003) and it works perfectly. If you have Office 2007 (trial version or otherwise) on a machine that also has 2003, then whichever version was installed most recently will probably be the default object called by ObjCreate("Excel.Application"), though I cannot confirm that at present.

To call the proper object in a situation where both versions are installed, you'd need to specify which object library to use explicitly.

ObjCreate("Excel.Application.11") ; This will call Excel 2003

ObjCreate("Excel.Application.12") ; This will call Excel 2007

In any script that was made for Office 2003, you can force it to use Office 2007 COM by doing a "replace all," using "Excel.Application" as your search criteria string, and "Excel.Application.12" as your replacement string.

Of course, you can always just uninstall 2003.

Hope that helps!

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

AWESOME! I will give it a try! I've seen another posting for a EXCEL UDF but it had some issues that I didn't look at much.


Digital Chaos - Life as we know it today.I'm a Think Tank. Problem is, my tank is empty.The Quieter you are, the more you can HearWhich would you choose - Peace without Freedom or Freedom without Peace?Digital Chaos Macgyver ToolkitCompletely Dynamic MenuSQLIte controlsAD FunctionsEXCEL UDFPC / Software Inventory UDFPC / Software Inventory 2GaFrost's Admin Toolkit - My main competitor :)Virtual SystemsVMWAREMicrosoft Virtual PC 2007

Share this post


Link to post
Share on other sites

How did you learn to code Autoit for Excel? I'm seeing more and more windows apps getting coded and I'm not sure where to begin myself.


Digital Chaos - Life as we know it today.I'm a Think Tank. Problem is, my tank is empty.The Quieter you are, the more you can HearWhich would you choose - Peace without Freedom or Freedom without Peace?Digital Chaos Macgyver ToolkitCompletely Dynamic MenuSQLIte controlsAD FunctionsEXCEL UDFPC / Software Inventory UDFPC / Software Inventory 2GaFrost's Admin Toolkit - My main competitor :)Virtual SystemsVMWAREMicrosoft Virtual PC 2007

Share this post


Link to post
Share on other sites

Right on. I just thought of something else - make sure during the search and replace that you first search for "excel.application." (note the dot on the end) in case whoever wrote the script hard-coded an object library version in there already.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

How did you learn to code Autoit for Excel? I'm seeing more and more windows apps getting coded and I'm not sure where to begin myself.

Excellent question. First, as a developer I've been exposed to both VB Script and VBA. I used to have to program Excel and Word macros as part of a previous job. Since Excel's VBA object system is essentially just routed through Excel's COM interface, all I had to do was figure out how to use AutoIt COM to start using Excel COM.

There are only minor differences between VB Script code and AutoIt code when it comes to dealing with COM. Once you know what those little things are (primarily variables), you're all set.

To learn Excel COM, there are several good books out there. The last time I was at a Borders bookstore, I saw an entire bookshelf dedicated to books dealing with Office automation. My favorite is, "Programming Excel with VBA and .NET," by Steve Saunders and Jeff Webb. It's a godsend.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Awesome! I've done a little Excel '95 programming years ago so i dont remember anything now, not that it hasn't changed much :whistle:. While I have scripted in several languages (vb, vbs, ASP, and now AUTOIT!!!), I am by far a jack of the trades rather than a master of any. I am not paid to be a programmer but I sure do love VB / VBS / AUTOIT / SQL so i do it on the side as time permits.

I've already used your code and credits to you and the original author that you mentioned.

I am working on my Autoit version of a SQLite DB manager (Like the SQL Manager in SQL 2000). It's working like a champ! But I do have a question now. rather than opening a new spreadsheet in it's own window, is there any way to have it open the spreadsheet in a GUI in autit?

In my app, I have a treelist of the DBs and Tables on the left side. On the right side, I want to display the data in the tables using your code. I've seen something like this before but I can't recall where now :)

Any thoughts?

Edited by dinodod

Digital Chaos - Life as we know it today.I'm a Think Tank. Problem is, my tank is empty.The Quieter you are, the more you can HearWhich would you choose - Peace without Freedom or Freedom without Peace?Digital Chaos Macgyver ToolkitCompletely Dynamic MenuSQLIte controlsAD FunctionsEXCEL UDFPC / Software Inventory UDFPC / Software Inventory 2GaFrost's Admin Toolkit - My main competitor :)Virtual SystemsVMWAREMicrosoft Virtual PC 2007

Share this post


Link to post
Share on other sites

Sure. You can use ptrex's example of an embedded OWC spreadsheet:

http://www.autoitscript.com/forum/index.php?showtopic=17802

Be advised that this object supports only a subset of Excel's features. Most importantly, you cannot load a spreadsheet directly into it. The OWC spreadsheet can be told to import data from a web page, a text file, or an XML file. It can also be told to connect to an external data source, i.e. a SQL database server. Barring these options, you're limited to copy & paste or direct cell-by-cell entry.

Good luck with your Excel COM endeavors!

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

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