Posts by XL-Dennis

  • ADO Delete Database Records

    Re: Vba Ado Delete Database Records


    Yes, it's too simple ;)


    #1 You should make sure that there exist "correct" values in the cells. If the cells are locked or hidden then You should use constant text variables instead which will improve the performance.


    #2 You should add an appropiate error handling including user friendly error messages. The error handling should also include any ADO generated errors.


    #4 Make sure that the connection is closed properly including scenarios where any error may occur.


    #5 Your present code imply that the selected value does exist in the DB. If that is not the case the code should give the end users information about it.


    #6 You use early binding and therefore make sure that the targeting machines has the referring version or later of the ADO External Library Object. If not then use late binding.


    #7 Spend some time in the advanced section here and You will find solutions to most of the above inputs.


    Quote


    Thanks in advance....


    You're welcome :)

  • A Survey Of Spreadsheet Users

    Re: A Survey Of Spreadsheet Users


    Hi Carl,


    In general I believe it's difficult to measure the impact Excel has on productivity. But it does not means it's not doable however it will propably require deeper interviews with people in order to 'catch' the impact.


    In the late 90's I helped a larger Swedish corporate where we actually measured the time saved with the implementation of an Excel based tool. For a group of around 45 projectmanagers the solutions saved 80 - 90 minutes minutes per week for each of them.


    As for the information about spreadsheet risk it should be read in view of that many large BI corporates have difficulties to sell their $$$ tools as Excel is compeeting with them. That's why we see so much reports about risks with Excel.


    All the very best from,
    Dennis

  • Books by Andy

    Re: Books by Andy


    Andy,


    In general the publishers are only looking for new stuff that can attract larger groups of readers. That's why we will only see new books about Excel 2007 and with the subtitle 'How To:". The authors will be the same one as they are most known and therefore sell more copies.


    Other new books that will be available are books that will cover VSTO but only for the whole Office Suite.


    This time it will be focused on the 'happy' marriage between Office 2007 and VST 3.0. As with most of the available VSTO books they will spending more then 85 % of the pages to explain the object's models since they only target enterprise developers.


    Of course, there will propably be available some poor books or chapters about Ribbon, XML and Callbacks function.


    What I would like to see is books that cover:


    Spreadsheet Design (This subject should be suitable to cover in 1 to 2 books).


    Standards and Best Practice for spreadsheeting (This subject should be suitable to cover in 4 to 6 books and cover all available Excel versions).


    Projects that take the readers from step 1 to the final step for developing solutions (with classic VB and VB.NET) for Excel 2000 to Excel 2007, including security and deployment. (This subject should be suitable to cover in 5 books where the last book will only set focus on deployment).


    Some of the above subjects need a deeper understanding of .NET Framework and .NET Framework's security.


    I would be very pleased if I can review some of these books : D


    All the very best from,
    Dennis

  • Books by Andy

    Re: Books by Andy


    Hi Dave :)


    Yes, carlmack recently made a post about OWC Chart and XML files so I made an unexpected visit here ;)


    It's good to see so many good people around Oz who also providing a highly qualify help for many people. Keep up the good work but don't forget to take part of the real life as well ;)


    All the very best from,
    Dennis

  • Thank You and goodbye

    Hi all,


    This is my last post here in my corner of the Excel-world. In the end of last year I decided that I would make a ‘reboot’ when it comes to the time I put into ‘the world of softwares’. It’s not easy since it will mean that I will start from scratch and become a newbie again. Well, perhaps not as a real newbie as I have my experience with Excel et al with me.


    I’m very pleased to see how well OzGrid has developed since its start for 3 years ago. At that time we didn’t know what the outcome would be but today we know that OzGrid has become an important spot in the virtual global Excel community. The best part is not the number of members or the number of post it’s too see how some members (and close friends) have grown a lot when it comes to knowledge and skillfulness with Excel. It’s a real pleasure for me and I feel lucky that I’ve been around here to take part of that progress.


    Yesterday the first shipment of two arrived to me. The package contained 8 books and the next shipment will include additional 6 books. One thing that still amazes me is the fact that the books usually cover more then 1000 pages. It means that I will spend the next coming months with reading and trying to learn new things. Actually, I have scheduled to spend the whole 2006 with reading, thinking, trying and exploring.


    What will I actually do? Well, I will try to catch up with the rapid development when it comes to the .NET platform. I think that the .NET rocks and with the recent release of version 2005 it has become even better. I will also explore and learn more about VSTO, which a close friend to me recently started to, call VBA.NET. It’s important to accept that it will take some time to understand the .NET platform and all the possibilities it offer. Personally I find it quite exciting to start with something new. I’ve also decided to challenge and see if I’m capable of learning C#. This is the #1 developing language on the .NET platform and it will probably be the leading tool for many years ahead. Most of You who read this probably assume that .NET is all about MSFT. That’s not true and if You’re curious I suggest that You take part of Borland Delphi at the following URL: http://www.borland.com/us/products/delphi/index.html


    I take this opportunity to thank Dave and the team for a pleasant and interesting time and I wish You all the best with the forum.


    Finally, I thank You all for reading my post here in the corner. After all, it’s a privilege to see that other people read what I actually post. Without You it wouldn’t been the same!


    Keep Excelling but don’t forget to give Yourself breaks to take part of the real life as well.

  • Search data ranges and format accordingly.

    Re: Search data ranges and format accordingly.


    Quote


    Thanks XL-Dennis, that code looks more complicated than I need it to be though!


    Well, it's not always true that I address things to the original poster. In this case You seems to be fine with the q&d-solution.


    But other members who take part of it may be more interested and they are the target audience.

  • Search data ranges and format accordingly.

    Re: Search data ranges and format accordingly.


    Hi all (and especially CM Tom :) )


    I thought I would "pop in" and point out that in general it's better to iterate through arrays of values then read them directly from the worksheet as the following example shows:


    [vba]
    Option Explicit


    Sub Check_Hide()
    Dim p_wbBook As Workbook
    Dim p_wsSheet As Worksheet
    Dim p_vaDateToCheck As Variant
    Dim p_vaDateSerie As Variant
    Dim p_lnCounter As Long


    Set p_wbBook = ActiveWorkbook


    With p_wbBook
    Set p_wsSheet = .Worksheets(1)
    End With


    With p_wsSheet
    p_vaDateSerie = .Range("A1:A2").Value
    p_vaDateToCheck = .Range("V2:V" & .Cells(.Rows.Count, "V").End _
    (xlUp).Row).Value
    End With


    With Application
    .ScreenUpdating = False

    For p_lnCounter = 1 To UBound(p_vaDateToCheck)
    If p_vaDateToCheck(p_lnCounter, 1) < p_vaDateSerie(1, 1) Or _
    p_vaDateToCheck(p_lnCounter, 1) > p_vaDateSerie(2, 1) Then
    p_wsSheet.Cells(p_lnCounter + 1, 1).EntireRow.Hidden = True
    End If
    Next p_lnCounter

    .ScreenUpdating = True
    End With


    End Sub
    [/vba]

  • Creating a stand alone system using VBA

    Re: Creating a stand alone system using VBA


    Hi,


    With MS Office You can use VBA to develop solutions for instance for Excel. However, they require that the target software are installed.


    To develop standalone solutions, i e exe-file, require that You develop a solutions with for instance MS Visual Basic 6.0 or MS Visual Studio 2005 where VB.NET is part of the package.


    For about 10 years ago there actually exist a tool that converted Excel-files to standalone solutions. However, for several reasons it couldn't compete with Excel as it couldn't handle all stuff like Excel can do.

  • AddIn location

    Re: AddIn location


    Quote


    IMO it is a dangerous practice to rely on technical solutions to all problems. Doesn't always work, and often the cost is too high.


    Which I fully agree and as have been pointed out that information to the end-users is necessary :)

  • Insert records to Access from xl

    Re: Insert records to Access from xl


    marc,


    You must use Excel 8.0 and not 11.0. It's the "version" required by the ISAM Driver which force us to use 8.0. It's not related to which version of Excel that is in use.

  • Translating English to Spanish formulas

    Re: Translating English to Spanish formulas


    Hi :)


    Quote


    When I run it, pops up a message which says that it is not compatible and it doesn't recognizes as an excel file.


    # 1
    Do You also get the same message when You try to open the file on the CD in the Spanish version of Excel?


    If yes then it may be related to the file itself, i e corrupted.


    # 2
    In general You should be able to open Excel-files in different language versions of Excel.


    The only restriction is that You can't use functions from the Analysis Toolpak as they are language specific.

  • Add CommandButton to MenuBar

    Re: Add CommandButton to MenuBar


    Season Greetings to everyone :)


    Below You find a solution that create a new commandbar, add a button to it and hook up a procedure to the button. Please see the direct help for further information:


    [vba]
    Option Explicit


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("MyCommandbar").Delete
    On Error GoTo 0
    End Sub


    Private Sub Workbook_Open()


    On Error Resume Next
    Application.CommandBars("MyCommandbar").Delete
    On Error GoTo 0


    With Application.CommandBars.Add(Name:="MyCommandbar", Temporary:=True)
    With .Controls.Add(Type:=msoControlButton)
    .OnAction = "The Procedure_Name when clicking on the button"
    .FaceId = 343
    .TooltipText = "Text to show when hovering over the button"
    End With
    .Position = msoBarFloating
    .Visible = True
    End With


    End Sub
    [/vba]


    The above should be added to the workbook's ThisWorkbook-module.