"Madiya" wrote in message
...
On Mar 25, 2:36 pm, "Peter T" <peter_t@discussions wrote:
"BEETAL" wrote in message
Please tell me how to go about using VB6. Just spend few lines and
direct
me
First 3 caveats
- VB6 is no longer officially supported by MS
- For the future it will not work with 64 bit Office as an ActiveX (not
sure
what the long term predominance of 64 bit will be)
- You will need to obtain the VB6. If you have an MSDN subscription you
can
get it free, otherwise from a third-party, eg eBay
OTH: It's by far the easiest of all the languages to migrate to (well,
it's
not a different language) and it works particularly well with Office; in
some respects better than its replacement vb .Net in respect of
communicating with Excel.
Although you can make an exe to automate Excel typically you make a VB6
ActiveX dll. You can call this with your own VBA code, as a wrapper where
your code simply calls a function in the dll passing any relevant
parameters. The function might return a value or object or maybe simply
load
a form and take it from there.
The aX could also be a ComAddin (from XL2000) or used as an
AutomationAddin
(from XL2002) if it's only purpose is UDFs. No VBA at all.
The VB6 form is a bit of a learning curve, superficially it looks like a
Userform but it isn't the same. Alternatively you can simply drag your old
VBA userform into the VB6 project and surprisingly it will work.
Unless you have VB6 to hand there isn't much point giving code examples
here, but there really isn't much to it. The main thing to watch out for
is
all code to Excel objects must be fully qualified back to the application,
eg xlApp.ActiveCell. Similarly to call Excel's VBA functions, say
xlApp.Union(args)
Regards,
Peter T
Hi Peter,
I am quite intrested in this com addin dll specifically for the code
security.
I need to connect to sql and pass on some commands which will return
few rows of data to be written back in excel.
Can you pl provide some code examples or point me to the same?
Here's my connection string and a sample command used in VBA.
=====================
Dim cn As New ADODB.Connection
Dim VAL, UPVAL
cn.Open "Driver={SQL
Server};Server=Madiya;Uid=sa;Pwd=tgpata;Database=G 1"
cn.Execute ("truncate table [check]")
=====================
Also to run the same I want to add a button in the context menu.
Using XL2007.
Regards,
Madiya
-------------------------------------------
Hello Madiya,
All the SQL stuff will be identical in VB6, so you should be able to port it
from VBA as-is. You would though need to qualify any references back to
Excel when it comes to dumping back any data to a sheet. You would add the
ADODB reference as you would in VBA, albeit from a different place in the UI
As a ComAddin you need to add a special class that implements
IDTExtensibility, in particular to trap the Excel host's OnConnection &
OnDisconnection events (template included with VB6). By default the class
will be named "Connect". Briefly, when Excel starts a reference to Excel is
passed to VB6 in the OnConnection event which you maintain globally as say
xlApp until Excel quits.
Menu buttons can be added the same way as in VBA with the VB6 app responding
to the button's click event, using withevents.
For 2007 it's not straightforward at all to add buttons to the Ribbon with
appropriate callbacks, a learning curve to get the xml not only right but in
the dll, but it can be done. Much simpler to do it the old way which will
put buttons in the Addins tab.
Regards,
Peter T