Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Performing VBA operations in other languages

Status
Not open for further replies.

xwb

Programmer
Jul 11, 2002
6,828
0
36
GB
This is in response to Link. The reasons for choosing other languages is

[ul]
[li]Familiarity witht the language - if you are familiar with a language, it might be faster to code in that language rather than switch to another. In some projects, the mandate is to keep everything in the same language[/li]
[li]Office macros can be annoying - ever since Office 2013 they keep flashing away when formatting[/li]
[li]being able to generate excel/powerpoints on machines without having office installed. This is often a requirement in industrial systems where the PC just performs a form filling exercise.[/li]
[/ul]

The first time I came across this was in VBScript - you can run VBA macros in VBScript without having to load Excel. This has the advantage of not having excel up on the screen and the format line flashing away (you can no longer hide this after Office 2013). All it needs is the Excel.Application or Word.Application or Powerpoint.Application object. The equivalent code can be translated to JScript. It is just personal preference whether you prefer VBScript or JScript. The nice thing about using VBScript is it is similar enough to VBA that the code can be used almost interchangeably.

The same thing can be done in C#. VBA in C#. Like in VBScript, this needs Excel.Application, Word.Application or Powerpoint.Application so you still need Office to be installed.

The same thing can be done in TCL Excel in TCL, It just needs TCOM and Office to be installed. Once connected, all the vba calls are available in TCL. This can also be used in Word and Powerpoint. It is messy to start from scratch - easier with a template.

Python is slightly different. There are versions that do not require Office to be installed and versions that do. For instance, openpyxl Openpyxl can edit excel files without having office installed. This is quite useful on test systems which need to generate reports without having Office installed. No need for temporary licences and no fear of messages flashing up saying you have an illegal copy of office.

PyXLL is just a VBA substitute - you program in python instead of VBA but this needs Office.

Another option for not installing excel is to use the Jet engine (Microsoft.Jet.OleDB) or the access engine (Microsoft.ACE.OleDB). Jet goes quite far back - possibly W2K but only works in 32-bit. ACE is the Jet replacement, comes free with the later Windows OSs in flavours of 32 and 64-bit. This cannot access Word or Powerpoint but is brilliant for Excel and Access without having Excel or Access installed.
 
Ah! This is very, very different from what you implied in the original thread. This is mostly just simple Office automation.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top