An easy way to put sharp Icons on your webpage is by using Google Icons.
Using Google Icons
Powerquery Tabbladen combineren
Soms worden gegevens aangeleverd op meerdere tabbladen, en deze wil je tot een enkele tabel combineren.
Je begint met het maken van een nieuwe Query als volgt:
Dan kies je het bronbestand:
Klik op Openen. In de volgende dialoog kan je kiezen welke tabbladen je wil importeren. Maar dat wil je nog niet! Als je het wel doet, dan maakt Powerquery voor elk tabblad een nieuwe Query waardoor je moeilijk kan combineren. Nee, je kiest hier de map en voor bewerken!
Nu krijg je keurig alle tabbladen als regels te zien:
Als je een Table klikt, dan zie je dat de hele tabel in deze cel zit gepropt:
We gaan de mutaties niet vergelijken, en het verborgen werkblad FilterDatabase willen we ook niet behandelen. Vandaar dat we de tabbladen uitsluiten van verdere verwerking door hier een filter toe te voegen waarbij we de ongewenste werkbladen de-selecteren:
Nu hebben we alleen de gewenste werkbladen. We gaan deze combineren door de knop met de 2 pijltjes in de Data kolom:
Hierna moeten we kiezen welke kolommen we willen meenemen. Ook staat standaard aan dat je de oorspronkelijke kolomnaam als voorvoegsel gebruikt. Dat wil je niet. Alle kolommen zouden dan Data.Postcode, Data.Huisnummer enzovoorts gaan heten. Dit vinkje dus weghalen!
Nu heb je een enkele tabel met alle rijen en een paar extra kolommen (4). De eerste kolom bevat de bron (uit welk bestand stammen de gegevens). De kolom Item staat achter de ingelezen kolommen en bevat hier de naam van het tabblad waar de gegevens vandaan komen.
Nu moeten de kolomnamen nog worden aangepast, want een kolom met een nummer is weinig duidelijk:
Gelukkig is er een mooie functie voor die de eerste rij omzet naar kolomnamen:
We moeten wel de naam van de eerste kolom herstellen, en het is vervelend dat de overige titelregels tussen de gegevens zijn blijven staan:
De overbodige titelregels kunnen we eenvoudig uitfilteren in bijvoorbeeld de perceelnummer kolom:
Daarna de kolommen een zinvolle naam gegeven, en de tabblad kolom naast de bronkolom geplaatst.
Nu moeten de kolomtypes nog worden aangepast. Dat kan je zelf handmatig doen, maar het is sneller om alles te selecteren (Ctrl-A) en vervolgens de knop Gegevenstype detecteren te klikken. Je ziet nu dat Powerquery vaak het juiste type kan bepalen:
Webhook site
Using Webhooks, you can connect services with eachother. For instance: You can install the Webhooks Add-on to Gravity Forms on your WordPress website. You can then define 0 to many Webhooks. Each Webhook delivers the form data in the required form (JSON/ Form fields) to any desired URL.
To test the data that is generated, there is this great Webhook Site service. On visiting, you get an URL target for your Webhook. This target URL is then used in the Webhook with the Gravity Form. Upon submitting the form, you can see the data generated on the Webhook.Site webpage.
Mailstore Home saves your e-mail messages
I always warn people for their dependency on provider e-mail services. The risk is when you change provider, you lose all your valuable e-mails in a jiffy. Enter Mailstore Home: This software (free for personal use) allows you to connect to your various mail servers, and archive the messages in a local store (in fact (sadly) it refused to create a store on my NAS). From that moment on, your e-mail messages are yours again! You can search them, view them anytime. No matter if your mail server crashes, is unavailable, or even if you have no internet connection: You have your e-mail.
Installing Visual Foxpro on Windows 10 64bit
After replacing my computer, I needed to reinstall Visual Foxpro. I had the install disc contents, but whatever executable I started with: it kept on crashing. A Google search brought silly advice, but fortunately there was a gem: an article on wOOdy’s Blog. This explained what the right procedure is and what file to start with (VFPSTART.HTA). It stil would not work. Not from a folder, and not from a mapped network disc. I found the free AnyBurn software that gave me the opportunity to create an ISO file from my install folder. Now it was as easy as double clicking the ISO file to have Windows mount the CD image and assign it a drive letter. Now everything worked like a charm. Then we have to apply both service packs, and perhaps some useful suggestions wOOdy made.
Foxpro ReturnObject
Function to prevent problems during object creation to crash your program. When problems arise, your program might be unable to continue, but at least you can show the user what went wrong instead of an uninformative crash warning.
* ReturnObject
* 2011.05.31 start
* 2012.07.06 parameters added
* Tries to create an object and return this. If object creation failed, a false is returned
* Parameters: Class to instantiate object from, max 2 parameters
FUNCTION ReturnObject( tcClass,tuPar1,tuPar2)
LOCAL loReturn,lnCount,loError
lnCount = PCOUNT()
TRY
DO CASE
CASE lnCount = 1
loReturn = CREATEOBJECT(tcClass,tuPar1)
CASE lnCount = 2
loReturn = CREATEOBJECT(tcClass,tuPar1,tuPar2)
OTHERWISE
loReturn = CREATEOBJECT(tcClass)
ENDCASE
CATCH TO loError
loReturn = .f.
ENDTRY
RETURN loReturn
ENDFUNC Foxpro and Excel
It is very possible to automate spreadsheets from Visual Foxpro using VBA/ COM automation. Here I wil document the parts I need.
Connecting to Excel
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
* could not instantiate Excel object
* show an error message here
return .F.
endifI use my own library (JLIB) containing a ReturnObject function that fetches errors. Using that function my code looks like this:
PUBLIC goX && global Excel object
IF InitiateExcel()
* Code here
ELSE
? [Error initiating Excel automation]
ENDIF
PROCEDURE InitiateExcel
goX = ReturnObject([excel.application])
RETURN (VARTYPE(goX)=[O])
ENDPROC Open an existing spreadsheet
oWorkbook = oExcel.Application.Workbooks.Open("C:\temp\test.xls")Create a new blank spreadsheet
oWorkbook = oExcel.Application.Workbooks.Add()Create a new spreadsheet from a template
oWorkbook = oExcel.Application.Workbooks.Add("C:\temp\template.xlt")Sheets collection/ objects
oWorkbook.sheets.count && returns the number of tabs/ sheets
oSheet = oWorkbook.sheets(1) && get an object to control the sheet
? oSheet.name && show the sheet nameTables
A table is called a ListObject in Excel/ Microsoft terminology
oSheet.ListObjects.count && gives the number of tables on the sheet
oTable = oSheet.ListObjects(1) && get an object to control the table
? oTable.ListRows.count && displays the number of data rows in the table
oTable.DataBodyRange.delete && deletes al rows. Error (crash) if there are no rows!Working with table rows
Adding a table row needs several steps: 1. Add the row and keep the reference to it. 2. Add the column data 1 by 1:
oRow = oTable.ListRows.Add && you have a new row and a reference to it
oRow.Range(1) = Date(2020,7,6) && Add a date in the first columnUsing named cells
? oSheet.Range("ProductionDate").value && show value of cell named ProductionDate
oSheet.Range("TotalPages").value = 13Saving and closing
The save method is in the workbook object. And you can quit from the Excel application object:
oSheet.SaveAs([x:\reports\report.xlsx]) && Save
oExcel.Quit && quit ExcelThe Quit only works on this Excel application you’ve initiated. So existing opened Excel sheets are untouched (remain opened).
Sources used
OpenXava rapid web development
This Java based development framework makes developing a (enterprise) web app as easy as defining your data model. Your main task is to create a good data model, pour it into classes, and defining the relationships and referential integrity. The framework creates the web app for you! When you are ready to deploy, the web app can be hosted in a Tomcat environment.
Gliffy diagram & flowcharting
I am using Gliffy a lot. It is a great graphic tool (free Chrome extension) to create flowcharts, UML diagrams, network diagrams and much more. Very important is the rubber-banding support: when you connect objects using their red indicated connections (during drawing), these connections are maintained wherever you drag the objects.
https://chrome.google.com/webstore/detail/gliffy-diagrams/bhmicilclplefnflapjmnngmkkkkpfad
Node.js: Application as a Windows service
node-windows
node-windows is a standalone module that makes it possible to offer a Node.js script as native Windows services..
Prerequisite: You have succesfully installed Node.js.
Install with npm using the global flag:
npm install -g node-windowsIn the project root run:
npm link node-windowsHello world example
Create a hello.js file with this code (Hello World sample from the Express website):
const express = require('express')
const app = express()
const port = 3000
app.get('/', (req, res) => res.send('Hello World!'))
app.listen(port, () => console.log(`Example app listening at http://localhost:${port}`))You can (should) test if it works by running it from the command line:
node hello.jsIf there are no errors, point your browser to http://localhost:3000. If it says Hello World! you are well on your way!
Script to create the service
Now we have our app, we want to make it a service. This is achieved by another script we call hello-windows-service.js:
var Service = require('node-windows').Service;
// Create a new service object
var svc = new Service({
name:'Node app hello',
description: 'Node app hello as Windows Service',
script: 'C:\\npm\\.node_modules_global\\hello.js'
});
// Listen for the "install" event, which indicates the
// process is available as a service.
svc.on('install',function(){
svc.start();
});
svc.install();Most important is the correct location of the script in the Service call..
Now run this script to install the service into Windows:
node hello-windows-service.jsNow if you check your services, I hope you’ll find this:
Great! The Node app is running as a Windows service under a local system account. The app is running even when nobody is logged in. Goal achieved. Oh and because it’s on Automatic start, it wil always become available after each server restart.
Based on this article by Peter Eysermans.