KNX2 Binding - Create Things and Items out of one Excel file

Hi all,

at some point I confused myself with the content of things and items files.
So: I wanted only one place to edit - and press a button to create the two files for OH2.

I created the attached Excel-Worksheet that offers some macros.
The fie contains some example data to make the understanding easier and to play immediately with the macro.
The example data is just a brute force reduction of my installation …
There are Macros for the creation of the two files and a simple check, if GA are already used.

Core principle:

  • To define a switch etc. only one line needs to be created in the Excel
  • The Thing definition and connection of Items to the device in the Things is all taken care of by the macro
  • After opening the Excel, the menu will be extended by “Add-Ins” and the “KNX-Editor”
  • Do the editing in the Things+Items, Go into Menu: “Add-Ins” to run the macro for creation of the file(s).
  • The output can be written into the files. Te content is also available on the clipboard for a manual paste.

Structure of “Things+Items”

  • While processing the sheet, each line is used for either Thing or Item or both
  • Text, only to be put into the Thing file, comes in Col B, similar for the Item file in Col C
  • A typical header of a ‘Thing device … {’ is put in Col B and closes later with ‘}’
  • The type of each element is written in Col B or C
  • Thing is created from Col D + F + G plus the K
  • Item is created from Col E + F + G + H + I + J + the device created from last Thing plus the Name of item
  • The name of the device (needed for the item to connect) is expected in the “Thing device …” context

Some specials:

  • Place “File=” in any B or C Col to allow automatic storage of the content into the files when the macro is running
  • All results are also on the clipboard … if you don not want to allow any overwriting by the macro just paste it manually
  • A comment can be placed into Col B or C to be placed into Things or Items. If a comment shall be visible in both files, put a “…” in Col B and place the comment in C
  • Group definitions can be placed in Col C
  • Group reference and Icon definition for the item in H and I
  • To allow Homekit support, I created a dropdown list per line to be selected in J
  • To make the files look nice … the length of names etc are determined in advance, to align the text
  • To ignore a line, say yes in Col A
  • The channel parameter allows also incomplete text like “<11/7/0” and makes it complaint
  • “Check for Double GA” : The Col K is scanned for duplicate naming of address. Everything that is double, is marked red.
    This might be on purpose - but it helped my to find a unwanted redefinition

Download at: https://1drv.ms/x/s!AkXhwKpZ5CVoj6AC8FaUe4Dbwr7ZGg?e=iL3Yq1

You must store locally, start Excel and allow to run Macros!
You might need to enable the “Add-Ins Menu entry” -> Rightclick on the menubar -> Adjust… (Menüband anpassen) -> Enable “Add-Ins”

This software comes with no guarantee … protect your *.items and *.things before running this :wink: !!
This is a hack - but I helped me to get a clean items and things file

Enjoy !
Michael

How does it look like:

Excel:

Menu: Menu

Result in Editor:

1 Like

Can you please upload that EXCEL file again somewhere else ?
It’s not available anymore… :slight_smile:

Done. I hope you like it!

Am I only to blind to see? :wink: after I allowed the macro to do their things, I can’t find “Add-Ins” or “KNX_Editor” anywhere? Where Do I have to look? (using Excel 3654 -16)

Hi !
EXCEL seams to hide the “Add-Ins” Entry.
Quick fix: Rightclick into the Menubar -> Adjust Menubar (Menüband anpassen) and enable the “Add-Ins” entry.
I uploaded 1.2
Enjoy!

Thanks for version 1.2 - I still can’t see “Add-Ins”, despite having the checkbox checked. Is there some security issue in Excel you know I can check to enable it? unfortunately there’s no “Add-Ins” entry…

Hi Michael,

this is looking really great,
I’ve been looking for something like this for a long time

But it ist not working on Excel 64bit, do you also have a Version vor 64 bit Office?

Stefan

Hi!
Can you open xlsx and xlsm files? What office version are you using?

I am using the latest version of Office 365.
when opening the file I get the message " Compile error - The code in this project must be updated for use on 64bit systems."

Same issue here - you can fix it by replacing the line:

Declare Function WideCharToMultiByte …

with

Declare PtrSafe Function WideCharToMultiByte …

Hi!
I placed an *.xlsx version of the file plus the macrocode into a separate file. Just open the xslx and copy the macrocode in.
Hope this helps
Michael

Can you share the modifications needed? Maybe I can bring them into my file.
Thanks

I have uploaded it here - but to be honest: I have not used it in the end. So I dunno if it works or not. I just can confirm that the error is gone here