Posts by XL-Dennis

    Re: New MS Excel MVP


    Wigi,


    Get back to work now ;-) You know the code for MS MVPs; To provide knowledge & support to the Online Excel Community.


    Welcome onboard!

    Re: Free Tool - .net Co Library


    Hm, I used the same software for them and with a template. I don't know why You get this different outcome. Delete the file and test by do a new download of the file.

    Hi all


    I thought I would make a post here to let you know that my new free managed COM Add-in for Excel 2000 - 2007 now is available for download.


    The tool is rather unique (in addition to the fact that it's a 100 % managed COM add-in) as it allow you to store all kind of VBA code and reuse them whenever wanted.


    In addition, it can create workable connection string with .NET Wizard as well as with the Data Link Wizard. These connection strings can be stored and reused whenever wanted.


    I take this opportunity to explicit thanks Ken (Puls) and Ross (Mclean) for their kindness to be beta-testers of the tool.


    For more information and download please see:
    .NET Co Library

    Re: Happy Birthday Dennis


    Hey MBTCM


    Great work You and Roy have done :) It looks professional and has a nice structure. Would it be difficult to incorporate the blogs (with WordPress software) with the site & forum?


    YBTCM Dennis

    Re: Happy Birthday Dennis


    Hey guys :)


    I hope I'm the right Dennis around here ::D


    Many thanks for Your kind words and things are looking better so let see what the future will bring. Take care and all the very best from,


    Dennis:viking:

    Re: .net Connection Tool


    TJ,


    Thanks for Your kind words :)


    Quote


    Knowing your previous work, I expect it to work very well.


    When developing solutions nowadays I test them on the following 'systems':
    Windows 2000 - Excel 2000
    Windows XP - Excel 2003
    Windows Vista - Excel 2007


    (My host operating system is UBuntu where I run vmWare for the above 'systems')


    One funny thing is that today all kind of softwares are always in the beta stage and whenever a new final version is released a new beta is also around. I can see the point with it for large software corporates but not for add-ins to the Office suite.


    In addition, security has become more important now then in the past so the biggest challenge tend to be to leverage the security aspect on the .NET platform and particular with Windows Vista. I put value in the security aspect although it's not a painless process.


    All in all, You should expect that my solutions should work :)


    Hi Roy,
    Things are looking better and better so now I plan for, at least, 2 months ahead :) Thanks for moving the thread to a more appropiate subforum. Oz has become a huge spot on the Internet so it takes sometime to find my ways around.

    Hi all,


    I just want to inform that I have released version 1.0 of my free .NET Connection Tool for MS Excel. The present version can create and store connection strings for all kind of databases. It can use the Data Link Wizard as well as the .NET based Wizard to create workable connections. New features will be added later on but it will not include all the new features that the upcoming .NET version of SQL Tester will have.


    For more information please see: .NET Connection

    Re: Addin Registry


    Hi Richard.


    TJ - Good to see You around ;)


    Let see if we can solve it in a smooth way :)


    Primo - Where to store the main XLA file?
    If we want to store it in a customized folder and at the same get it activated in Excel then it require that the installation tool in use can:


    Read the Windows Registry entries and pick up the next available number in the subkeys that hold the activated XLAs in Windows Registry. These subkeys have the name of "OPEN" and a number included like "OPEN9".


    This subkeys can be located at the following path in the Windows Registry:
    HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options


    The present package tool included in Visual Studio 2005 does not provide any help with it.


    I know that Jan Karel (Pietersen) has a guide about creating a setup utility in Excel: http://www.jkp-ads.com/articles/DistributeMacro10.htm


    In addition, the only installation tools I'm aware that can solve it a smooth way is Wise, InstallShield and Visual Installer (a Swedish installation tool in which I've been involved to develop the XLAs installation solution). However, I'm probably not fully updated so perhaps there exist additional tools that solve it nicely.


    An interesting installation tool is WiX that use XML files but it's beyond the scope of this post to discuss it further.


    Anyway, my recommendation is simple to place the main XLA in the same folder as the underlying XLAs and XLT. Instruct the users on how to activate it via Excel's UI.


    Secundo - How to handle the underlying XLAs?
    First of all, in order to 'hide' them from users You should change the file extension from 'xla' to something else.


    Here I use the file extension 'xldennis' and the full name of the XLA is: Tool1.xldennis


    Second, add-ins can be loaded in two ways, either via the open command or by activating them via Excel's UI.

    Since we have changed the file extension we need to open them with the open statement as the following snippet code shows:


    Code
    1. Const Too1 As String = "c:\My Tools\Tool1.xldennis"
    2. Application.Workbooks.Open (Tool1)


    Closing
    I hope that the above give You a solution that can be used to get things work properly.

    Re: Addin Registry


    Richard,


    If I understand it correctly You want to avoid a situation where some of Your XLAs are viewable via Excel's UI, i e in the add-in dialogform.


    One easy and simple approach is to use another file extension then ".xla" and then You can load them via the main XLA.


    In general it's a good strategy to keep all related files in one folder.


    Quote


    Is it also not a better idea to registrate the addin with installation from a VISTO.exe directly into


    If You refer to the main XLA then I agree. I'm not sure what "VISTO.exe" refer to but I assume it's an installation tool that can write to the Windows registry to install and activate the main XLA.

    Re: Vba, R.i.p?


    Quote


    It easier in terms of the intergration of xl within the VBE. Using drag and drop to construct UIs for instance.


    Yes, there exist some advantages but they are well "compensated" with new complex stuff.


    To deploy .NET-solutions is challenging and with Vista's new security model including User Account Control (UAC) it's at best described as very challenging.


    Quote


    ... that in order to add a button to the ribbon to run a macro you need to edit xml prior to using/opening the workbook. There is no way to build the button on the fly from within the workbook.


    One thing we can agree on is the fact that the the Ribbon is here to stay.
    We can also fully agree on that it was not the group of developers MSFT had in mind when they designed the new UI.


    Initially I didn't like the Ribbon UI at all. However, when I decided to not view it from the experience with classic commandbars I saw the benefits the new UI actually provides.


    Creating customized XML files with the RibbonX code may "on its face" look difficult and complext but is both logical and understandable. By implement the IRibbonExtensibility we get the callbacks. It's possible to alter the visibility etc and also via callbacks control what will be done.


    The following coming article in MSDN Magazine gives a basic introduction:
    Extend The 2007 Office System With Your Own Ribbon Tabs And Controls


    Patrick Schmid has a forum dedicated to Ribbon which gives additional knowledge and also the possibility to discuss and raise questions.

    Re: Vba, R.i.p?


    Quote


    My biggest concern is that whilst MSFT are making things easier for developers they are causing everyday users to question where they fit into MSFT and Offices future.


    Easier in terms of :confused:


    I'm not 100 % convinced that MSFT actually has made it easier for developers.


    Quote


    With xl2007 they may have made it easier to use but it's more difficult to control.


    I like the RibbonX and as a developer I'm forced to work with it from a different approach then with classic commandbars :)

    Re: Vba, R.i.p?


    Andy,


    Quote


    In fact I would suggest that if you are learning a language choose C#.


    You've been listening too much to MSFT : D


    C# is more of "plumbing" then what VB.NET ever will be which can either be interpretated as "more flexible and gives more control" or "using C# with Office is like ---".


    Developers who's coming from the business side tend to set focus on the business requirements and therefore usually don't pay attention to the detailed underlying plumbing. Developers with a technical background are used to be in control and therefore prefer to use C# when working with the .NET platform.


    The good news is that MSFT seems lately to realize that there still exist a great number of developers that prefer VB.NET then C# ;)

    Re: Vba Ado Delete Database Records


    postman2000


    #1

    Code
    1. Dim dbPath, dbName As String
    2. 'Path & FileName to the Database File
    3. dbPath = M.Range("G2").Value
    4. dbName = M.Range("G3").Value


    (A small remark: The first declared variable is interpreted by Excel as a Variant variable and not as a String variable so in general we should avoid to try to implicit declare variable's datatypes in that way.)


    The keypoint here is that if these two values are fixed, i e never changed or rarely changed then it's preferrable to use constant variables like the following:


    Code
    1. Const stPath As String = "g:\......"
    2. Const stName As String = "Test.mdb"


    Of course they could be only one as well. When the code is executed then Excel will not be forced to spend some time to first instantiate the variable(s) and then populate the variable(s) with values as this is now done in "one go".


    From a maintance point of view it's easier to change the values in code and we will also get a better structure as code is separated from the user interface (ie the worksheet interface).


    #4 Error handling
    I had connection pooling in mind when writing some of the comments and I believe it may be too complicated to cover in a thread like this one. Except for that, the number of users is small number so please disregard that part.


    If You include both an error handling and one "exit place" in the code Your solution will be more bullet proof:



    #5 No but since it was not explicit mentioned in Your first post I thought I should point it out :)


    #6

    Quote


    ...in a very controlled computer environment...


    That's very good :)
    I believe that many developers would appreciate that very much ;)