So, for now this article is a list of links that I used to pull this off. It is VERY rough  but, I have LOTS of VBA code in Excel that I want to reuse but have Excel VBA triggered FROM THE WEB when a piping designer clicks on it. [Update: it fully works and is in use!] The link drives the existing VBA in excel which in turn, drives the running instance of Autocad and does lots of things – in this case I want to reuse my code that opens an autocad drawing, iterates through all the components until the one you want is found, then it zooms into an AutoCad component of the currently selected cell.

This link is in a SSRS (Report Services) page that we already have to find components based on the long and short description from AutoPLANT. Here is the round trip discussion and the links I used are at the bottom. I can’t believe I did this. Super cool.

• user puts in a description/key word into the report query
• a report comes up – user clicks on the component of choice (it is a custom url scheme) “mycustomapp://zoomInThisComponent?component_id=BASE64ENCODEDSTRING&drawingName=BASE64ENCODEDSTRING”
  • note there is some coding to do in T-SQL to base 64 encode stuff
• a c# app that is linked to that registered custom url scheme is triggered
• the current version of excel is accessed or it complains you don’t have excel started
  • it decodes the strings above and uses them to open the correct autocad files etc.
• the ALREADY WRITTEN VBA code in Excel then does what it always did
  • (that is why I have Excel in the mix – really you can do it all in .net – but why redo code? Well – ok because you can justify your client paying you for it but reusing it means you can move ahead and do cool things like this instead.
  • this code iterates through all components, looks at the AutoPLANT dictionary and finds the componetn id that matches the one you clicked on
  • then it zooms in
  • lots of code exists that closes the orignal drawing if it is open and IGNORES re-opening it if it is already loaded
  • again – I am reusing lots of code.

Super cool and it works like a charm. VERY EASY – not so straight forward. I have a working prototype in about 3 hours.

 

Here are my links:

• How to iterate through ALL instances of Excel (my article)
• Command line parameters in VBA

http://www.vbforums.com/showthread.php?366559-Excel-How-to-Pass-Command-Line-Parameter-DKenny-is-KING!

https://social.msdn.microsoft.com/Forums/office/en-US/6124691b-a5ce-4fc0-a648-e85b4a32f77a/excel-vba-to-read-command-line-parameters-and-copy-in-a-cell?forum=exceldev

 

• Odd that I had to google this – but selecting a simple cell in c# has some sommands that do NOT pull up with intellisense.

http://stackoverflow.com/questions/2513968/how-to-focus-a-cell-in-excel-vsto-using-c-how-to-select-first-cell-using-c-sha

 

• How to fake highlighting autoCad components since .Highlight() just could care less about doing its job (Transient Graphics)

http://drive-cad-with-code.blogspot.ca/

 

• Excel Command line options

https://support.office.com/en-us/article/Command-line-switches-for-Excel-321cf55a-ace4-40b3-9082-53bd4bc10725

 

• Registering an application handler for a custom uri scheme

https://msdn.microsoft.com/en-us/library/aa767914%28VS.85%29.aspx

http://stackoverflow.com/questions/3057576/launch-application-from-a-browser

 

• Running a macro in VBA from .net

http://stackoverflow.com/questions/14248592/running-an-excel-macro-via-c-run-a-macro-from-one-workbook-on-another

 

• Running excel functions on a local computer from .net using Interop.Excel

http://stackoverflow.com/questions/10477977/what-reference-do-i-need-to-use-microsoft-office-interop-excel-in-net

 

• Get the current running version of Excel using GetObject … but in c#

https://social.msdn.microsoft.com/Forums/vstudio/en-US/71044a7d-5424-4c84-94d3-3b29f84b1d2f/net-equivalent-of-createobject-and-getobject

 

• Adding a pause to the command line app

http://stackoverflow.com/questions/7280591/read-user-input-from-console

 

• Confirming command line arguments in c# app

http://stackoverflow.com/questions/7280591/read-user-input-from-console

 

• VBA version of Base64Decoder class

http://www.source-code.biz/snippets/vbasic/Base64Coder.bas.txt

http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=267:excel-vba-empty-zls-null-nothing-missing&catid=79&Itemid=475

http://stackoverflow.com/questions/11675454/how-to-parse-url-parameters-in-vba

 

• Calling VBA Functions from c#

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.run.aspx

 

• c# encoding base64

http://stackoverflow.com/questions/11743160/how-do-i-encode-and-decode-a-base64-string

 

• I had to do the encoding in SQL by making a scalar function – why? Because doing it in SSRS is too much – why not do it in SSRS

http://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/