vrijdag 24 april 2015

Excel: Bootvluchtelingen en groei bevolking van Afrika

Volop in het nieuws, de bootvluchtelingen. Veel gruwelijke verhalen. Veel van deze mensen komen uit landen met een uiterst onderdrukkend regime. Eritrea bijvoorbeeld. Een andere belangrijke factor waar we veel minder over horen is de toenemende bevolkingsdruk in Afrika. Nu weet ik ook wel dat niet alle bootvluchtelingen uit Afrika komen. Hier wil ik evenwel de ontwikkeling van de bevolking van dit continent cijfermatig toelichten.

In onderstaande grafiek laat ik de ontwikkeling van de inwonersaantallen zien van 2003 tot en met 2023.


BRONhttp://data.worldbank.org

In het bijbehorende bestand kun je zelf simpel de jaartallen aanpassen en bekijken hoe dat het beeld verandert.

In heel veel landen in Afrika laat de bevolking zeer hoge groeicijfers zien. In Eritrea bijvoorbeeld ligt dat op 3,3 procent per jaar. Dat betekent dat de bevolking daar zich binnen twee├źntwintig jaar zal verdubbelen. In Zuid Soedan gebeurt dat zelfs in zo'n zestien jaar. Beide politiek zeer onstabiele landen. Men kan zich dus voorstellen dat de behoefte van mensen te vertrekken groot is.

Het bestand met de cijfers ExcelInwonersAfrika.xlsx kun je downloaden via:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

dinsdag 21 april 2015

Report Builder / Reporting Services: Reports Without Data? Very Useful!

The report I created here contains no data from an external source. The numbers we see, all come from calculations based on the figures given via the input parameters.


The expressions look like this:

Max Deviation:

=Code.BerekeningNorm(Parameters!parNormAfwijking.Value,Parameters!parProfielLengteNorm.Value)

(based on the upper two paramaters)

Requirement:

=Code.Berekening(Parameters!parMaxAfwijkingNorm.Value, Parameters!parProfielLengteNorm.Value) 

(based on lower parameter and the right upper one)

The function code behind the report:

Function Berekening(MaxAfwijkingNorm as Integer,ProfielLengteNorm as Integer) as Double
Berekening = MaxAfwijkingNorm ^2
Berekening = Berekening + (ProfielLengteNorm / 2)^2
Berekening = Berekening / (2 * MaxAfwijkingNorm)
Berekening = Berekening - ((Berekening^2) -  250000)^(1/2)
End Function

Function BerekeningNorm(NormAfwijking as Double,ProfielLengteNorm as Integer) as Double
BerekeningNorm=NormAfwijking^2
BerekeningNorm = BerekeningNorm + 250000
BerekeningNorm = BerekeningNorm /(NormAfwijking*2)
BerekeningNorm = BerekeningNorm - (BerekeningNorm ^ 2 - (ProfielLengteNorm/2)^2 )^(1/2)
End Function

woensdag 15 april 2015

Excel: Creating Your Own Tabs With XML And VBA

In order to create you own nice looking tabs you need the Custom UI Editor for Microsoft Office. You can download it for free.

How is this different from choosing File, Options, Customize Ribbon and adding a new tab with new groups and then commands to the group? You can also move the new tab to any position. Then your new tab is always there. Even when you don't open the specific file with the right VBA.

In my example the outcome looks like this, a nice tab, placed in front of the Home Tab. You will only get this tab when you start the specific example (which you can download).


The XML behind this:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <ribbon startFromScratch="false">
    <tabs>
      <tab id="MaxIlze" label="Dossier Overzicht" insertBeforeMso="TabHome">
        <group id="customGroup1" label="Adressen verrijken">
          <button id="customButton1" label="Adressen toevoegen" size="large" 
onAction="toevoegenpchnr" imageMso="QueryAppend" />
         <button id="customButton2" label="Gegevens toevoegen" size="large" 
onAction="vullen" imageMso="DatabaseAccessBackEnd"/>
         <button id="customButton3" label="Adres zoeken" size="large" 
onAction="zoekenpostcode" imageMso="FindDialog"/>
         <button id="customButton4" label="Dossier zoeken" size="large" 
onAction="zoekendossier" imageMso="FindDialog"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Comments

  • insertBeforeMso="TabHome" will place the tab before the Home Tab
  • onAction="toevoegenpchnr" refers to the VBA behind the file
  • imageMso="QueryAppend" refers to the Microsoft buttons; you could use image to refer to you own buttons
  • label="Adressen toevoegen" refers to the label underneath the buttons
The VBA behind the file:


Sub toevoegenpchnr(control As IRibbonControl)
    MsgBox "toevoegen"
End Sub

Sub vullen(control As IRibbonControl)
    MsgBox "vullen"
End Sub

Sub zoekenpostcode(control As IRibbonControl)
    MsgBox "zoeken postcode"
End Sub

Sub zoekendossier(control As IRibbonControl)
    MsgBox "zoeken dossier"
End Sub

Comment
  • control As IRibbonControl, this needs to be added to the VBA procedure

