Posts by XL-Dennis

    Re: create several pivot tables from severable data tables automatically


    A public forum is frequent by people around the world on a freely basis. Most time questions are answered within a hour or so but also as this thread shows later.

    If You want to have a fast answer then You are always free to hire a consultant that may be available in short notice.

    Please follow up Excelpower's answer and let us know the outcome of it.

    Re: select only rows that are numeric in certain cells

    Here we go:

    Option Explicit

    Sub Copy_Cells_Numeric_Values()
    Dim p_wbBook As Workbook
    Dim p_wsSheet As Worksheet
    Dim p_rnValue As Range
    Dim p_rnTarget As Range

    Set p_wbBook = ActiveWorkbook
    Set p_wsSheet = p_wbBook.Worksheets(1)

    With p_wsSheet
    Set p_rnTarget = .Range("C1")
    Set p_rnValue = Application.Union( _
    .Range("A1:A100").SpecialCells(xlCellTypeConstants, xlNumbers), _
    .Range("A1:A100").SpecialCells(xlCellTypeFormulas, xlNumbers))
    End With

    With Application
    .ScreenUpdating = False
    p_rnValue.Copy p_rnTarget
    .ScreenUpdating = True
    End With

    End Sub

    Re: Conditional Vlookup

    Hi Rich & Domenic :)

    Thanks for the heads up on it and Domenic is right about that we use the ";" as the separator in the Swedish versions.

    Re: Interaction with IE

    Hi Barry :)

    Something along this line should propably do what Your looking for:

    Option Explicit

    Sub test()
    Dim objIE As SHDocVw.InternetExplorer

    Set objIE = CreateObject("InternetExplorer.Application")

    Do While objIE.Busy And Not objIE.ReadyState = 4

    With objIE
    .Visible = True
    .Navigate "http:\\"
    'Here I let objIE be available for 1 minute.
    Application.Wait Now + TimeSerial(0, 1, 0)
    End With

    Set objIE = Nothing

    End Sub

    Re: select only rows that are numeric in ceratin cells


    To do it manually:
    1. Select the area in the A-column
    2. Choose the command Edit | Go To...
    3. In the dialog click on the button Special
    4. Choose Constants and as well as Numbers under the Formulas options (uncheck the
    unwanted options under Formulas).
    5. Click on the OK-button
    6. Copy the highlighted cells.

    This can also be achieved via VBA but You need to explicit mention it before moving on.

    Re: Can't save and restore window state


    See if the following will solve the issue:

    Option Explicit

    Dim AWS As Long

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.WindowState = AWS
    End Sub

    Private Sub Workbook_Open()

    With Application
    AWS = .WindowState
    .WindowState = xlNormal
    End With

    End Sub

    The above procedures are placed in the module "ThisWorkbook"

    Re: Initialize all chek boxes at once

    Hi João Nuno Cruz :)

    The only reasonable approach would be iterate the collection of checkboxes on the worksheet.

    Option Explicit

    Private Sub Workbook_Open()
    Dim p_wsSheet As Worksheet
    Dim p_Chbox As CheckBox
    Dim p_OLEChbox As OLEObject

    Set p_wsSheet = Me.Worksheets(1)

    'If You use the Forms Checkbox on the worksheet
    For Each p_Chbox In p_wsSheet.CheckBoxes
    If p_Chbox.Value = xlOn Then p_Chbox.Value = xlOff
    Next p_Chbox

    'If You use the Control Toolbox's Checkbox on the worksheet
    For Each p_OLEChbox In p_wsSheet.OLEObjects
    If TypeOf p_OLEChbox.Object Is MSForms.CheckBox Then
    p_OLEChbox.Object.Value = False
    End If
    Next p_OLEChbox

    End Sub

    The example use the Open_Workbook event and should therefore be placed in the module "ThisWorkbook"

    Re: Merging unlocked cells while having the sheet protected?


    Why do You want to merge cell in a protected worksheet?

    The only workaround would be with code as the following example shows:

    Sub Merge_Cells()
    Dim p_wbBook As Workbook
    Dim p_wsSheet As Worksheet
    Dim p_rnMerge As Range

    Set p_wbBook = ActiveWorkbook
    Set p_wsSheet = p_wbBook.Worksheets(1)

    With p_wsSheet
    Set p_rnMerge = .Range("A10:B10")
    End With

    With p_wsSheet
    .Unprotect Password:="Secret"
    p_rnMerge.MergeCells = True
    .Protect Password:="Secret"
    End With

    End Sub

    The code can be added to a worksheet event if wanted.

    Re: copying cell content from table into userform


    See if the following example will work for You:

    Option Explicit

    Private Sub cboSetThresholds_Click()
    Dim frmTres As New frmTreshold
    Dim stText As String

    stText = Me.Range("A1").Value

    With frmTres
    .TextBox1.Text = stText
    End With

    Set frmTres = Nothing

    End Sub

    Creating a Microsoft Database (MDB) and add tables, fields to it on the fly.

    A common misunderstanding is that the file format MDB only can be handled with MS Access and therefore it’s referred to as “Access-database”.

    However, per se MDB is associated with the Microsoft Jet Database Engine. The Jet Database Engine itself is shipped with Windows and other Microsoft softwares like Office et al. MS Access is only one of several softwares that can manipulate the Database Engine.

    What MS Access provide us with is a user friendly UI and tools to work with MDBs but as we know Excel and other softwares can easily create UI for working with the data stored in the MDBs.

    In this post I’ll show how we can create a MDB on the fly, populate it with tables and fields as well as manipulating some properties of the tables and fields.

    Since OzGrid is first of all an Excel-forum the example is based on that we have added the below information about the tables and the fields we want to append in a worksheet:

    • Table names in the C-column
    • Field names in the D-column
    • Datatype in the E-column
    • Format (if any) in the F-column – Not used in the example
    • Number of digits for the datatype Decimal in the G-column

    What we need:
    * Microsoft Windows 2000 and later
    * Microsoft Excel 2000 and later
    * MDAC 2.5 and later

    Step 1
    Add references to the following external libraries via the command Tools | References… in the VB-editor:
    * Microsoft Ext. ADO 2.5 for DDL Security and later.
    * Microsoft ActiveX Data Object 2.5 Library and later.

    Step 2
    Add a standard module to the workbook and then add the following procedure:

    If we need we can also append foreign keys and also create relations between the tables. The above example shows how easily and fast we can create temporarily as well as permanent MDBs for different purposes.

    Re: Selection.QueryTable.refresh BackgroundQuery:=False


    A guess is that You either have not the correct connection to the database or the SQL-query is wrong or it take a very long time before You can connect to the database.

    Please check Your configuration and if possible also expand the ODBC-connection timeout.

    See if the following will help You:

    Option Explicit

    Sub Update_QTs()
    Dim wsG7C As Worksheet
    Dim wsATC As Worksheet

    With ThisWorkbook
    Set wsG7C = .Worksheets("G7C")
    Set wsATC = .Worksheets("ATC")
    End With

    'Here it's assumed that only one QueryTable exist in each worksheet.

    With wsG7C.QueryTables(1)
    .refresh BackgroundQuery:=False
    End With

    With wsATC.QueryTables(1)
    .refresh BackgroundQuery:=False
    End With

    End Sub

    Re: picking data from closed workbooks


    (Hi Barry, Roy and Dave :) )

    Based on the little information given I only show an example which gives a hint on how it can be achieved.

    Since ExecuteExcel4Macro works with Excel's built in function I believe it can evaluate the conditions but it's subject to furhter post from You.

    Option Explicit

    Sub Retrieve_Value_From_Closed_Workbook()
    Dim wbBook As Workbook
    Dim wsTarget As Worksheet
    Dim wsSource As Worksheet
    Dim stFilename As String
    Dim i As Long, j As Long
    Dim vaObjects As Variant

    Set wbBook = ThisWorkbook
    Set wsTarget = wbBook.Worksheets(1)

    With wsTarget
    vaObjects = .Range(.Range("A2"), .Range("A65536").End(xlUp)).Value
    End With

    For j = 1 To 3
    stFilename = "'c:\Sources\[S" & j & ".xls]Sheet1'!R1C1:R6C2,2,0"
    For i = 1 To UBound(vaObjects)
    With wsTarget
    .Cells(1 + i, 1).Offset(0, j).Value = _
    Application.ExecuteExcel4Macro("VLOOKUP(""" & _
    vaObjects(i, 1) & """," & stFilename & ")")
    End With
    Next i
    Next j

    End Sub


    Re: Web Search Toolbar


    First of all, it's a pretty cool tool and one advantage is that we can customize it too :)

    Second, I feel honoured to be among the standard links - thank You.

    Re: Diversity

    You're welcome and again it's all about diversity :)

    As for my english I believe it has improved by 1001 % as I, thanks to Jim, only read english SF-books and nowadays I also think in english when I write post ;)

    But again, it's all about diversity which is the fuel to bring us together and share knowledge.

    [J]# 1 The Keyword - Diversity

    If this post should be described in one word, it would be diversity. First of all, I have always liked and supported diversity, no matter what the circumstances or the subject. However, it is easy to say something like this but more difficult and complex to actually live according to diversity. It challenge us on a continual basis, both in our private lives as well in our professions.

    In the Excel-world there also exists and should exist diversity. Issues have rarely one solution and many commands can be executed in several ways. A trend that indeed supports diversity is that new sites and new bloggs are set up. These bring new aspects and new knowledge and give a wider perspective of Excel and the areas it can be applied within.

    Two highly respected members of the OzGrid community, WillR and jhenderson, have recently set up sites, respectively bloggs. WillR’s site reflects his wider interest of Excel & databases while Jim’s blogg sets focus on mechanical estimating with Excel. They invite other people to share and take part of interesting things within their areas of interest:

    Will’s site
    Jim’s blogg

    I would also like to take this opportunity to thank Will and Jim as by setting up the sites they do indeed support diversity and do it for free.

    # 2 Thanks Armstrong!

    In the mid 40’s there was the musician called Louis Armstrong and in the late 60’s there was Neil Armstrong, who took the first steps on the moon, and finally there was Lance Armstrong.

    During 1999 – 2005 Lance won the Tour de France which is a new record of 1st places. He is also a role model for modern athletes in many ways. After this year’s Tour he decided to end his career and move along in life.

    However, in the early 90’s he managed to survive cancer and when he later becomes a megastar of the sport, he used the position to create the Lance Armstrong foundation. For more information please see: Livestrong

    Personally I rank his efforts and strong commitment to cancer-research and how to live with and fight against cancer much higher then all his amazing performances in racing. Thanks Lance Armstrong!

    # 3 Total Extreme Makeover

    I decided to become more ”modern” and do some extreme makeover. My initial problem was to find a good case for it. Since my body, face, teeth, hair are always in perfect shape as well as my house it was not an easy task but finally it become obvious to me.

    It was time to do a total extreme makeover of my computers. I decided to keep two computers, one stationary and one laptop. The laptop is used for presentation of solutions and the stages of development towards clients while the stationary is the core developer-machine.

    The stationary was equipped with 2 x 250 GB IDE discs and 4 GB RAM enabling me to run VMware in an acceptable way. I decided to use the Debian distribution as the host system and run all the Windows operating systems as guest systems (virtual systems).

    At present the computer runs 8 guest systems with different kinds of configurations and versions of Windows and Office etc. All backups, as well as guest systems that are not used, are stored by the server where also a SQL Server 2000 and a MySQL reside. The laptop now has 1 x 160 GB IDE disc and 2 GB RAM and runs WMware as well (Yes, I use two licenses) with Mandrake Linux as the host operating system.

    To my big surprise no TV-company was interested to show a program about my computers extreme makeover but my wife is very pleased with the extra space…

    # 4 Information Bridge Framework (IBF)

    My latest interest and concern is about the IBF. It’s too early for me to discuss the pros and cons of it but in short it offers an approach to populate Excel and other programs in the Office System 2003 and later with dynamic data from Web Services via the Taskpane through dynamic links.

    As with many new technologies it puts a high demand on the planning stage and how to create workable structures.

    MSDN startpage

    Two articles that give a basic idea about it can be found at the following URLs:

    Introducing the Microsoft Office Information Bridge Framework

    Technical Overview of Information Bridge Framework

    Some other online resources of general interest:

    Office Zealot

    The Code Project:

    IBF – Part I
    IBF – Part II

    The development of IBF also reflects the emergency trend of diversity and how to communicate dynamic information.

    If we only want to consume Web Services in Excel 2002 and later then the following URL gives a basic understanding how to achieve it:

    Extract from Dave Hawley’s book “Excel Hacks” written by Simon St.Laurent:

    Consuming Web Services
    (For those of You who already are happy owner of a copy of the book it’s part of the chapter 8.)

    Blogg post by Eli Robillard, which is based and extended on the above extract:

    Consuming Web Services

    Another excellent source is Stephen Bullen’s et al book, “Professional Excel Development”, where Web Services is covered with XML in chapter 23.

    # 5 Book reviews

    Two books that also reflect diversity have recently been reviewed by me and they are:

    Bill Jelen (aka Mr Excel) & Michael Alexander:
    Pivottable Data Crunching

    Conrad Carlberg:
    Managing Data with Microsoft Excel

    I like both these books for one major reason; they explicitly set focus on specific areas and therefore provide a deep that more general view that written books can’t achieve.

    # 6 The End

    Diversity gives us a wider perspective of things and reflects in high degree that there exists no single “truth”. The paradox is that we all tend to agree on diversity but the ideals in our “modern” world and the social, as well as the professional acceptable rules, tend to be on the contrary. This is something we all need to take responsibility for and act to prevent. Otherwise we end up in a situation where diversity is just a non-substantial phrase and we become less friendly to each other.

    Finally, the life is too short and the “life of the computers” is not the real life so turn off the computer(s) and take part of the life![/J]