This one is For all the accomplished MVPs.
in [Programming]
|
From: Peter T on 25 Mar 2010 05:36 "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 Automation Addin (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
From: Madiya on 8 Apr 2010 03:02 On Mar 25, 2:36 pm, "Peter T" <peter_t(a)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=G1" cn.Execute ("truncate table [check]") ===================== Also to run the same I want to add a button in the context menu. Using XL2007. Regards, Madiya
From: Peter T on 8 Apr 2010 10:26
"Madiya" <madiya122(a)yahoo.co.uk> wrote in message news:c9032fed-4fc1-4d76-b786-59254086a0ae(a)x12g2000yqx.googlegroups.com... On Mar 25, 2:36 pm, "Peter T" <peter_t(a)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=G1" 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 |