dinsdag 14 april 2015

Excel: Microsoft Query and Slicers (2013)

For some reason I was under the impression that Microsoft Query was outdated and should me replaced by PowerPivot. While working on some VBA scripts to create a ListObject with a QueryTable (what you need to use a Microsoft Query), I all of a sudden discovered a new feature for Microsoft Query: Slicers! Or better said, the button Insert Slicer.


And of course, the slicer you create is automatically connected to your dataset:


Got a remark from Craig Hatmaker, in version 2013, slicers work with all tables. So, Microsft Query tables, Pivot Tables and regular lists,changed into a table. And of course, connected charts are adjusted with your choice.

By the way, the VBA scripts I was working on:

Sub ListObjectAdd()
    'create listobject connected to database
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=ndw;", Destination:=Range("$A$1")).QueryTable
        .CommandText = "SELECT * FROM Customers"
        .ListObject.DisplayName = "Tabel_Query_van_ndw"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub ListObjectSQL()
    'change SQL commandtext
    Dim strConnection As String
    Dim strSQL As String
    
    strSQL = "SELECT country, ContactName, CompanyName FROM Customers"
    strConnection = "ODBC;DSN=ndw;"

    'ActiveSheet.ListObjects("Tabel_Query_van_ndw").QueryTable.Connection = strConnection
    ActiveSheet.ListObjects("Tabel_Query_van_ndw").QueryTable.CommandText = strSQL
    ActiveSheet.ListObjects("Tabel_Query_van_ndw").QueryTable.Refresh
End Sub

Sub ListObjectDelete()
    'delete listobject
    ActiveSheet.ListObjects("Tabel_Query_van_ndw").Delete
End Sub

Sub SlicerAdd()
    'add slicer
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Tabel_Query_van_ndw") _
        , "country").Slicers.Add ActiveSheet, , "country", "country", 189, 639.75, 144 _
        , 198.75
End Sub

woensdag 8 april 2015

Excel: Are PDF Converters Useful?

Ik was asked to look into a PDF converter for Excel. Reward: a free license. By the way, it can also be used to convert to other file types:


This one - a commercial product, on the market for over 10 years - is called Able2Extract. More information can be found via:

http://www.investintech.com/prod_a2e.htm

I use their own words about the product: The Advanced PDF to Excel conversion feature allows users to:
  • Correctly recognizes rows and columns inside every table locked in PDF format.
  • Users can choose to split or merge rows or columns pre-conversion based on their own criteria which can significantly save them time.
  • It is possible to convert only selected part of the table.
So i ran a simple test. I converted an Excel temperature tabel to PDF:

I then used the converter to get the data back to Excel. I simply selected all data and pressed Excel => convert:


As you can see, something went wrong with the first column, but that could be easily adjusted. By the way, simply copy/paste did put all the data in just one A column. So, it would have saved me time. The point is, I pretty rarely convert PDF data to Excel. On the other hand, the next time I will look for a converter!

Word: Templates With The Perfect Circle

In the example template I am showing here, a dialogue window will automatically start on opening:


As soon as you enter some stuff in the dialogue window and you click OK the text fields in the memo template will be filled in:


At the same time, a new Tab + Button will appear at the left hand side: Adjust Memo Fields. As you click this button, the dialogue window will appear again, filled in this time:


When you are in the right mood, you can go on forever.

If you are interested, you can download the template with the legacy fields memo.dotm here:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

or the new one with the content controls: memonieuw.dotm

woensdag 1 april 2015

Excel: simpel VBA script om PDF's te printen

De data:

Factuur Afdrukken
2015WM0101.pdf nee
2015WM0102.pdf ja
2015WM0103.pdf nee
2015WM0104.pdf ja

Het VBA script:

Sub printPDFfiles()
    Dim strProg As String
    Dim intRows As Integer
    Dim rngCel As Range
    Dim strFile As String
    Dim strFolderFrom As String
    
    Application.ScreenUpdating = False

    'Bepaal het pad waar de PDF's staan
    strFolderFrom = "D:\data\Essentra\pdf\"
    
   'bepaal het aantal gebruikte rijen
    intRows = ActiveSheet.UsedRange.Rows.Count
    
    'Controleer je ADOBE READER versie en het pad erheen, bijvoorbeeld:
    'strProg = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe"
    strProg = "C:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe"
    
    
    'ActiveSheet.UsedRange.Columns(1).Range(Cells(2, 1), Cells(intRows, 1)).Cells
    'alle cellen uit het gebruikte bereik
    
    For Each rngCel In ActiveSheet.UsedRange.Columns(1).Range(Cells(2, 1), Cells(intRows, 1)).Cells
        strFile = rngCel.Value    'haal naam uit cel
    
        If rngCel.Offset(0, 1) = "ja" Then
            Shell (strProg & " /s /n /h /t " & strFolderFrom & strFile)
            '/s=don't show splash screen
            '/n=new instance
            '/h=minimised window
            '/t=print to default printer; or use /t <filename> <printername> <drivername> <portname>
        End If
    Next

    Application.ScreenUpdating = True
End Sub