Posts by XL-Dennis

  • create several pivot tables from severable data tables automatically

    Re: create several pivot tables from severable data tables automatically


    difre,


    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.

  • select only rows that are numeric in certain cells

    Re: select only rows that are numeric in certain cells


    Here we go:


    [vba]
    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
    [/vba]

  • Conditional Vlookup

    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.

  • Interaction with IE

    Re: Interaction with IE


    Hi Barry :)


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


    [vba]
    Option Explicit


    Sub test()
    Dim objIE As SHDocVw.InternetExplorer


    Set objIE = CreateObject("InternetExplorer.Application")


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


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


    Set objIE = Nothing


    End Sub
    [/vba]

  • select only rows that are numeric in certain cells

    Re: select only rows that are numeric in ceratin cells


    Hi,


    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.

  • Can't save and restore window state

    Re: Can't save and restore window state


    Bill,


    See if the following will solve the issue:


    [vba]
    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
    [/vba]


    The above procedures are placed in the module "ThisWorkbook"

  • Initialize all chek boxes at once

    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.


    [vba]
    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
    [/vba]


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

  • Merging unlocked cells while having the sheet protected?

    Re: Merging unlocked cells while having the sheet protected?


    Hi,


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


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


    [vba]
    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
    [/vba]


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

  • copying cell content from table into userform

    Re: copying cell content from table into userform


    Hi,


    See if the following example will work for You:


    [vba]
    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
    .Show
    End With


    Set frmTres = Nothing


    End Sub
    [/vba]

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

    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.

  • Selection.QueryTable.refresh BackgroundQuery:=False ??

    Re: Selection.QueryTable.refresh BackgroundQuery:=False


    Hi,


    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:


    [vba]
    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
    [/vba]

  • picking data from closed workbooks

    Re: picking data from closed workbooks


    Hi,


    (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.


    [vba]
    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


    [/vba]

  • Web Search Toolbar

    Re: Web Search Toolbar


    Roy,


    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.

  • Diversity

    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.

  • Diversity

    [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]