dinsdag 31 mei 2016

Excel: VBA en het zoeken en plaatsen van plaatjes bij producten

Laatste tijd een aantal keren mensen gezien die in Excel werken met een productenlijst met plaatjes. Die plaatjes plakken ze er dan zelf bij. Wat een werk! Met VBA is zoiets simpel te regelen. Plaatjes worden automatisch bij het artikelnummer gezocht en eventueel vernieuwd. Uiteraard moet er wel een map met plaatjes zijn en moeten de namen van de plaatjes gerelateerd zijn aan het artikelnummer.

In het onderstaande voorbeeld heb ik dit uitgewerkt.


Het Excel voorbeeld bestaat uit twee bladen. De eerste heet Plaatjes en bevat de artikelnummers en een knop. Bij een druk op deze knop zoekt het VBA script het juiste plaatje erbij. Wordt dit niet gevonden dan plaatst het een blauwe cirkel met een kruis. De regelhoogte en de grootte van het plaatje worden automatisch ingesteld aan de hand van variabelen uit het tweede blad.

 Het tweede blad heet Constanten. Hierin worden een aantal instellingen gedefinieerd:

Naam Inhoud
Pad D:\walmar domino\images\
Extensie .jpg
Afstand links 100
Breedte 70
Hoogte 70

Het VBA script ziet er als volgt uit:

Sub plaatjestoevoegen()
    'definiĆ«ren variabelen
    Dim shpPlaatje As Shape
    Dim intLinks As Integer, intTop As Integer, intBreedte As Integer, intHoogte As Integer
    Dim intRows As Integer
    Dim rngCel As Range
    Dim strPad As String, strExtensie As String
    
    'vullen variabelen
    'kunnen ook uit een constantenblad komen
    strPad = Worksheets("constanten").Range("b2")
    strExtensie = Worksheets("constanten").Range("b3")
    intLinks = Worksheets("constanten").Range("b4")
    intBreedte = Worksheets("constanten").Range("b5")
    intHoogte = Worksheets("constanten").Range("b6")
    
    intTop = Rows("1:1").RowHeight
    intRows = ActiveSheet.UsedRange.Rows.Count
    
    'juiste blad activeren
    Worksheets("plaatjes").Activate
    
    'rijhoogte instellen voor het gebied met plaatjes
    ActiveSheet.Rows("2:" & intRows).RowHeight = 15
    ActiveSheet.Rows("2:" & intRows).RowHeight = intHoogte
    
    'wissen aanwezige plaatjes, behalve button; vervangen lukt niet met VBA
    For Each shpPlaatje In ActiveSheet.Shapes
        If shpPlaatje.Name <> "Button 3" Then
            shpPlaatje.Delete
        End If
    Next
    
    'plaatsen plaatjes
    For Each rngCel In ActiveSheet.UsedRange.Columns(1).Cells
        If rngCel.Address <> "$A$1" Then
            'testen of plaatje bestaat
            If Dir(strPad & rngCel.Value & strExtensie) <> "" Then
                ActiveSheet.Shapes.AddPicture strPad & rngCel.Value & strExtensie, _
                True, True, intLinks, intTop, intBreedte, intHoogte
            Else
                ActiveSheet.Shapes.AddShape(msoShapeFlowchartSummingJunction, intLinks, _                       intTop, intBreedte, intHoogte).Select
            End If
            intTop = intTop + intHoogte
        End If
    Next

    'A1 selecteren
    ActiveSheet.Cells(1, 1).Select
End Sub



Voor verder Excel tips klik hier.

donderdag 19 mei 2016

Report Builder: Trouble With Multivalue Parameters With Too Many Values

The other day I ran in to a problem with Report Builder. I had a number of multi value parameters with a lot of values. And there was a single value parameter for selecting items too. The report rendered fine in design mode. But as soon as I published it, it was no longer rendering properly.

The mutli value parameters also contained values which would not give any result at all given a certain item number. But then again, in design node no problem.

The solution I finally came up with was to interrelate the multi value parameters to the single value parameter by using a SQL  subquery.


Let's look at this example I created based on the NorthWind database. My main dataset:

SELECT        Customers.CustomerID, Customers.CompanyName, [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, 
                         Orders.OrderDate, Products.ProductName
FROM            Orders INNER JOIN
                         [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                         Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
                         Products ON [Order Details].ProductID = Products.ProductID

Two more datastes for the multi value parameters:

Parameter: parProduct
select ProductID, ProductName from Products
where ProductID in
(
select ProductID  from vwOrdersCustomers
where OrderId = @parOrder)

Parameter:  parCustomer
select CustomerID, CompanyName from Customers
where CustomerID in
(
select CustomerID from vwOrdersCustomers
where OrderId = @parOrder)

And a single value parameter: parOrder.

woensdag 18 mei 2016

Excel: validatie op basis van een benoemde kolom binnen een Excel Tabel

Er zijn in Excel een aantal manieren op een cel te valideren met een lijst. Ik loop ze hier langs.

A. Met een gewoon bereik:


Probleem: als er waarden worden toegevoegd, breidt het gekozen bereik zich niet automatisch uit.

B. Met een formule

Op de plek van Bron zetten we bijvoorbeeld:

=VERSCHUIVING(lijsten!$C$2;0;0;AANTALARG(lijsten!$C:$C)-1;1)

Dit heeft als voordeel dat het gekozen bereik zich automatisch uitbreidt. Komt er een element bij, dan wordt deze ook in de lijst getoond. Nadeel is dat de formule enigszins bewerkelijk is.

C. Met een naam

Op de plek van Bron zetten we dan bijvoorbeeld:

=Lijst_met_functie_Index

Een dergelijke naam verwijst dan naar een formule. In dit geval hebben we een aantal mogelijkheden:

=VERSCHUIVING(lijsten!$C$2;0;0;AANTALARG(lijsten!$C:$C)-1;1)

of bijvoorbeeld:

=INDEX(lijsten!$C:$C;2;1):INDEX(lijsten!$C:$C;AANTALARG(lijsten!$C:$C);1)

Namen met zulke formules hebben als voordeel dat het gekozen bereik zich automatisch uitbreidt. Komt er een element bij, dan wordt deze ook in de lijst getoond. Nadeel is dat de formules enigszins bewerkelijk zijn.

D: Met een naam binnen een tabel
We maken dan van een reeks eerst een tabel:


De naam van de tabel passen we vervolgens aan: 


Binnen deze tabel selecteren we de kolom A2:A7 en geven die een naam:


Deze naam ziet er dan enigszins eigenaardig uit:

=LijstMetEenTabel[[#Alles];[Lijst met een tabel]]

Het laatste stuk verwijst naar de kolomnaam. Als we dan bij gegevensvalidatie de bron aanpassen in:

=Lijst_met_een_tabel

Dan  verwijst deze naar een dynamisch bereik. Komen er waarden bij, dan breidt het bereik zich uit.

Dergelijke namen gebaseerd op een kolom binnen een tabel, kunnen we ook binnen een grafiek gebruiken. Deze beweegt dan ook mee met de tabel.

E. Met de functie INDIRECT verwijzen naar een kolom binnen een tabel

Bij Bron vullen we dan in:

=INDIRECT("LijstMetEenTabel[Lijst met een tabel]")


Voor verder Excel tips klik hier.