Jump to content
seadoggie01

VSTO Add-In, COM, and AutoIt

Recommended Posts

Posted (edited)

Background: I have a project I've been working on at work. It involves getting an Excel document from an email, extracting data from the Excel document, inputting it into another Excel document, and uploading it to our server through ImageNow/Perceptive content. I made an AutoIt script that will do most of this with OutlookEx, VBA/VSTO Add-in (for the excel portion), and many hours. When I was exploring sharing data between Excel VBA and the VSTO Add-in, I found that I needed to make a COM class ... (to interface between VBA and .Net). I've been using COM in AutoIt for running my VBA macros ($Application.Run and $Workbook.Sheet(1)), so it got me wondering...

 

Can I create a COM object of my own and call it through AutoIt? If so, do I need to register it with Windows or can I reference the location of the dll to avoid registration?

Edited by seadoggie01

Share this post


Link to post
Share on other sites

As you have already used the OutlookEX to do the mail part, why not use the Excel UDF to do the Excel part?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-09-14 - Version 0.5.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I tried, but it's difficult and slow. The code is getting out of hand as the Excel documents that I'm trying to extract the data from are created from a bunch of different templates and often have multiple sheets with data that I don't need. With the add-in, I can easily throw it back to the user to select the necessary data if I have to. Also, because (IMHO) it's easier in .Net than AutoIt.

Share this post


Link to post
Share on other sites

I'm sure everything that can be done using .Net can be done with with AutoIt as well. With the Excel UDF it is easier as the functions are wrappers to hide complexity and to handle errors. If the UDF is used in a sensible way then it is fast as well.

Post an example and I will provide some AutoIt code :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-09-14 - Version 0.5.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Actually, I found a (nearly) perfect solution. I failed to realize that you implemented (or even that Excel allowed) ? and * in the Find dialog / _Excel_RangeFind(). By searching for cells matching 10 characters, I can find the bank account numbers. It is beautiful! :D And, as an added bonus, it only finds visible cells, so anything filtered is ignored. I ♥️ _Excel!

 

I am still interested in finding out if COM objects from VB.Net are register-able, but just out of curiosity now.

Share this post


Link to post
Share on other sites
15 minutes ago, seadoggie01 said:

I am still interested in finding out if COM objects from VB.Net are register-able, but just out of curiosity now.

 

Never used it myself but could be what you are looking for:

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-09-14 - Version 0.5.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

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

×
×
  • Create New...