tag:blogger.com,1999:blog-42751319578556277602024-03-13T00:31:33.908+01:00 myTselection My selections on new Technology, new software, scripts, etc. or just some stuff I'm busy with.Unknownnoreply@blogger.comBlogger64125tag:blogger.com,1999:blog-4275131957855627760.post-73346123781914944672023-02-21T19:32:00.008+01:002023-10-30T12:06:46.040+01:00Home Assistant custom integration HACS<p>As I wanted to get the data of different websites integrated within Home Assistant, I started to develop some custom integrations (<a href="https://hacs.xyz/" target="_blank">HACS</a>) myself. More details on my own Home Assistant setup can be read in <a href="https://mytselection.blogspot.com/2021/05/home-assistant-free-or-cheap-home-iot.html" target="_blank">my previous post</a>. Doing so, I’ve now developed these:</p> <ol style="text-align: left;"><li><a href="https://github.com/myTselection/telenet_telemeter" target="_blank">Telenet Telemeter</a>: get usage statistics of the Belgian Telenet internet provider and mobile phone data usage</li> <li><a href="https://github.com/myTselection/pixometer" target="_blank">Pixometer</a>: using the Pixometer mobile app, legacy energy meter readings can easily be captured. With this integration this enegery meter readings can now easily be integrated into Home Assistant too.</li> <li><a href="https://github.com/myTselection/youfone_be" target="_blank">Youfone</a> Belgian mobile phone provider usage statistics</li> <li><a href="https://github.com/myTselection/bibliotheek_be" target="_blank">Bibliotheek.be</a> Belgian library information to get info on library loans and allow automation of loan extensions</li><li><a href="https://github.com/myTselection/Carbu_com" rel="nofollow" target="_blank">Carbu.com and Mazout.com</a>: retrieve best prices for super95, diesel and fuel oil in neighbourhood, including prediction on price changes expected in near future.</li> <li><a href="https://github.com/myTselection/MijnTuin" target="_blank">MijnTuin</a>: Integration of the garden calendar of MijnTuin.org</li><li><a href="https://github.com/myTselection/TotalEnergiesClubCard" target="_blank">TotalEnergies Club Card</a>: small limited integration to get info on the points and transactions made with the TotalEngergies Club Card.</li><li><a href="https://github.com/myTselection/MyEnergy" target="_blank">MyEnergy</a>: integration to regularly fetch the Belgian gas and electricity prices of the website mijnenergie.be. </li><li><a href="https://github.com/myTselection/JNM" target="_blank">JNM</a>: integration to fetch JNM activities</li></ol> <p>All these integration use a common code base. Bibliotheek_be also has several services defined. Following sources helped me during analysis and development:</p> <ul> <li><a href="https://developers.home-assistant.io/docs/creating_component_index" title="https://developers.home-assistant.io/docs/creating_component_index">https://developers.home-assistant.io/docs/creating_component_index</a></li> </ul> <p>In order to get integration logo integrated in HACS, I forked <a href="https://github.com/home-assistant/brands" title="https://github.com/home-assistant/brands">https://github.com/home-assistant/brands</a>, created a new branch and added the logo.png and icon.png files within a subfolder with the name of the integration within folder ‘custom_integration’.</p> <p>To make sure the new custom integration meets the <a href="https://hacs.xyz/docs/publish/start/" target="_blank">requirements</a> defined by HA and HACS, github actions need to be added into each repo so they can run on every push into github. </p> <ul> <li><a href="https://github.com/home-assistant/actions#hassfest">https://github.com/home-assistant/actions#hassfest</a></li> <li><a href="https://github.com/hacs/action">https://github.com/hacs/action</a></li> </ul> <p>In order to get these new integrations registered within the standard known HACS integrations, I forked <a href="https://github.com/hacs/default" title="https://github.com/hacs/default">https://github.com/hacs/default</a>, created a new branch and added my repo names into ‘integration’ file and launched a pull request to get it integrated in the default HACS repo.</p><p>Update: 30/10/2023 added new custom integrations</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-3855319527592528952021-11-18T10:40:00.002+01:002021-11-18T12:58:29.157+01:00Samsung Buds2 linked to iPhone - what works and what won't<p>Recently I got some new <a href="https://www.samsung.com/us/mobile-audio/galaxy-buds2/" target="_blank">Samsung Galaxy Buds2</a> (2021). These are looking very nice and sound is good but it seems the <a href="https://apps.apple.com/us/app/samsung-galaxy-buds/id1491433898" target="_blank">Samsung Buds app</a> is not supporting this new device on iPhone. As a consecquence, not all options and configuration is available as it would on a recent Android.</p><p>Yet, after playing around with the device on my iPhone, I can conclude so far what is working with Buds2 and iPhone:</p><p></p><ul style="text-align: left;"><li>Bluetooth connection can be setup within iPhone config Bluetooth</li><ul><li>To force the Buds2 into a Bluetooth pairing, long press (> 3sec) on both Buds2 at the same time. They will let you hear they are in a Bluetooth discoverable mode</li></ul><li>When a music player is active, music can be stopped and started with a single click on any of the Buds2</li><li>When someone is calling your phone and you want to pick-up the phone, you can double click any Buds2 in order to pick-up or end the phone call.</li><li>The Active Noice Cancellation (ANC) can be enabled and disabled by long pression any of the Buds2. Long pressing the Buds2 will enable the outside microphones so you can talk to someone and have a standard life conversation with someone in the room without the need of taking the Buds2 out of your ears. Long pressing again will disable these outside mics so surrounding noice will no longer be actively amplified.</li></ul><div>So far, these are the functions that I have found still to be working even while connected to an iPhone and thus having a limited functionality set.</div><p></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-10779386676478437772021-05-28T10:50:00.001+02:002021-05-28T10:50:12.043+02:00my “Chrome extensions” selection<ol> <li><a href="https://chrome.google.com/webstore/detail/bnomihfieiccainjcjblhegjgglakjdd">'Improve YouTube!' (Video & YouTube Tools)</a> – Extra YouTube player option and default settings</li> <li><a href="https://chrome.google.com/webstore/detail/gighmmpiobklfepjocnamgkkbiglidom">AdBlock — best ad blocker</a> – Block adds</li> <li><a href="https://chrome.google.com/webstore/detail/hgmloofddffdnphfgcellkdfbfbjeloo">Advanced REST client</a> – Play with REST webservices</li> <li><a href="https://chrome.google.com/webstore/detail/fhbjgbiflinjbdggehcddcbncdddomop">Postman</a> – Play with REST webservices<!--EndFragment--></li> <li><a href="https://chrome.google.com/webstore/detail/lamaakaemgdclpnfbofmhpkanfnojjch">Allow Select And Copy</a> – Some pages try to block select and copy functionality</li> <li><a href="https://chrome.google.com/webstore/detail/nkgllhigpcljnhoakjkgaieabnkmgdkb">Don't Fuck With Paste</a> – Som pages try to block copy / paste functionality<!--EndFragment--></li> <li><a href="https://chrome.google.com/webstore/detail/iiblfhmcehfejobfioibjbdpgigcmkgj">Auto Refresh Page</a> – Sometimes you want a page to refresh on regular base</li> <li><a href="https://chrome.google.com/webstore/detail/cgjnhhjpfcdhbhlcmmjppicjmgfkppok">DownAlbum</a> – Assist to download foto albums shared via web</li> <li><a href="https://chrome.google.com/webstore/detail/cnpniohnfphhjihaiiggeabnkjhpaldj">Image Downloader</a> - Assist to download foto albums shared via web<!--EndFragment--></li> <li><a href="https://chrome.google.com/webstore/detail/cmeckkgeamghjhkepejgjockldoblhcb">Export links of all extensions</a> – Create overview of all Chrome Extensions installed</li> <li><a href="https://chrome.google.com/webstore/detail/fdpohaocaechififmbbbbbknoalclacl">GoFullPage - Full Page Screen Capture</a> – Capture full web pages (incl scrolling down) as image or PDF</li> <li><a href="https://chrome.google.com/webstore/detail/ghbmnnjooekpmoecnnnilnnbdlolhkhi">Google Docs Offline</a> – Support for offline Google Docs functionalities</li> <li><a href="https://chrome.google.com/webstore/detail/bmnlcjabgnpnenekpadlanbbkooimhnj">Honey</a> – Search for coupon codes when shopping online</li> <li><a href="https://chrome.google.com/webstore/detail/hehijbfgiekmjfkfjpbkbammjbdenadd">IE Tab</a> – Some sites still need Internet Explorer</li> <li><a href="https://chrome.google.com/webstore/detail/eokekhgpaakbkfkmjjcbffibkencdfkl">Local Explorer - File Manager on web browser</a> – Allow to open Windows Explorer from website url</li> <li><a href="https://chrome.google.com/webstore/detail/gojbdfnpnhogfdgjbigejoaolejmgdhk">OneNote Web Clipper</a> – Clip website into OneNote</li> <li><a href="https://chrome.google.com/webstore/detail/opefiliglgllmponlmoajkfbcaigocfc">Recently Closed Tabs</a> – See list of recently closed tabs</li> <li><a href="https://chrome.google.com/webstore/detail/pnlccmojcmeohlpggmfnbbiapkmbliob">RoboForm Password Manager</a> – Password manager integration</li> <li><a href="https://chrome.google.com/webstore/detail/nepmkgohgoagfgcoegjaggacodcpdibj">Selection Highlighter</a> – Highlight the text select in the full website</li> <li><a href="https://chrome.google.com/webstore/detail/mpiodijhokgodhhofbcjdecpffjipkle">SingleFile</a> – Download webpage as single mhtml file</li> <li><a href="https://chrome.google.com/webstore/detail/hbdpomandigafcibbmofojjchbcdagbl">TweetDeck by Twitter</a> – Twitter with options and multi accounts</li> <li><a href="https://chrome.google.com/webstore/detail/djflhoibgkdhkhhcedjiklpkjnoahfmg">User-Agent Switcher for Chrome</a> – Easily switch user agent</li> <li><a href="https://chrome.google.com/webstore/detail/elicpjhcidhpjomhibiffojpinpmmpil">Video Downloader professional</a> – download video’s</li> <li><a href="https://chrome.google.com/webstore/detail/jnhgnonknehpejjnehehllkliplmbmhn">Web Scraper - Free Web Scraping</a> – download / scrap content of full site or convert site to table</li> <li><a href="https://chrome.google.com/webstore/detail/dpecplbkinpdbedgejddhepkgcppgchk">Multi-File Downloader</a> – download video’s and images from site</li> <li><a href="https://chrome.google.com/webstore/detail/mmeijimgabbpbgpdklnllpncmdofkcpn">Screencastify - Screen Video Recorder</a> – record screen</li> <li><a href="https://chrome.google.com/webstore/detail/bfbameneiokkgbdmiekhjnmfkcnldhhm">Web Developer</a> – options for debugging during web development</li> </ol>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-37346970590052583252021-05-12T15:48:00.023+02:002024-02-13T20:18:42.932+01:00Home Assistant – DIY home IoT and automations domotica<h1>Moving away from IFTTT</h1> <p>I <a href="https://mytselection.blogspot.com/2018/02/if-this-than-that-cloud-service.html" target="_blank">used to orchestrate my home IoT devices</a> such as cheap <a href="https://www.aliexpress.com/wholesale?SearchText=tuya" target="_blank">Tuya</a> or <a href="https://www.aliexpress.com/wholesale?SearchText=ewelink" target="_blank">Ewelink</a> <a href="https://www.aliexpress.com/wholesale?SearchText=sonoff" target="_blank">Sonoff</a> wifi relays using the cloud <a href="https://ifttt.com/home" target="_blank">IFTTT</a> (If This Than That) platform as it made it easy to setup some flows without having any local infrastructure. Yet, quickly the flows became a bit more complex to add smart integration with other platforms such as Tado and Ezviz Wifi doorbell. Initially I still was able to achieve most of the orchestration and rules by integrating other cloud platform such as <a href="http://apilio.io" target="_blank">apilio.io</a> and later on <a href="https://switchur.com/" target="_blank">switchur</a>. But as the <a href="https://www.reddit.com/r/ifttt/comments/ipjag0/ifttt_pro_free_limited_to_3_applets/" target="_blank">IFTTT suddenly decided to charge extra high monthly costs</a> (besides the high rates they charge to the manufacturers supporting IFTTT), and my use cases and needs became too complex to manage well with IFTTT, I decided to look at some alternatives. Too bad IFTTT didn’t had a bit more respect for their community and early adopters, but after all, I’m very glad to made the move, as much more fine grained control is now possible.<img align="right" alt="Home Assistant - Wikipedia" border="0" height="154" src="https://upload.wikimedia.org/wikipedia/commons/thumb/6/6e/Home_Assistant_Logo.svg/1200px-Home_Assistant_Logo.svg.png" style="background-image: none; border: 0px currentcolor; display: inline; float: right; margin: 0px 0px 5px;" width="154" /></p> <p>First I tried to play around with <a href="https://www.openhab.org/" target="_blank">OpenHAB</a> as a free home IoT platform, as it can easily run within a normal Windows environment (Java based) and has easy support for remote iOS/Android application. But soon I concluded I should switch to <a href="https://www.home-assistant.io/" target="_blank">Home Assistant</a> as it has a <a href="https://community.home-assistant.io/" target="_blank">massive community support</a> and a great look and feel with <a href="https://www.home-assistant.io/integrations/" target="_blank">many devices and platform</a> supported, including my favorites such as the <a href="https://store.google.com/product/nest_hub_2nd_gen" target="_blank">Google Nest Hub</a> and <a href="https://store.google.com/product/chromecast" target="_blank">Google ChromeCast</a>.</p> <h1>Setup Home Assistant</h1> <p>In order to run Home Assistant, one should check out the excellent <a href="https://www.home-assistant.io/installation/" target="_blank">installation documentation</a> they provide. I chose to use a docker container in order to first test within a Windows environment but next run the same on an old small media pc on which I installed Ubuntu. Running perfectly on limited CPU, 2GB Ram and an 250GB SSD.</p> <h2>Home Assistant Docker</h2> <p>To just run home assistant <a href="https://docs.docker.com/docker-for-windows/" target="_blank">docker in Windows</a>, make sure to have installed <a href="https://docs.docker.com/docker-for-windows/wsl/" target="_blank">WSL</a>. In order to store your Home Assistant configuration outside of the docker container, a volume (folder) should be mapped from your OS towards the docker container. These files will be fully accessible from the Docker container, but will not be touched when upgrading or removing your docker container, to make sure your personal stored files are not lost. This mapping is possible with –v option:</p> <p>initial install:</p> <blockquote> <p><font face="Courier New">docker run --init -d --name="home-assistant" -e "TZ=Europe/Brussels" -w /config -v "<path>\ha-config\:/config" -p 8123:8123 homeassistant/home-assistant:stable</font></p> </blockquote> <p>run docker: </p> <blockquote> <p><font face="Courier New">docker run -d --name="home-assistant" -e "TZ=Europe/Brussels" -w /config -v "<path>\ha-config\:/config" --net=host --restart=unless-stopped homeassistant/home-assistant:stable</font></p> </blockquote> <p>update docker:</p> <blockquote> <p><font face="Courier New">docker update home-assistant</font></p> </blockquote> <p>This will start your personal home assistant and make it accessible on port <font face="Courier New">8123</font>. </p> <p>Yet, after a while other option may be desired and other docker containers of other applications may be used (eg plex, youtube-dl, deepstack, etc). If you don’t want to type the full command with all option at each run, you can use docker compose to store your configuration option and allow to easily start, stop, upgrade etc. A <font face="Courier New">docker-compose.yml</font> file will just store your options for you to ease the commands to run.</p> <p>A basic <font face="Courier New">docker-compose.yml</font> file used in Linux Ubuntu example shown below:</p> <blockquote> <p><font face="Courier New">version: '3' <br /> services: <br /> homeassistant: <br /> container_name: home-assistant <br /> image: homeassistant/home-assistant:stable <br /> volumes: <br /> - /home/myt/homeassistant/:/config <br /> - /home/myt/homeassistant/media/:/media <br /> environment: <br /> - TZ=Europe/Brussels <br /> restart: unless-stopped <br /> network_mode: host</font></p> </blockquote> <p>With such as docker-compose file, below commands can be used. The container name <font face="Courier New">homeassistant</font> at the end of these commands is optional, it can be used if multiple dockers are defined in your compose file:</p> <p>start with docker compose :</p> <blockquote> <p><font color="#333333" face="Courier New">docker-compose up –d homeassistant</font></p> </blockquote> <p>restart with docker compose:</p> <blockquote> <p><font color="#333333" face="Courier New">docker-compose restart –d homeassistant</font></p> </blockquote> <p>update with docker compose:</p> <blockquote> <p><font color="#333333" face="Courier New">docker-compose pull</font></p> <p><font face="Courier New">docker-compose up -d --build homeassistant</font></p></blockquote><p><a href="https://community.home-assistant.io/t/running-commands-on-the-host-without-ssh/510481/14" target="_blank">Advanced tips for running commands on the host without ssh</a> </p> <h2>Remote access</h2> <p>The main development of the free and open source project Home Assistant is supported by the Dutch company <a href="https://www.nabucasa.com/" target="_blank">Nabu Casa</a>. They get their money to continue the development of Home Assistant by adding full remote cloud support to your local Home Assistant environment. This allows easy integration with Amazon and Google cloud services (assistant, text to speech, Nest Hub) and remote access to control your home with the Home Assistant app. These services require access to your local free Home Assistant deployment. Yet, these paid cloud services (5$/month) are not mandatory and all these cloud services can also be achieved when manually setup. It will just require some extra manual effort. Anyone who may not be familiar with Linux configuration and less IT minded will definitely be advise to use this paid subscription. Yet below approach is an alternative.</p> <p>Different options exist to get remote access to your local server. As most will receive a dynamic IP address from their ISP that will regularly change, some forwarding is required in order to always know the public IP address of your local server. A <a href="https://www.google.com/search?q=free+dynamic+dns+providers" target="_blank">dynamic DNS forwarder</a> may be supported by your router or a little application can be installed on your server to update the public address of a DNS. Yet, even when the dynamic public IP address is know, this will require you to open a port on your router and allow inbound traffic from the internet towards your local server. Another solution that could be used is by running <a href="https://telebit.cloud/" target="_blank">telebit.io</a> or <a href="https://github.com/AlexxIT/Dataplicity" target="_blank">dataplicity</a> (similar to <a href="https://ngrok.com/" target="_blank">ngrok</a> but will keep for free your unique fixed dns whichs is only possible in the paid version of ngrok). With this little telebit service you can easily install and get a fixed telebit.io DNS linked to a specific port, such as port 8123 of your local Home Assistant by running below and confirming your email address: </p> <blockquote> <p><font color="#333333" face="Courier New">> curl https://get.telebit.io/ | bash</font></p> <p><font face="Courier New">> ~/telebit http 8123</font></p> </blockquote> <p>Over time, I notice local network interruptions may interrupt the telebit forwarding. As a result, I configured a little cronjob that will check every 5 minutes if the external telebit url is still accessible. If not, it will deactivate and activate telebit again.</p> <blockquote> <p><font face="Courier New">> crontab –e</font></p> <font face="Courier New"></font> <p><font face="Courier New">*/5 * * * * /home/myt/check_telebit.sh</font> <br /></p> </blockquote> <p><font face="Courier New">check_telebit.sh:</font></p> <blockquote> <p><font face="Courier New">#!/bin/bash</font></p> <p><font face="Courier New">URL=</font><a href="https://***.telebit.io"><font face="Courier New">https://***.telebit.io</font></a> <br /><font face="Courier New"> EXPECTED_STATUS=200</font></p> <p><font face="Courier New">status=$(curl -s -o /dev/null -w '%{http_code}' $URL) <br /> if [ $status -ne $EXPECTED_STATUS ]; then <br /> echo "HTTP status code is not $EXPECTED_STATUS. Disabling and re-enabling Telebit." <br /> # disable Telebit <br /> /home/myt/telebit disable <br /> # enable Telebit <br /> /home/myt/telebit enable <br /> else <br /> echo "HTTP status code is $EXPECTED_STATUS" <br /> fi <br /></font></p> </blockquote> <p>Whatever solution is chosen, once a public accessible URL is available towards your local Home Assistant, it can be used within the Home Assistant iOS and Android apps and within the configuration of Amazon or Google Cloud services. </p> <p>It might be required to also define the external public url within your HA <font face="Courier New">configuration.yaml</font> file:</p> <blockquote> <p><font face="Courier New">homeassistant: <br /> external_url: !secret external_url</font></p> </blockquote> <p>As soon as any remote public online access is enabled, I would strongly advice to make sure the server is regularly patched with security updates as well as <a href="https://www.home-assistant.io/docs/authentication/multi-factor-auth/" target="_blank">enabling 2FA within Home Assistant</a>, as you should with <a href="https://2fa.directory/" target="_blank">any other cloud service</a>.</p> <h1>Integration of services and devices</h1> <p>Once Home Assistant is up and running, it allows you to easily connect <a href="https://www.home-assistant.io/integrations/" target="_blank">many different devices and cloud services</a>. These can be setup using the Home Assistant Configuration Dashboard (<a href="https://localhost:8123/config/dashboard" target="_blank">localip:8123/config/dashboard</a>) –> Integrations. Yet, also many services will require to be defined within the ‘<font face="Courier New">configuration.yaml</font>’ file stored within the <font face="Courier New">config</font> folder of your Home Assistant. All information required to correctly define these in your local config can be found on the excellent <a href="https://www.home-assistant.io/docs/" target="_blank">documentation pages of Home Assistant</a>.</p> <h2>Services</h2> <p>Below a list of the services I found most useful:</p> <ul> <li><a href="https://www.home-assistant.io/integrations/calendar.google/" target="_blank">Google Calendar</a>: whenever I need some activity to be scheduled on time, I do prefer to create a specific Google Calendar for it instead of hard coding it in scripts or automations. This makes it possible to get a clear overview of your schedules (using different colors for each calendar) and will also make it possible to easily add repetitions or exceptions (eg during holidays) by just planning it within your calendar as if you’d plan a regular meeting. Once the calendar is defined, an automation can be created that will trigger whenever a meeting starts and/or stops. </li> <ul> <li>Good to know: within a <a href="https://www.home-assistant.io/docs/automation/trigger/" target="_blank">trigger</a> by state change, you can define a ‘From’, ‘To’ state and ‘For’ how long that state should be active before the action is triggered. Eg the calendar will change to state ‘on’ when a meeting becomes active. The status can always be checked easily using the ‘<a href="http://localhost:8123/developer-tools/state" target="_blank">Developer Tools</a>’ page. Yet, if both ‘For’ and ‘To’ are left empty, the automation can be triggered by any change. I found this very useful as I initially tended to copy the automation in order to get a trigger for all states expected, while a common trigger was easier.</li> <a href="https://drive.google.com/uc?id=1MIdzerAavqQ9E_m4LY6f0NeEOTE4GFou"><img alt="HaCalendarTrigger" border="0" height="244" src="https://drive.google.com/uc?id=164DSGllSZsYbBpz4qA_DB8ehqMSkwJy6" style="background-image: none; display: inline; margin: 0px 0px 5px;" title="HaCalendarTrigger" width="213" /></a></ul> <li><a href="https://www.home-assistant.io/integrations/manual" target="_blank">Alarm</a>: an Alarm control panel can be defined which allows you to arm, disarm and many other options. To set this up, I followed much of the approach <a href="https://www.thouters.be/HassHandsfreeAlarm.html" target="_blank">as explained here by Thomas</a>. Based on groups of sensors and groups of users, I automatically arm and disarm my custom alarm. When leaving (armed_away) or when at home (armed_home) sensors will trigger the alarm which will start recording on the camera’s, send notification and flash lights. Yet, in contradiction with the <a href="https://www.thouters.be/HassHandsfreeAlarm.html" target="_blank">approach of Thomas</a>, I <strong>don’t use a group to initiate the triggers</strong>. Since, once the group has state ‘open’ it will no longer detect other doors or windows being opened. So if for example you would have a window open before leaving, the group state will be open before the alarm is armed. Next, if another window in the same group would open, the group state will remain open and thus not trigger the alarm. Using Google Nest mini, Hub and ChromeCast integration I can also start a <a href="https://www.youtube.com/watch?v=Jr0HpAENhTM" target="_blank">sirene youtube video</a> at full volume and flash blue led lights. Different advanced options exist in order to get <a href="https://www.smarthomelab.ca/advanced-ios-notifications/" target="_blank">advanced notifications</a> with specific option and sounds. An example below of an alert notification in case of alarm triggered, this requires <a href="https://www.home-assistant.io/integrations/ios/" target="_blank">ios</a> <a href="https://companion.home-assistant.io/docs/notifications/actionable-notifications/" target="_blank">actionable notifications</a> push category ‘camera’ to be defined in order to allow immediate action on the notification to disarm. The camera category has special treatment in order to allow to open a life stream of the camera when long-pressing the notification on the iPhone. Using the url option, a specific lovelace site in the Home Assistant can be opened whenever the notification is clicked. The <font face="Courier New">update.caf</font> default iOS sound made some sound that would take my attention when needed.</li> <blockquote> <p><font face="Courier New">- service: notify.mobile_app_iphone <br /> data: <br /> url: /lovelace-alarm/alarm <br /> title: Alarm <br /> message: Alarm at home {{now().strftime("%H:%M:%S %d/%m/%Y")}} <br /> data: <br /> entity_id: camera.ip_cam_stream <br /> attachment: <br /> content-type: jpeg <br /> apns_headers: <br /> apns-collapse-id: alert <br /> push: <br /> sound: <br /> name: Update.caf <br /> critical: true <br /> category: camera <br /> thread-id: alarm</font> <br /></p> </blockquote> <li><a href="https://www.callmebot.com/" target="_blank" title="https://www.callmebot.com/">Telegram notification call</a>: Still in order to make sure I would be aware of alarm events (eg at night of while away) I preferred to get called to make sure I would wake up. By integrating the CallMeBot api linked to telegram, this can be achieved. The bot will make a call and with text to speech read the pre-defined message eg indicating which sensor was triggered.</li> <li><a href="https://github.com/hacs/integration" target="_blank">HACS</a>: HACS is THE un-official Home Assistant Community Store. It supports many custom integrations of services and extra front-end customizations. Once <a href="https://hacs.xyz/docs/installation/installation" target="_blank">HACS is installed</a>, it will be very easy to find, install and update any other custom integration.</li> </ul> <h2>Devices and integrations</h2> <p>As I used to only use cloud based services with IFTTT, most of my devices can still be controlled via the different cloud manufacturers, eg Ewelink and Tuya. These have a huge set of cheap wifi devices connecting through their platform. Using a local Home Assistant, you could opt to disable most of these cloud accesses if you don’t trust the cheap China based firmwares. Yet, for me I do prefer to have a fallback to control my devices using Home Assistant and the specific device apps. If my Home Assistant would not be accessible remotely, I may still be able to control the devices directly. </p> <ul style="text-align: left;"> <li>Sonoff Ewelink wifi relays (+- €6 per relay): the relay just allows to turn on or off the current on a wire. Whatever is connected to it will thus get power on or off by it. I use it to control lights, water heating, cameras that doesn’t support full integration, garage ports, etc. <a href="https://github.com/AlexxIT/SonoffLAN" target="_blank">Integration of Ewelink Sonoff</a> devices is now well supported in combination with the standard Sonoff cloud firmware.</li> <li>Sonoff Ewelink wifi power plug switch (+- €6 per switch): Same behavior as the wifi relay, but made for power cord connection</li> <li>Sonoff Ewelink wifi door sensors (DW2) (+- €6 per sensor): these very small battery based sensors will just detect if the sensor is open or closed. Based on this the alarm can trigger if desired, etc.</li> <li>tado central heating (base kit +- €200, +- €60 per extra optional radiator valve): this allows multi room valves to control temperature in each room separately and get constant sensor information on temperature and humidity of each room. Over time, I extended this to multiple rooms as it showed to give a high comfort: when needed separate room temperature can be increased while also allowing to limit the heating to minimum when not needed. So over time, this may result in some heating cost saving.</li> <li>Ezviz wifi doorbell DB1 with camera (+- €100): doorbell that will call you on your phone when someone rings the bell, it includes sensor for detecting motion (before button is pressed) and camera to see life feed. It support local SD card recording and <a href="https://community.home-assistant.io/t/platform-error-camera-requirements-for-ezviz-not-found-pyezviz-0-1-5/231640/170?u=myt" target="_blank">recording using Home Assistant</a>, by doing so, no cloud recording subscription is needed (while possible). Motion detection <a href="https://github.com/RenierM26/ha-ezviz" target="_blank">integration within HA</a> is possible, yet so far the bell button press event can’t be captured within Home Assistant yet.</li> <li>Tuya wifi power plugs (+- €6): I connect an AirWick power plug-in to this smart plug, so I can plan when the AirWick fragrance should be release (it’s too overwhelming when leaving powered on all the time). So based on a specific Google Calendar I repeat the AirWick to turn on and off at regular intervals. Using <a href="https://github.com/rospogrigio/localtuya" target="_blank">local tuya HACS</a> or <a href="https://www.home-assistant.io/integrations/tuya/" target="_blank">standard tuya integration</a>, full integration within HA is working well.</li> <ul> <li>Many of these Tuya smart power plugs also offer power monitor functionality. This can easily be integrated into the <a href="https://www.home-assistant.io/blog/2021/08/04/home-energy-management/" target="_blank">power moniotorring</a> of Home Assistant. If the device only provided current, it can be converted into kWh using the <a href="https://www.home-assistant.io/integrations/utility_meter/" target="_blank">Utility Meter integration</a>.</li> </ul> <li>Cheap IP camera’s (+- €15 per camera): some don’t allow full integration, but by connecting these to a wifi relay, still they can be turned on and off. Some cheap ip camera’s do support rtsp video streaming, which can be integrated into HA generic <a href="https://www.home-assistant.io/integrations/#camera">camera</a> integration. In order to find the correct rtsp url to use, eg with ffmpeg, one can use the <a href="https://sourceforge.net/projects/onvifdm/" target="_blank">onvif device manager</a> tool which will scan your local network and discover many cameras including the rtsp url, or using <a href="https://camera-sdk.com/p_6632-ip-camera-rtsp-url.html" target="_blank">this site</a> detecting your IP cam device type and url. Doing so, I discovered I was still able to integrate some cameras I didn’t expect they would work. Example configuration.yaml: </li> <blockquote> <p><font face="Courier New">camera: <br /> - platform: ffmpeg <br /> name: Living webcam YCC365 <br /> # PTZ support for YCC365 compatible cameras: https://github.com/fjramirez1987/PTZ-YCC365 <br /> input: !secret YCC365_rtsp_url <br /></font></p> </blockquote> </ul> <ul style="text-align: left;"> <ul> <li>I also bought a cheap Tuya based wifi IP camera (+- €15). <strike>Because it was based on Tuya, I expected to be able to get a better integration in Home Assistant platform. Yet, it seems these cameras don’t allow any RTSP life streaming feed of the camera image, nor ONVIF protocol. Even the motion detection notifications can’t be integrated, except within the Tuya application itself. So I’m still looking for a good wifi ip camera with optimal Home Assistant integration to be able to trigger any Home Assistant event as soon as a motion is detected. As a temporary workaround I now trigger a Tuya wifi plug state when a motion is detected. With <a href="https://github.com/rospogrigio/localtuya" target="_blank">Home Assistant local tuya HACS</a> integration, this plug state switch can immediately be detected and next this can trigger any other event within Home Assistant. This allows alarm trigger on camera motion detection, but is not optimal off course. Since the AirWick sense is only connected to this Tuya wifi smart plug, briefly turning it on and off again at some motion detection has not much further impact.</strike>Since 10/2021 the Tuya integration within HA has been renewed and now much better support is available, <a href="https://community.home-assistant.io/t/tuya-security-camera-missing-motion-detected-sensor/361707" target="_blank">including camera support</a>!</li> <li>When you’d have multiple camera’s to show in a web lovelace, it might be interesting to look at the <a href="https://github.com/AlexxIT/WebRTC" target="_blank">WebRTC</a> plugin which makes it possible to have faster rtsp streaming.</li> </ul> <li><a href="https://www.aliexpress.com/item/4001155629821.html" target="_blank">Tuya water irrigation wifi control</a> (+- €35): open or close a water to allow remote garden irrigation</li> <li>Tuya wifi roller blind motor (+- €40): remote control roller blinds and by integrating within Home Assistant they can become smart and eg take into account sun state or anything else</li> <li>Tuya wifi IR controller (+- €15): allows to control all infra red devices via wifi. this allows to ‘integrate’ many old ‘dump’ devices, althout the integration is not always optimal since you can’t get any feedback or status info of the device.</li> <li>Tuya wifi smoke detector (+- €15): get notified when smoke is detected at home and sound a loud alarm</li> <li><a href="https://github.com/arjenvrh/audi_connect_ha" target="_blank">Audi connected car integration</a>: see mileage, state of car doors, windows and locks</li> <li><a href="https://www.home-assistant.io/integrations/cast/" target="_blank">Google ChromeCast</a>, Audio ChromeCast and Google Nest Hub (€35 - €100): ChromeCast devices allow great interaction and Home Assistant has good support for these.</li><li>Tuya ZigBee gateway & ZigBee door sensor and scene button: </li><ul><li>These ZigBee devices didn't seem supported by default in Home Assistant. In order to still be able to read the states of such unsupported device, it might be a solution to <a href="https://developer.tuya.com/en/docs/iot/link-virtual-devices?id=Kat3060fjxmoc" target="_blank">add a 'Vritual Tuya Device'</a> (when adding a virtual device, best to add it into the 'Tuya App Account' instead of into an Asset, to make sure it becomes visible in Home Assistant)</li><li>Using the Tuya app build in scene's, the virtual devices (eg switch) can be set whenever the states of the Home Assistant unsupported device state would change. This will allow to still see the actual state of such unsupported device in Home Assistant and interact with it.</li></ul> </ul> <h1>Personal tips for automations and scripts</h1> <h2>Scripts to return to automatic default state</h2> <p>While defining different automations and scripts, you will notice this will probably be a incremental approach. Specific occupations will occur, which you’ll notice you may want to cover over time, extending your automations and scripts to cover more exceptional cases or combinations etc.</p> <p>Personally, I tried to use the automations to trigger and initiated special activities, yet, within the ‘Action’ part of the automation, I would try to use as most as possible a call to a script. This allows you to re-use many actions as the same script can be called at other triggered events. This will make sure you won’t have to redefine the same actions over and over again for each automation.</p> <p>For many group of devices I also tried to follow the same setup as used by <a href="https://www.tado.com" target="_blank">tado</a>: define a default standard schedule and allow to overrule it manually whenever desired. As soon as the manual overruling is no longer needs, return to the default automatic schedule.</p> <ul> <li>I most often define a default automatic schedule using a specific Google Calendar: eg when outside lights need to turn on and off, when camera’s need to turn on and off, when water heating needs to trigger, etc. </li> <li>Within the Home Assistant web and app interface, all buttons are available to overrule this automatic default schedule</li> <li>I created for all these groups of devices a script to return to automatic state whenever launched. This ‘automatic’ or ‘default’ script became very important. Whenever a specific intervention is ended, I would not call to turn on or off a specific device, but always call this ‘auto’ script in order to make sure the device can return to the state it should be as defined by the default schedule. </li> </ul> <p>For example: I created a default schedule to light up some outside lights in the evening based on a specific Google Calendar. But I don’t want to keep these light on all night, only in the evening and morning. Yet, I return home in the middle of the night, I still want these outside lights to light up for a short time. So the lights follow the schedule in the calendar and whenever I return while the sun is down, I make sure the lights will light up. But after a while, the light will not be turned off but the ‘auto’ script will be triggered to make sure they return to their default schedule. So the 'auto' script will detect if the lights should go on or off depending on sun state and calendar planning. I applied the same approach for cameras, heating, water heating, alarm etc. These ‘auto’ scripts tent to become complex with different conditions to cover all cases, but once defined, any automation can easily call such ‘auto’ script at any time without worrying about all special cases and combinations. This has proven to be very valuable.</p> <h2>Parallel processing</h2> <p>While writing different scripts and automations in Home Assistant, one should remember when an automation or script sequence is launched, all commands will be executed in the specific sequence one after another. If multiple actions would need to be executed in parallel, different automations can be created that get triggered. Eg by creating a variable (‘Configuration > Helpers’). As soon as the state of this variable changes, all automations depending on this helper variable state will be triggered and execution will be started in parallel. For an alarm activation this may be needed to make sure the different actions get triggered asap, such as recording, lights, notifications etc.</p> <h2>Icons Material Design</h2> <p>It is possible to assign custom icons to devices, buttons, scripts etc within Home Assistant. The easiest way to achieve this is by using Material Design icons, which are natively supported, eg using ‘<font face="Courier New">mdi:account</font>’ to get a kind of <a href="https://materialdesignicons.com/icon/account" target="_blank">account/user</a> type of icon. </p> <p>As many icons are supported by Material Design, <strike>I mostly use <a href="https://materialdesignicons.com" target="_blank">this materialdesignicons.com website</a> with good search capabilities in order to easily search through the long list of icons and try to find some icons that matches best my needs.</strike> Since late 2021 extra support has for Material iconas has been added withing HA which allows very easy Material Design icon selection within HA itself.</p> <h2>Most valuable uses cases to automate</h2> <ul style="text-align: left;"> <li>Full autonomous <b>alarm system</b>: turning on and off based on location of the family members, see details above. It will turn on and off the in house cameras making sure the camera’s are only capturing when needed so to limit capturing private privacy in house activity.</li> <li>Warn if the <b>garage gate</b> is open, with smart notifications to easily close the garage gate or trigger the alarm. As long as the garage gate remains open, the warning notification will triggered regularly (every 30min) to keep on warning the gate remains open.</li> <li>Whenever the garage gate is open, start the garage camera to record any motion detected</li> <li>Warn when <b>leaving the house</b> while some doors or windows are still open, indicating in the notification which door or window is not closed.</li> <li>Warn when <b>windows are open</b> for a too long time, while it’s cold outside.</li> <li>Warn when the <b>battery</b> of door and window sensors are getting low</li> <li>Control the <b>lights outside the house</b>, to turn on and off based on sunset and sunrise or alarms</li> <ul> <li>Flash the front door lights several times when arriving home and <b>frost is expected</b> the next day so to warn the car windshield should be protected against frost.</li> <li>Turn off the lights in the middle of the night till morning, but when arriving home in this period, still turn on the front door lights for a short period while entering the house.</li> </ul> <li>Turn on hall light at night or when cloudy weather</li> <li>If <b>motion </b>is detected at the front door, cast a life feed to Google <b>Nest Hub</b> to see what’s happening</li> <ul> <li>These use cases quickly became a bit more complex as exceptions where desired to only start the casting when someone is home and no casting on the hub was active. If some music casting was active, it would continue the music after a short interruption to show the front door camera stream.</li> </ul> <li>Start <b>camera recording</b> when alarm is triggered</li> <ul> <li><a href="https://community.home-assistant.io/t/platform-error-camera-requirements-for-ezviz-not-found-pyezviz-0-1-5/231640/170?u=myt" target="_blank">store pictures of last alarms</a> triggered by Ezviz DB1 doorbell</li> </ul> <li>Warn if my <b>car is unlocked</b> for a long period or when it’s unlocked while no one of the family is close to the car.</li> <li>When taking a <b>bath in the bathroom</b>, make sure the temperature of the bathroom is high enough and hot water heating is enabled.</li> <li>Disable the <b>bathroom ventilation</b> system at night (as it makes too much noise). </li> <li>Detect and <b>notify high humidity</b> (mostly in bathroom). If high humidity is detected, allow to easily turn on heating and enable bathroom ventilation system.</li> <li>If a room <b>heating</b> is on yet some <b>window</b> is <b>open </b>in the same room, send a warning notification.</li> <li>If warm outside and no rain is expected in coming days and it didn’t rain much in previous days, start the <b>water irrigations</b> system for a while early in the morning.</li> <li>Turn on some <b>color led lamp behind television</b> when casting and making the led color randomly change when casting music.</li><ul><li><a href="https://community.home-assistant.io/t/fireplace-mood-script-led-rgb-random-colors/647407" target="_blank">Make the led color lamp randomly change color in fireplace theme colors</a></li></ul> <li>When a song is playing on ChromeCast or Nest Hub, allow with one click to retrieve artist and song name and query it for immediate <b>mp3 download on YouTube-DL</b> to store the music in our offline music Plex collection. With some iOS shortcuts, any song we hear (eg on radio) can be identified (eg with <a href="https://www.musixmatch.com/" target="_blank">MusiXMatch</a> / Shazam) and the song name and artist can be forwarded through Home Assistant (since remotely accessible) which will trigger the mp3 download using YouTube-DL. This way, the YouTube-DL site and API don’t need to be publicly/remotely accessible, so all queries are forwarded via Home Assistant with REST API.</li> <li>Monitor <b>power consumption of fridge/freezer</b> to get a warning if a long period is detected in which no power is used or very high power is used. I want to (try to) detect if the fridge has lost power or if the <b>freezer door wouldn't be closed</b> correctly (resulting in high power usage).</li> <li>Monitor <b>power consumption</b> of our home. <strike>I have made a REST custom integration to fetch Pixometer.io values to be retrieved from the </strike><a href="https://community.home-assistant.io/t/pixometer-integration/285608/4?u=myt" target="_blank"><strike><b>Pixometer</b>.io API</strike></a><strike>.</strike> I made my own <a href="https://github.com/myTselection/pixometer" target="_blank">custom HACS integration</a> to retrieve Pixometer.io scan details.</li> <li>Track <a href="https://community.home-assistant.io/t/rest-sensor-needs-to-get-latest-element-of-list/404882" target="_blank"><b>fuel prices</b> from mazoutvoordeel.be</a> and get notified if low prices are detected and/or fuel tank is getting empty.</li> <li>Track library loans and automatically extend loans when only couple of days are left, using my own <a href="https://github.com/myTselection/bibliotheek_be" target="_blank">custom HACS integration ‘Mijn Bibliotheek.be’</a></li> <li>Track Telenet internet subscription usage (telemeter), with my own <a href="https://github.com/myTselection/telenet_telemeter" target="_blank">custom HACS integration Telenet Telemeter</a>.</li> <li>Track Youfone.be mobile phone and data subscription usage, with my own <a href="https://github.com/myTselection/youfone_be" target="_blank">custom HACS integration Youfone.be</a>.</li> <li>Check if <b>traffic to work or home</b> might be slower than expected and show a map of the traffic using the Waze travel time integration.</li> <li>'<a href="https://github.com/pippyn/Home-Assistant-Sensor-Afvalbeheer" target="_blank">Afvalbeheer</a>' integration (installed via HACS) to get details of <b>local waste collection</b>. This allows to show specific warnings/notifications. If waste will be collected next day, but the garage door didn't open in last X hours or the phone is connected for charging in the evening, send special notification to highlight the waste collection. Example sensor <span style="font-family: courier;">configuration.yaml</span> for Belgium (RecycleApp):</li> <ul> <li><span style="background-color: white; caret-color: rgba(0, 0, 0, 0); color: #212121; font-family: monospace; font-size: 14px; white-space: pre;">sensor:</span></li> <li> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼe" style="color: #212121;"></span><span class="ͼe" style="color: #212121;"> - </span><span class="ͼj" style="color: #ff9900;">platform</span><span style="color: #212121;"> </span><span class="ͼe" style="color: #212121;">: </span><span style="color: #212121;">afvalbeheer</span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">wastecollector</span></span><span class="ͼe" style="color: #212121;">: </span><span style="color: #212121;">RecycleApp</span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">builtinicons</span></span><span class="ͼe" style="color: #212121;">: </span><span class="ͼb" style="color: #212121;">1</span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">resources</span></span><span class="ͼe" style="color: #212121;">: </span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))"> - </span>restafval</div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))"> - </span>gft</div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))"> - </span>papier</div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))"> - </span>pmd</div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"> postcode</span><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))">: </span>!secret postcode</div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"> streetnumber</span><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))">: </span>!secret streetnumber</div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"> streetname</span><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))">: </span>!secret streetname</div> <div class="cm-line" style="background-color: white; caret-color: transparent; color: #212121; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"> cityname</span><span class="ͼe" color="var(--codemirror-meta, var(--primary-text-color))">: </span>!secret cityname</div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">printwastetypes</span></span><span class="ͼe" style="color: #212121;">: </span><span class="ͼb" style="color: #212121;">0</span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">upcomingsensor</span></span><span class="ͼe" style="color: #212121;">: </span><span class="ͼb" style="color: #212121;">1</span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">dateformat</span></span><span class="ͼe" style="color: #212121;">: </span><span class="ͼn" style="color: #212121;">'%d-%m-%Y'</span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">name</span></span><span class="ͼe" style="color: #212121;">: </span><span style="color: #212121;">Recycle</span></div> <div class="cm-line" style="background-color: white; caret-color: transparent; font-family: monospace; font-size: 14px; padding: 0px 2px 0px 4px; white-space: pre;"><span class="ͼj" color="var(--codemirror-atom, #F90)"><span style="color: #212121;"> </span><span style="color: #ff9900;">dutch</span></span><span class="ͼe" style="color: #212121;">: </span><span class="ͼb" style="color: #212121;">1</span><span class="ͼb" style="color: #212121;"></span></div> </li> </ul> </ul> <h2>Frontend web / app lovelace dashboards</h2> <p>I like to have all house automation easily accessible within the Home Assistant app using the interface (lovelace). Over time I’ve build different views and splitted these up as described below:</p> <ul> <ul></ul> <li>General quick-switch screen with big buttons to easily control all devices, lights etc.</li> </ul> <p> </p> <ul style="text-align: left;"> <li>Alarm screen with immediate view on main sensors and camera and allow to trigger alarm or cast camera to hub</li> <ul> <li>A separate screen is available to show <a href="https://github.com/TarheelGrad1998/gallery-card" target="_blank">photo and video album gallery</a> with recent camera captures of motion detections</li> <li>A similar alarm main info screen is build, but with specific limited buttons specifically to cast to Nest Hub showing main info on small screen without need to scroll</li> <li>It can also show some graphs with recent alarm and sensor activities. Whenever I show graphs, I added a selector to easily define the history horizon to show. This is performed using a simple helper input_number variable that holds the number of hours of history to be loaded. </li> <ul> <li>Standard history graph lovelace entity config:</li> </ul> <blockquote> <p><font face="Courier New">type: history-graph</font></p> <p><font face="Courier New">entities:</font></p> <p><font face="Courier New"> - entity: sensor.speedtest_download</font></p> <p><font face="Courier New">hours_to_show: 48</font></p> <p><font face="Courier New">refresh_interval: 0</font></p> </blockquote> </ul> <ul> <ul> <li>Dynamic history graph, using custom lovelace integration <a href="https://github.com/iantrich/config-template-card" target="_blank">‘config-template-card’</a>:</li> </ul> <!--EndFragment--></ul> <ul> <blockquote> <p><font face="Courier New">type: 'custom:config-template-card'</font></p> </blockquote> <blockquote> <p><font face="Courier New">variables:</font></p> <p><font face="Courier New"> - 'states[''input_number.hours_of_history''].state'</font></p> <p><font face="Courier New">entities:</font></p> <p><font face="Courier New"> - input_number.hours_of_history</font></p> <p><font face="Courier New">card:</font></p> <p><font face="Courier New"> type: history-graph</font></p> <p><font face="Courier New"> entities:</font></p> <p><font face="Courier New"> - entity: sensor.speedtest_download</font></p> <p><font face="Courier New"> hours_to_show: '${vars[0]}'</font></p> <p><font face="Courier New"> refresh_interval: 0</font></p> </blockquote> <ul> <blockquote> <p><a href="https://drive.google.com/uc?id=1R8PCC5MmFsuvItYnDbxCxKSD4U8xGNLr"><img alt="historygraph" border="0" height="164" src="https://drive.google.com/uc?id=1UlAnX_FojKbeqmdWtENW5eicHC4snuun" style="background-image: none; display: inline; margin: 0px 0px 5px;" title="historygraph" width="244" /></a> </p> </blockquote> <p>The same approach can be used with any other lovelace entity card type to have a dynamic parameter, eg I use the same on a map card to <font face="Courier New">hours_to_show</font> on a map:</p> <blockquote> <p><font face="Courier New">type: 'custom:config-template-card'</font></p> <p><font face="Courier New">variables:</font></p> <p><font face="Courier New"> - 'states[''input_number.hours_of_history''].state'</font></p> <p><font face="Courier New">entities:</font></p> <p><font face="Courier New"> - input_number.hours_of_history</font></p> <p><font face="Courier New">card:</font></p> <p><font face="Courier New"> type: map</font></p> <p><font face="Courier New"> entities:</font></p> <p><font face="Courier New"> - entity: person.name</font></p> <p><font face="Courier New"><font face="Courier New"> hours_to_show: '${vars[0]}'</font></font></p> <p><font face="Courier New"> default_zoom: 10</font></p> </blockquote> </ul> </ul> <li>Car overview showing state of car windows and locks and showing mileage and remaining gas. </li> <ul> <li>It can also show a map with car track in recent period and family members presence</li> <li>As the Audi connected services allows to get status of many different windows and locks, I added a bullet on the car picture to easily see which door/window/lock would be on. More or less similar integration is possible with other car vendors such as BMW etc.:</li> <blockquote> <p><a href="https://drive.google.com/uc?id=1xYHdGyjp4Ty8EuShzM7w_UHzQU42Oq0f"><img alt="audi" border="0" height="183" src="https://drive.google.com/uc?id=1y_UiyoKN3-pJPmyGDM9bBYntG7vW9CWI" style="background-image: none; display: inline; margin: 0px 0px 5px;" title="audi" width="271" /></a></p> </blockquote> <ul></ul> </ul> <li>Home central heating screen with clear overview of room temperature and humidity. This screen will allow me to quickly change temperature, hot water heating, control bathroom lights and ventilation, see upcoming weather conditions, etc.</li> <ul> <li>It can also show some graphs showing heating and temperature activity</li> <li>Again the ventilation is not smart, but by just connecting it to a wifi relay, some control to turn on and off is possible even with cheap devices.</li> <ul></ul> </ul> <li>Media screen with overview of media playing and allow to quickly start YouTube, Spotify or plex media to stream to hub or ChromeCast devices. Also allows to control main devices by infra red to easily change volume, channel, etc. To integrate Spotify, I used the <a href="https://github.com/fondberg/spotcast" target="_blank">spotcast</a> integration and developed <a href="https://community.home-assistant.io/t/catch-spotify-interrupted-because-of-streaming-started-on-other-device/526459" target="_blank">a specific automation to detect if the Spotify playback is interrupted</a> because it started to play on another device.</li> <ul></ul> <li>Full floorplan image of all house levels showing all lights and plugs and electricity connections linked to the fuses within the electricity box: Every light or plug is linked to a fuse wiring which enables to quickly see the fuse linked to a specific light or plug. Whenever I’d need to turn off a fuse, I would immediately see which other plugs or lights would be impacted. Any fuse can be enabled/disabled to see which plugs/lights are linked, as well as any light or plug can be enabled/disabled to easily see to which fuse it’s linked. <ul><a href="https://drive.google.com/uc?id=1ES0VDkUfWDLzkgm5eqgJ4o3MArFHvPBp"><img alt="floorplan" border="0" height="244" src="https://drive.google.com/uc?id=1H9E063a3WJ7lPeNwOJWJmVY7pszKNYed" style="background-image: none; display: inline; margin: 0px 0px 5px;" title="floorplan" width="228" /></a> </ul> </li> <li>In order to allow easier configuration, I use many input helpers variables which are easier to change later on in order to tweak specific needs, instead of searching through all automations and scripts. With some templates this can be achieved, for example:</li> <ul> <li>Condition on sun elevation with input_number variables:</li> <ul> <li><span style="font-family: courier;">{{state_attr('sun.sun', 'elevation') < states.input_number.sunset_elevation.state|float }}</span></li> </ul> <li>Conditions based on humidity sensor with input_number variables:</li> <ul> <li><span style="font-family: courier;">{{states.sensor.room_humidity.state|float >= states.input_number.max_room_humidity.state|float}}</span></li> </ul> <li>If for example a delay is required, it can be based on a variable input_number:</li> <ul> <li><span style="font-family: courier;">delay: '{{states.input_number.xxx_timeout_min.state| multiply(60) | int}}'</span></li> </ul> </ul> </ul> <h1>Integration with other applications</h1> <p>While setting up Home Assistant, the need rose to have an own multi media management platform, mainly for music, but also for some movies. So I added another docker container to run Plex and scan a folder with media files. Different plugins exist to integrate <a href="https://www.plex.tv/media-server-downloads/#plex-media-server" target="_blank">Plex</a> with Home Assistant and the Plex Assistant looks promising, but is not yet setup (currently still missing full Dutch music support).</p> <p>Below service is used to cast Plex to ChromeCast script in home assistant <font face="Courier New">scripts.yaml</font>, the <font face="Courier New">input_select.media_player</font> helper is used to allow a drop-down choice of the ChromeCast device to cast too. Within the helper variable <font face="Courier New">input_select.PlexPlaylist</font> all playlists defined in Plex are listed so you can choose which playlist should be started when casting, a random song will be selected from this playlist:</p> <blockquote> <p><font face="Courier New">play_plex_media_selection: <br /> alias: Play Plex media selection <br /> sequence: <br /> - service: media_player.play_media <br /> data_template: <br /> entity_id: media_player.{{ states('input_select.media_player') }} <br /> media_content_id: 'plex://{ "playlist_name": "{{states(''input_select.PlexPlaylist'')}}", <br /> "shuffle": "1" }' <br /> media_content_type: PLAYLIST <br /> mode: single <br /> icon: mdi:play-network</font></p> </blockquote> <p>To easily search a song on YouTube an cast it, I created a REST sensor based on YouTube API. Full details described in <a href="https://community.home-assistant.io/t/get-alerted-when-someone-subscribes-to-your-youtube-channel-or-likes-a-video/324999/33?u=myt" target="_blank">HA Community post</a>.</p> <p>In order to easily download much from YouTube, I also added a <a href="https://hub.docker.com/r/alexta69/metube" target="_blank">docker container MeTube with YouTube-DL yt_dlp including API</a>. This was integrated with Home Assistant via REST API in order to easily allow to download any YouTube music video to mp3. </p><p>By setting the YTDL_OPTIONS <span style="font-family: courier;">updatetime</span> to <span style="font-family: courier;">false</span>, this will result in the same behavior as passing the command line option <span style="font-family: courier;">--no-mtime</span> to <span style="font-family: courier;">yt_dlp</span>. This will make sure the timestamp of the downloaded file will be set to current time of download (now). Else the local downloaded file will receive the timestamp of the youtube upload timestamp provided in the youtube headers.</p> <blockquote> <p><font face="Courier New"></font></p> </blockquote> <font face="Courier New">docker-compose.yaml</font> used for this <a href="https://hub.docker.com/r/alexta69/metube" target="_blank">YouTube-DLP MeTube</a>: <blockquote> <p><span style="font-family: Courier New;"> metube:</span></p><p><span style="font-family: Courier New;"> # https://github.com/alexta69/metube</span></p><p><span style="font-family: Courier New;"> container_name: metube</span></p><p><span style="font-family: Courier New;"> image: "alexta69/metube"</span></p><p><span style="font-family: Courier New;"> restart: unless-stopped</span></p><p><span style="font-family: Courier New;"> network_mode: host</span></p><p><span style="font-family: Courier New;"> #ports:</span></p><p><span style="font-family: Courier New;"> # - '8081:8081'</span></p><p><span style="font-family: Courier New;"> volumes:</span></p><p><span style="font-family: Courier New;"> - /home/plex/media:/mp3</span></p><p><span style="font-family: Courier New;"> - /home/plex/movies:/video</span></p><p><span style="font-family: Courier New;"> environment:</span></p><p><span style="font-family: Courier New;"> - TZ=Europe/Brussels</span></p><p><span style="font-family: Courier New;"> - UID=1000</span></p><p><span style="font-family: Courier New;"> - GID=1000</span></p><p><span style="font-family: Courier New;"> - 'YTDL_OPTIONS={"updatetime": false}'</span></p><p><span style="font-family: Courier New;"> - DOWNLOAD_DIR=/video</span></p><p><span style="font-family: Courier New;"> - AUDIO_DOWNLOAD_DIR=/mp3</span></p><p><span style="font-family: Courier New;"> - OUTPUT_TEMPLATE=%(title)s.%(ext)s</span></p> </blockquote> <p>Rest command integration to allow REST http request from Home Assistant towards YouTube-DL within home assistant <font face="Courier New">configuration.yml</font>:</p> <blockquote> <p><font face="Courier New">rest_command: </font></p> <p><font face="Courier New"> youtubedl: <br /> url: '</font><font face="Courier New">http://localhost:8081/add'</font> <br /><font face="Courier New"> method: POST <br /> payload: '{"format":"mp3", "quality":"best", "url":"{{ youtube_url }}"}' <br /> content_type: 'application/json; charset=utf-8'</font></p> </blockquote> <p>Script to call YouTube-DL via REST API:</p> <blockquote> <p><font face="Courier New">download_youtube_mp3: <br /> alias: Download YouTube MP3 <br /> sequence: <br /> - service: rest_command.youtubedl <br /> data: <br /> youtube_url: '{{ states(''input_text.youtube_url'') }}'</font></p> <p><font face="Courier New">search_download_youtube_mp3: <br /> alias: Search & Download YouTube MP3 <br /> sequence: <br /> - service: rest_command.youtubedl <br /> data_template: <br /> youtube_url: ytsearch:{{ states('input_text.search_mp3_youtube') }}</font></p> <p><font face="Courier New">download_mp3_selection: <br /> alias: Download MP3 selection based on selected media player playing artist and song <br /> sequence: <br /> - service: rest_command.youtubedl <br /> data_template: <br /> youtube_url: ytsearch:{{state_attr(('media_player.' ~ states("input_select.media_player")), "media_artist")}} {{state_attr(('media_player.' ~ states("input_select.media_player")), "media_title")}}</font></p> </blockquote> <p>Once this is integrated, a webhook can also be defined within Home Assistant to allow a http call from other applications to pass through Home Assistant. Within Home Assistant <font face="Courier New">automation.yaml</font><font face="Trebuchet MS">. This allows for example to call your Home Assistant public dns with http query such as: <a href="https://homeassistant-public-dns/api/webhook/ytsearch?search=Faithless Insomina" target="_blank">https://homeassistant-public-dns/api/webhook/ytsearch?search=Faithless Insomina</a>:</font></p> <blockquote> <p><font face="Courier New">- id: 'XXXX' <br /> alias: YouTube search and download webhook <br /> description: '' <br /> trigger: <br /> - platform: webhook <br /> webhook_id: ytsearch <br /> condition: [] <br /> action: <br /> - service: input_text.set_value <br /> data: <br /> value: '{{ trigger.query.search }}' <br /> entity_id: input_text.search_mp3_youtube <br /> - service: script.search_download_youtube_mp3 <br /> data: {} <br /> mode: single</font></p> </blockquote> <p>Having such a webhook, allowed me to define an iOS shortcut that would use Shazam and once the music has been identified, call the HA Webhook to immediately download the MP3 of the best YouTube video match. I have this iOS shortcut linked to a <a href="https://support.apple.com/guide/iphone/back-tap-iphaa57e7885/ios" target="_blank">double or tripple tap on the back of my iPhone</a> to very easily scan and download any song I hear without any further manual action.</p> <ul> <li>Shazam it</li> <li>Replace ‘&’ with ‘ ‘ in Shazam Media</li> <li>Get contents of url (webhook + shazam query input) with HTTP method POST</li> </ul> <p>Further integration and optimizations with Plex have been shared in HA Community forum:</p><p></p><ul style="text-align: left;"><li><a href="https://community.home-assistant.io/t/rating-plex-song-from-within-home-assistant/605967" target="_blank">Rating Plex song from within Home Assistant</a> </li><li><a href="https://community.home-assistant.io/t/use-group-with-media-players-or-media-player-group/390277/5" target="_blank">Increasing volume of media player</a></li></ul><p></p><h2 style="text-align: left;">Apple Watch and CarPlay integration with Home Assistant</h2><div>When using Apple Watch and CarPlay, iOS actions can be defined to easily trigger a Home Assistant automation and scripts.</div><div>Yet, iOS actions don't contain actual status information of your Home Assistant. In order to overcome this and further extend the action with related actions, actionable notification are very powerfull.</div><div>See information I wrote on this in <a href="https://community.home-assistant.io/t/carplay-support/240150/87?u=myt" target="_blank">the HA Forum</a> and <a href="https://community.home-assistant.io/t/do-more-with-your-apple-watch-with-actionable-notifications-eg-control-covers/614385/2?u=myt" target="_blank">Apple Watch specific optimisations</a>.</div><div>To make the Apple Watch very powerfull, I defined several action such as 'Alarm Status', 'Media Status', 'Light Status', 'Heating Status'. These actions will just trigger an actionable notification to be returned immediately. And these actionable notifications contain actual HA status information and the actions linked to it can be defined dynamically and with actual status information.</div><div><br /></div><div><br /></div><div>Update: 18/01/2024: Apple Watch and CarPlay information added.</div><p>Update: 09/01/2022 taking into account some last HA updates such as much improved Tuya support and Material Design icons. </p> <p>Update: 21/02/2023: added info on Spotify and my own custom integrations</p><p>Update: 10/07/2023: updated info on MeTube instead of YoutubeDL container</p><p>Update: 13/12/2023: added extra blogpost links </p>Unknownnoreply@blogger.com0België50.503887 4.46993622.193653163821153 -30.686314 78.814120836178844 39.626186tag:blogger.com,1999:blog-4275131957855627760.post-78766373416317015212021-05-10T11:47:00.001+02:002021-05-28T18:49:49.503+02:00On screen numeric touch keyboard – AutoHotKey<a href="https://drive.google.com/uc?id=1bsG80PD0Z6wopkfAijjBGbl3Xf3kUERX"><img title="OnScreenKeyboardNumeric" style="margin: 0px 0px 5px; float: right; display: inline; background-image: none;" border="0" alt="OnScreenKeyboardNumeric" src="https://drive.google.com/uc?id=1WDL6v0ErZzGwppcrX_WD0kL2j0xrp-Bs" width="174" align="right" height="244" /></a> <p>Many new small 12” or 14” laptops come with a touch screen in combination with a small keyboard, missing the numeric input keys. When using azerty keyboards, the numeric keys need to hold shift key, which resulted in some annoyance when often needing to enter Windows credentials with Windows Hello PIN. Off course, Windows 10 allows you to open an on screen keyboard (OSK) and even has a nice keyboard shortcut for it: Win + Ctrl + O, but I preferred to use a small numeric touchscreen keypad entry so I created one using <a href="https://www.autohotkey.com/" target="_blank">AutoHotKey</a>.</p> <p>The <a href="https://raw.githubusercontent.com/myTselection/blogspot/master/OnScreenKeyboardNumeric.ahk" target="_blank">AHK script</a> can easily be converted into and <a href="https://raw.githubusercontent.com/myTselection/blogspot/master/OnScreenKeyboardNumeric.exe" target="_blank">.exe</a> executable using <a href="https://www.autohotkey.com/docs/Scripts.htm#ahk2exe" target="_blank">Ahk2Exe</a> that can be added into the Windows start menu to launch at Windows startup: just add a shortcut to this .exe into folder “%appdata%\Microsoft\Windows\Start Menu\Programs\Startup”.</p> <p>The script will monitor all windows and whenever a windows having the text “Windows Security “ would pop up, it will activate the on screen numeric keyboard on top and allow you to easily enter the numbers, press Enter or press backspace (<). </p> <a href="https://drive.google.com/uc?id=1bsG80PD0Z6wopkfAijjBGbl3Xf3kUERX"> <br /></a> <p><a href="https://drive.google.com/uc?id=1bsG80PD0Z6wopkfAijjBGbl3Xf3kUERX"> <br /></a></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-51958120322991014632020-03-29T13:56:00.001+02:002020-06-17T16:57:48.417+02:00Use Windows Spotlight logon backgrounds a desktop background slideshowSince I liked the Windows Spotlight automatically changing backgrounds, I wanted to use the same as desktop background. Windows allows to select a folder with pictures to use a slideshow background. Yet, it seemed the Windows Spotlight backgrounds aren’t stored as standard pictures. <br />
<a href="https://www.blogger.com/$Windows%20desktop%20wallpaper%20settings[5].png" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a>After some research, I found out the Windows login Spotlight backgrounds are saved in the folder: <span style="font-family: "courier new";">%LOCALAPPDATA%\Packages\Microsoft.Windows.ContentDeliveryManager_cw5n1h2txyewy\LocalState\Assets\</span>. But each background is stored twice, one vertical and one horitzontal picture, I’d only need the horizontal for desktop backgrounds.<br />
I created the <a href="https://github.com/myTselection/blogspot/blob/master/spotlight.ps1" target="_blank">PowerShell script</a> below to copy the horizontal pictures from the Spotlight folder and copy into a Spotlight folder within the users Picture folder. Next, the desktop wallpaper settings can be pointed to this %userprofile%\Pictures\Spotlight folder to use as filled slidedshow.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJG1lzzlqdNDyBgW2MOmQQkFvix48WWCyL8FROclMKfOAY30vFkMCx8lSSf5oGCWmWl97XOSK50ysnigtu4jeUarJ8hI8KNPvJyh3hkQoXZAkTlttKVm-b0KGnP6mJIunbp9B_WmGCd9c/s1600/Windows+desktop+wallpaper+settings.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="822" data-original-width="1600" height="328" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJG1lzzlqdNDyBgW2MOmQQkFvix48WWCyL8FROclMKfOAY30vFkMCx8lSSf5oGCWmWl97XOSK50ysnigtu4jeUarJ8hI8KNPvJyh3hkQoXZAkTlttKVm-b0KGnP6mJIunbp9B_WmGCd9c/s640/Windows+desktop+wallpaper+settings.png" width="640" /></a></div>
<br />
<br />
<pre class="brush: vb">Function Get-ImageDetails2
{
begin{
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") |Out-Null
}
process{
$fi=[System.IO.FileInfo]$_
if( $fi.Exists){
$img = [System.Drawing.Image]::FromFile($_)
$img.Clone()
$img.Dispose()
}else{
Write-Host "File not found: $_" -fore yellow
}
}
end{}
}
Function DirectoryToCreate($DirectoryToCreate) {
if (-not (Test-Path -LiteralPath $DirectoryToCreate)) {
try {
New-Item -Path $DirectoryToCreate -ItemType Directory -ErrorAction Stop | Out-Null #-Force
}
catch {
Write-Error -Message "Unable to create directory '$DirectoryToCreate'. Error was: $_" -ErrorAction Stop
}
"Successfully created directory '$DirectoryToCreate'."
}
else {
<#"Directory $DirectoryToCreate already existed"#>
}
}
DirectoryToCreate("$env:USERPROFILE\Pictures\Spotlight\")
Function Get-ImageDetails
{
begin{
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") |Out-Null
}
process{
$file=[System.IO.FileInfo]$_
if( $file.Exists){
$fs = New-Object System.IO.FileStream ($file.FullName, [IO.FileMode]::Open, [IO.FileAccess]::Read, [IO.FileShare]::Read)
$img=[System.Drawing.Image]::FromStream($fs)
$fs.Dispose()
$img | Add-Member `
-MemberType NoteProperty `
-Name Filename `
-Value $file.Fullname `
-PassThru
}
}
end{}
}
dir "$env:LOCALAPPDATA\Packages\Microsoft.Windows.ContentDeliveryManager_cw5n1h2txyewy\LocalState\Assets\" -Recurse | % {
$image = $_
$imagePath = $image.PSPath
$imageName = $image.Name
$imageDetails = $image| Get-ImageDetails
$imHeight = $imageDetails.Height
$imWidth = $imageDetails.Width
if (($imHeight -le $imWidth ) -and ($imWidth -gt 1000)) {
copy-item -path "$imagePath" -destination "$env:USERPROFILE\Pictures\Spotlight\$imageName.jpg"
}
}</pre>
Using a Windows Scheduled task, the script can be run after every login. In order to make sure no PowerShell command window is shown for each execution, I created the little <a href="https://github.com/myTselection/blogspot/blob/master/spotlight.vbs" target="_blank">.vbs script</a> to run the PowerShell in a hidden mode.<br />
<pre class="brush: vb">Set objShell = CreateObject("WScript.Shell")
objShell.Run "CMD /C START /B " & objShell.ExpandEnvironmentStrings("%SystemRoot%") & "\System32\WindowsPowerShell\v1.0\powershell.exe -windowstyle hidden -file " & "spotlight.ps1", 0, False
Set objShell = Nothing</pre>
This <a href="https://github.com/myTselection/blogspot/blob/master/spotlight.vbs" target="_blank">.vbs file</a> can be launched from the Windows Task Scheduler:<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://www.blogger.com/$task%20scheduler%20action[5].png" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://www.blogger.com/$task%20scheduler%20trigger[6].png" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9becADc6YilgJOLpuaHDl9__hI-ugNM42LmXuZ9-j1jci8JkN3uu9e54bOUjqmxXf9NVwxWqlJFd8mZZER-YTAXT-AIow20T0xLDaB2NRiB8QtxC-HjK9ysGGTXTqA9S2TcRGr8FPZ48/s1600/task+scheduler+action.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="645" data-original-width="657" height="313" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9becADc6YilgJOLpuaHDl9__hI-ugNM42LmXuZ9-j1jci8JkN3uu9e54bOUjqmxXf9NVwxWqlJFd8mZZER-YTAXT-AIow20T0xLDaB2NRiB8QtxC-HjK9ysGGTXTqA9S2TcRGr8FPZ48/s320/task+scheduler+action.png" width="320" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigbAdw1aNYQZvsDiWp8w9eRfN7Ydhjo47msQ_jEpS6HCJ3vaIEOLVCgCTLroly_THfORTal5-KlavhG29i1W_YrGq5xEzFAYf8yDv7gPl9EWUNepE1nYa01N5NXhjtrLB8LJRNM2wtgs0/s1600/task+scheduler+trigger.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="769" data-original-width="1009" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigbAdw1aNYQZvsDiWp8w9eRfN7Ydhjo47msQ_jEpS6HCJ3vaIEOLVCgCTLroly_THfORTal5-KlavhG29i1W_YrGq5xEzFAYf8yDv7gPl9EWUNepE1nYa01N5NXhjtrLB8LJRNM2wtgs0/s320/task+scheduler+trigger.png" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-10994838686186051112019-11-07T22:30:00.001+01:002019-11-07T22:59:08.594+01:00Quoridor boardgame on paper<p>If you like the <a href="https://boardgamegeek.com/boardgame/624/quoridor" target="_blank">boardgame Quoridor</a>, you may use <a href="https://github.com/myTselection/blogspot/raw/master/Quoridor%20v6.pdf" target="_blank">this PDF</a> to print it and easily have the game in a very portable way: as in a single sheet of paper. One paper allows you to play the game twice (both sides printed) with 2 or 4 players. <img style="margin: 0px 0px 5px; border: 0px currentcolor; border-image: none; float: right; display: inline; background-image: none;" border="0" src="https://cf.geekdo-images.com/itemrep/img/Fq5LoNt40LfuPXGQfeTMZ3_2lxA=/fit-in/246x300/pic3488232.jpg" align="right" /></p> <p>You can use the corners of the paper as the pawns. With a pen, draw the walls on the board and mark the used walls at the bottom of the board. </p> <blockquote> <p>The abstract strategy game Quoridor is surprisingly deep for its simple rules. The object of the game is to advance your pawn to the opposite edge of the board. On your turn you may either move your pawn or place a wall. You may hinder your opponent with wall placement, but not completely block him off. Meanwhile, he is trying to do the same to you. The first pawn to reach the opposite side wins.<img style="margin: 0px 0px 5px;" src="https://raw.githubusercontent.com/myTselection/blogspot/master/Quoridor%20v6.png" width="689" height="487" /></p></blockquote>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-26341366087416708812019-11-07T22:17:00.001+01:002020-03-29T13:17:22.204+02:00Download files from urls in Excel sheet<p>If you’d need to download several files based on some concatenated url’s it can be useful to build the url’s with Excel.</p> <p>This <a href="https://github.com/myTselection/blogspot/raw/master/DownloadFiles.xlsm" target="_blank">little macro</a> can then be used to download all the url’s found on the active sheet and store the files in a folder. </p> <p>The target folder where the files need to be stored will be requested upon running the macro.</p> <p>Based on some <a href="https://www.mrexcel.com/forum/excel-questions/353006-download-file-excel.html" target="_blank">example VBA</a> found online. </p> <pre class="brush: vb">Sub DownloadUrlsOnSheet()
Dim iRow, iColumn As Long
Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim WHTTP As Object
On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0
targetDir = BrowseForFolder
If (targetDir = False) Then
Exit Sub
End If
If Dir(targetDir, vbDirectory) = Empty Then MkDir targetDir
Set rngRange = Nothing
Set rngRange = Worksheets(ActiveCell.Worksheet.Name).Cells.Find("*", Worksheets(ActiveCell.Worksheet.Name).Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious)
If Not rngRange Is Nothing Then
'if found then assign last non-empty cell row and colum index to the variable
lngMaxColumIndex = rngRange.Column
lngMaxRowIndex = rngRange.Row
Else
MsgBox ("Error clearing data, please check logs")
Exit Sub
End If
For iRow = 1 To lngMaxRowIndex
For iColumn = 1 To lngMaxColumIndex
currValue = Trim(Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Text)
If CheckURL(currValue) Then
TempFile = Right(currValue, InStr(1, StrReverse(currValue), "/") - 1)
WHTTP.Open "GET", currValue, False
WHTTP.Send
FileData = WHTTP.ResponseBody
FileNum = FreeFile
Open targetDir & "\" & TempFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
FileNum = FreeFile
Open targetDir & "\LogFile.txt" For Append As #FileNum
Print #FileNum, currValue & " --- Dowmloaded ----"
Close #FileNum
Else
FileNum = FreeFile
Open targetDir & "\LogFile.txt" For Append As #FileNum
Print #FileNum, currValue & " !!! File Not Found !!!"
Close #FileNum
End If
Next
Next
Set WHTTP = Nothing
MsgBox "Open the folder " & targetDir & " for the downloaded files..."
Shell "C:\WINDOWS\explorer.exe """ & targetDir & "", vbNormalFocus
End Sub Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
'Destroy the Shell Application
Set ShellApp = Nothing
'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function
'
Function CheckURL(URL) As Boolean
Dim W As Object
On Error Resume Next
Set W = CreateObject("winhttp.winhttprequest.5")
If Err.Number <> 0 Then
Set W = CreateObject("winhttp.winhttprequest.5.1")
End If
On Error GoTo 0
If (Len(URL) < 4 Or Left(URL, 4) <> "http") Then
CheckURL = False
Exit Function
End If
On Error Resume Next
W.Open "HEAD", URL, False
W.Send
If W.Status = 200 Then
CheckURL = True
Else
CheckURL = False
End If
End Function</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-49101393549962422312018-03-15T22:09:00.001+01:002018-03-15T22:13:38.251+01:00Create and/or update Outlook contacts from Excel<p align="justify">When you have a good overview of all your contact details in an Excel file, it can be useful to export<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOWtOtyM3BZndN4IOwIYVtVp8hGa2_-akaUP9l_KRiJHYCbiHwQPiY3lMAaZoyTHtusUQparNovacR9gJ8TfS9DrMA-BVRsos09SAONs8VkEh_W1I9ZqQaQ7p-qNPsonkxH3i7Vn0pek0/s1600-h/Excel_20073"><img title="Excel_2007" style="margin: 2px 0px 2px 5px; float: right; display: inline; background-image: none;" border="0" alt="Excel_2007" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiiHdSu2Ytk1LAyrVlesZRy8fnuxLN_MSKv31CymCmN0B-34kaZKTsZvF4WvL6zETO8SJA0prQi4tJEtxQGfMw__hc7eomDlA7b3j7dPOdj2n4RQCOTj1b7FY7z7N3Uzn_HENSMWG3t5w/?imgmax=800" width="143" align="right" height="119" /></a> these towards Outlook (eg to allow automatic synchronization towards your mobile). When including contact pictures, the look and feel of Outlook and your mobile increases a lot by immediately showing the related pictures when receiving a mail or call.</p> <p align="justify">Since the contact details can change over time, I created the Excel macro below to update existing contacts and create the non existing contact within Outlook, including all available details of the contact.</p> <p align="justify">Using a ‘<font face="Courier New">Reference</font>’ sheet, in which the columns are references using a <em>named cell</em>, it’s easy to retrieve the contact details from the right column, while still being able to easily change the data or order. The data is retrieved using the <font face="Courier New">Cells.Range</font> option, eg: <font face="Courier New">Sheets(sheetName).Cells(i, Sheets("References").Range("columnname").Value)</font></p> <p align="justify">In order to make sure the same contact is not added multiple times, the contact is first retrieved from the available Outlook contacts using the <font face="Courier New">olFolder.Item.Restrict(…filter…)</font> option based on the email address of the contact.</p> <p align="justify">Since I load the contact details from a DB using a linked Query within Excel, I also refresh the query data before performing the migration from Excel to Outlook.</p> <p align="justify">All details can be found in the macro source below and the <a href="https://www.dropbox.com/s/zfj833vzlvvx6sd/ContactDetails.xlsm?dl=0" target="_blank">example Excel file (including the macro)</a>.</p> <pre class="brush: vb">'Developed by myT, http://myTselection.blogspot.com
Option Explicit
Public Sub RefreshQueries()
Dim wks As Worksheet
Dim qt As QueryTable
Dim lo As ListObject
For Each wks In Worksheets
For Each qt In wks.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
For Each lo In wks.ListObjects
lo.QueryTable.Refresh BackgroundQuery:=False
Next lo
Next wks
Set qt = Nothing
Set wks = Nothing
End Sub
'http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=167:import-contacts-from-excel-to-outlook-automate-in-vba&catid=79&Itemid=475
Sub ExcelWorksheetDataAddToOutlookContacts()
'Automating Outlook from Excel: This example uses the Items.Add Method to export data from an Excel Worksheet to the default Contacts folder.
'Automate Outlook from Excel, using Late Binding. You need not add a reference to the Outlook library in Excel (your host application), in this case you will not be able to use the Outlook's predefined constants and will need to replace them by their numerical values in your code.
Dim oApplOutlook As Object
Dim oNsOutlook As Object
Dim oCFolder As Object
Dim oDelFolder As Object
Dim oCItem As Object
'Dim olItems As Outlook.Items
Dim olItems As Object
'Dim olContactItem As contactItem
Dim olContactItem As Object
Dim oDelItems As Object
Dim lLastRow As Long, i As Long, n As Long, c As Long
Dim firstRowToProcess As Integer, emailColumn As Integer, pictureColumn As Integer, processedColumn As Integer, itemsFound As Integer
Dim sheetName As String, fullFilePath As String
Dim updateExistingContacts As Boolean
RefreshQueries
'Config:
sheetName = "Contacts"
firstRowToProcess = 2
updateExistingContacts = False
updateExistingContacts = MsgBox("Update and overwrite fields of existing contacts? Pictures will always be updated. (Existing notes will be appended, not overwritten)", vbYesNo, "Overwrite")
Application.ScreenUpdating = False
' turns off screen updating
Application.DisplayStatusBar = True
' makes sure that the statusbar is visible
Application.StatusBar = "Preparing export contacts to Outlook"
'determine last data row in the worksheet:
lLastRow = Sheets(sheetName).Cells(Rows.Count, "A").End(xlUp).Row
'Create a new instance of the Outlook application, if an existing Outlook object is not available.
'Set the Application object as follows:
On Error Resume Next
Set oApplOutlook = GetObject(, "Outlook.Application")
'if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set oApplOutlook = CreateObject("Outlook.Application")
End If
'disable error handling:
On Error GoTo 0
'use the GetNameSpace method to instantiate (ie. create an instance) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
Set oNsOutlook = oApplOutlook.GetNamespace("MAPI")
'----------------------------
'Empty the Deleted Items folder in Outlook so that when you quit the Outlook application you bypass the prompt: Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?
'set the default Deleted Items folder:
' 'The numerical value of olFolderDeletedItems is 3. The following code has replaced the Outlook's built-in constant olFolderDeletedItems by its numerical value 3.
' Set oDelFolder = oNsOutlook.GetDefaultFolder(3)
' 'set the items collection:
' Set oDelItems = oDelFolder.Items
'
' 'determine number of items in the collection:
' c = oDelItems.Count
' 'start deleting from the last item:
' For n = c To 1 Step -1
' oDelItems(n).Delete
' Next n
'
'----------------------------
'set reference to the default Contact Items folder:
'The numerical value of olFolderContacts is 10. The following code has replaced the Outlook's built-in constant olFolderContacts by its numerical value 10.
Set oCFolder = oNsOutlook.GetDefaultFolder(10)
' Set olItems = oCFolder.Items.Restrict("[MessageClass]='IPM.Contact'")
'Find contact to update, if not found add a new contact item
'post each row's data on a separate contact item form:
For i = firstRowToProcess To lLastRow
'restrict info: https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/items-restrict-method-outlook
'folder items info: https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/folder-items-property-outlook
Set olItems = oCFolder.Items.Restrict("[MessageClass]='IPM.Contact' And [Email1Address] = '" & Sheets(sheetName).Cells(i, Sheets("References").Range("email").Value) & "'")
itemsFound = 0
For Each olContactItem In olItems
Application.StatusBar = "Updating Outlook contact " + olContactItem.FullName & ", " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value)
'matching contact found in Outlook and Excel
'web pictures should be stored in a local folder
fullFilePath = Replace(Sheets(sheetName).Cells(i, Sheets("References").Range("pictureurl").Value), "http://someimageurl", "C:\Users\username\Documents\Pictures\profilephotos\")
If FileExists(fullFilePath) Then
olContactItem.AddPicture (fullFilePath)
Else
'MsgBox ("Missing picture: " + fullFilePath + ", for member: " + olContactItem.firstName + " " + olContactItem.lastname)
Debug.Print ("Missing picture: " & fullFilePath & ", for member: " & olContactItem.firstName & " " & olContactItem.lastName)
End If
If updateExistingContacts = True Then
With olContactItem
'update existing contact fields https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._contactitem_properties.aspx
.firstName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value)
.lastName = Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
If (Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value) <> "") Then
.Birthday = DateValue(Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value))
End If
.BusinessAddressStreet = "BusinessStreet 100"
.BusinessAddressCity = "BusinessCity"
.BusinessAddressCountry = "BusinessCountry"
.BusinessAddressPostalCode = "BusinessPostal"
.BusinessHomePage = "http://www.company.com"
'do not remove or duplicate existing categories, set some desired category when not yet set
If (InStr(.Categories, "Business") = 0) Then
.Categories = .Categories & ",Business"
End If
.CompanyName = "Company"
.Email1DisplayName = Sheets(sheetName).Cells(i, Sheets("References").Range("email").Value)
.FullName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
.FileAs = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
'.gender = Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value)
' If (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "M") Then
' .gender = Microsoft.Office.Interop.Outlook.OlGender.olMale
' ElseIf (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "F") Then
' .gender = Microsoft.Office.Interop.Outlook.OlGender.olFemale
' Else
' .gender = Microsoft.Office.Interop.Outlook.OlGender.olUnspecified
' End If
.HomeAddressCity = Sheets(sheetName).Cells(i, Sheets("References").Range("city").Value)
.HomeAddressCountry = "DefaultCountry"
.HomeAddressPostalCode = Sheets(sheetName).Cells(i, Sheets("References").Range("postcode").Value)
.HomeAddressStreet = Sheets(sheetName).Cells(i, Sheets("References").Range("street").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("street_number").Value)
.JobTitle = Sheets(sheetName).Cells(i, Sheets("References").Range("current_function").Value)
'.Language = Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value)
.MobileTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("mobile").Value)
.BusinessTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("phone").Value)
.BusinessFaxNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("fax").Value)
.Department = Sheets(sheetName).Cells(i, Sheets("References").Range("division").Value)
.ManagerName = Sheets(sheetName).Cells(i, Sheets("References").Range("manager").Value)
.WebPage = "http://www.company.com"
If (InStr(.body, "Staff number: ") = 0) Then
.body = "Staff number: " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value) & vbCrLf
.body = .body & "Recruitment date: " & Sheets(sheetName).Cells(i, Sheets("References").Range("recruitment_date").Value) & vbCrLf
.body = .body & "Education: " & Sheets(sheetName).Cells(i, Sheets("References").Range("educations").Value) & vbCrLf
.body = .body & "Languages: " & Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value) & vbCrLf
.body = .body & "Specialty skills: " & Sheets(sheetName).Cells(i, Sheets("References").Range("specialty_skills").Value) & vbCrLf
' Else
' keeps original body note, but duplicates all data if run mulitple times
' originalBody = .body
' .body = "Staff number: " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value) & vbCrLf
' .body = .body & "Recruitment date: " & Sheets(sheetName).Cells(i, Sheets("References").Range("recruitment_date").Value) & vbCrLf
' .body = .body & "Education: " & Sheets(sheetName).Cells(i, Sheets("References").Range("educations").Value) & vbCrLf
' .body = .body & "Languages: " & Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value) & vbCrLf
' .body = .body & "Specialty skills: " & Sheets(sheetName).Cells(i, Sheets("References").Range("specialty_skills").Value) & vbCrLf
' .body = .body & vbCrLf & originalBody
End If
End With
End If
Sheets(sheetName).Cells(i, Sheets("References").Range("processed").Value).Value = "OK"
olContactItem.Save
itemsFound = itemsFound + 1
Next
If itemsFound = 0 Then
Application.StatusBar = "Adding Outlook contact " & Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value) & ", " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value)
'Using the Items.Add Method to create a new Outlook contact item in the default Contacts folder.
Set oCItem = oCFolder.Items.Add
'display the new contact item form:
'oCItem.Display
'set properties of the new contact item:
With oCItem
'update existing contact fields https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._contactitem_properties.aspx
.firstName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value)
.lastName = Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
If (Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value) <> "") Then
.Birthday = DateValue(Sheets(sheetName).Cells(i, Sheets("References").Range("birthdate").Value))
End If
.BusinessAddressStreet = "BusinessStreet 100"
.BusinessAddressCity = "BusinessCity"
.BusinessAddressCountry = "BusinessCountry"
.BusinessAddressPostalCode = "BusinessPostal"
.BusinessHomePage = "http://www.company.com"
'do not remove or duplicate existing categories, set some desired category when not yet set
If (InStr(.Categories, "Business") = 0) Then
.Categories = .Categories & ",Business"
End If
.CompanyName = "Company"
.Email1DisplayName = Sheets(sheetName).Cells(i, Sheets("References").Range("email").Value)
.FullName = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
.FileAs = Sheets(sheetName).Cells(i, Sheets("References").Range("first_name").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("last_name").Value)
'.gender = Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value)
' If (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "M") Then
' .gender = Microsoft.Office.Interop.Outlook.OlGender.olMale
' ElseIf (Sheets(sheetName).Cells(i, Sheets("References").Range("gender").Value) = "F") Then
' .gender = Microsoft.Office.Interop.Outlook.OlGender.olFemale
' Else
' .gender = Microsoft.Office.Interop.Outlook.OlGender.olUnspecified
' End If
.HomeAddressCity = Sheets(sheetName).Cells(i, Sheets("References").Range("city").Value)
.HomeAddressCountry = "DefaultCountry"
.HomeAddressPostalCode = Sheets(sheetName).Cells(i, Sheets("References").Range("postcode").Value)
.HomeAddressStreet = Sheets(sheetName).Cells(i, Sheets("References").Range("street").Value) & " " & Sheets(sheetName).Cells(i, Sheets("References").Range("street_number").Value)
.JobTitle = Sheets(sheetName).Cells(i, Sheets("References").Range("current_function").Value)
'.Language = Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value)
.MobileTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("mobile").Value)
.BusinessTelephoneNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("phone").Value)
.BusinessFaxNumber = Sheets(sheetName).Cells(i, Sheets("References").Range("fax").Value)
.Department = Sheets(sheetName).Cells(i, Sheets("References").Range("division").Value)
.ManagerName = Sheets(sheetName).Cells(i, Sheets("References").Range("manager").Value)
.WebPage = "http://www.company.com"
If (InStr(.body, "Staff number: ") = 0) Then
.body = "Staff number: " & Sheets(sheetName).Cells(i, Sheets("References").Range("pnr").Value) & vbCrLf
.body = .body & "Recruitment date: " & Sheets(sheetName).Cells(i, Sheets("References").Range("recruitment_date").Value) & vbCrLf
.body = .body & "Education: " & Sheets(sheetName).Cells(i, Sheets("References").Range("educations").Value) & vbCrLf
.body = .body & "Languages: " & Sheets(sheetName).Cells(i, Sheets("References").Range("speaking_language_info_list").Value) & vbCrLf
.body = .body & "Specialty skills: " & Sheets(sheetName).Cells(i, Sheets("References").Range("specialty_skills").Value) & vbCrLf
End If
End With
'mark as done
Sheets(sheetName).Cells(i, Sheets("References").Range("processed").Value).Value = "OK"
fullFilePath = Replace(Sheets(sheetName).Cells(i, Sheets("References").Range("pictureurl").Value), "http://someimageurl", "C:\Users\username\Documents\Pictures\profilephotos\")
If FileExists(fullFilePath) Then
oCItem.AddPicture (fullFilePath)
Else
'MsgBox ("Missing picture: " + fullFilePath + ", for member: " + oCItem.firstName + " " + oCItem.lastname)
Debug.Print ("Missing picture: " & fullFilePath + ", for member: " & oCItem.firstName & " " & oCItem.lastName)
End If
'close the new contact item form after saving:
'The numerical value of olSave is 0. The following code has replaced the Outlook's built-in constant olSave by its numerical value 0.
oCItem.Close 0
End If
Next i
'quit the Oulook application:
oApplOutlook.Quit
Application.StatusBar = "Outlook " & lLastRow & " contacts exported"
Application.ScreenUpdating = True
'clear the variables:
Set oApplOutlook = Nothing
Set oNsOutlook = Nothing
Set oCFolder = Nothing
Set oDelFolder = Nothing
Set oCItem = Nothing
Set oDelItems = Nothing
Set olContactItem = Nothing
Set olItems = Nothing
MsgBox "Successfully Exported Worksheet Data to the Default Outlook Contacts Folder."
End Sub
Function FileExists(fullFileName As String) As Boolean
FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
End Function
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-34012940839280128042018-02-21T00:48:00.001+01:002021-05-12T16:03:06.171+02:00If This Than That – Cloud service orchestration<blockquote> <div align="justify"><a href="https://ifttt.com/" target="_blank">IFTTT</a> and other (<a href="https://flow.microsoft.com/" target="_blank">Microsoft Flow</a>, <a href="https://zapier.com/" target="_blank">Zapier</a>, <a href="https://www.integromat.com/en" target="_blank">Integromat</a>) easily enable anyone to combine many cloud s<img title="IFTTT" style="margin: 0px 0px 5px; border: 0px currentcolor; float: right; display: inline; background-image: none;" border="0" alt="IFTTT" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGtOnZUaP69-A7pcP_xw3HHARZYeKZYQyUeCvKRWkwIKw6B7zsPaYuPS2i5ldTU2w3q4U2b9jca_-uDf9JLR-McWWfyng3QdpURkxXcFi7ykozIhqhHPrtS_tsWAzLsJY7E5bNQzOuMM0/?imgmax=800" width="145" align="right" height="81" />ervices for task automation.</div> <div align="justify">IFTTT is free and integrates many public cloud services, but it might seem limited in the combinations that can be created with it. Nevertheless I was surprised to see some nice examples that allow smart integration.</div> <h4 align="justify">Control lights with planning:</h4> <div align="justify">I wanted to control my lights around the house, using a easy to manage planning (could be used to control central heating too). By combining the <a href="https://ifttt.com/google_calendar" target="_blank">Google Calendar</a> services and <a href="https://ifttt.com/ewelink" target="_blank">eWeLink IoT</a> services, this was very easy and cheap, since only a Sonoff wifi relay had to be bought.</div> <div align="justify">Within <a href="https://calendar.google.com/calendar" target="_blank">Google Calendar</a>, I created a new calendar ‘Light-control’ with daily repeating events every evening to turn on the lights. Giving it a nice yellow color makes it very easy to get a clear overview.</div> <div align="justify">Within IFTTT add a new applet with ‘if’ trigger <a href="https://ifttt.com/create/if-google_calendar?sid=1" target="_blank">Google Calendar</a> ‘Any event starts’. The ‘then’ trigger can be set to eWeLink to switch on your Sonoff channel. Similar, another IFTTT applet can be created triggered on ‘Any event ends’ to switch off you Sonoff channel via the eWeLink Smart Home service. </div> <div align="justify">By just updating your calendar events you can now very easily manage your IoT device planning. You can even ‘invite’ your lights to participate in a ‘meeting'. Just enable the Google Calendar option ‘Auto accept invites’ and put your calendar id (eg <a href="mailto:123456789abcdefg@group.calendar.google.com">123456789abcdefg@group.calendar.google.com</a>) as a guest for your meeting and the light will switch on and off for your planned event.</div> <h4 align="justify">Complex combinations</h4> <div align="justify">Sometime the one-on-one link that can be used for the service orchestration within IFTTT can be too limited. To enable complex combinations, <a href="http://apilio.io/" target="_blank">apilio.io</a> or <a href="https://switchur.com/" target="_blank">switchur</a> can be used.</div> <div align="justify">As an example, using <a href="https://apilio.io/" target="_blank">apilio.io</a>, I wanted to be warned if frost would be expected tomorrow, to make sure I would protect my car windshield in time. But I want to get warned when arriving at home in the evening, to make sure I get notified while still in my car, while by default the Weather Underground service would provide the Weather forecast of tomorrow, early in the mornings.</div> <div align="justify">First create you <a href="https://apilio.io/" target="_blank">apilio.io</a> account and register your <a href="https://ifttt.com/services/maker_webhooks/settings" target="_blank">IFTTT WebHooks</a> settings.</div> <div align="justify">Next, within apilio.io, I create 3 <a href="https://apilio.herokuapp.com/boolean_variables" target="_blank">boolean variables</a>:</div> <ol> <li> <div align="justify"> arriving_home</div> </li> <li> <div align="justify"> evening</div> </li> <li> <div align="justify"> frost_tomorrow</div> </li> </ol> <div align="justify">For each of these boolean variables, URL’s will be provided by apilio to set the value of the boolean to true or false using a unique webservice url.</div> <div align="justify">Next, within IFTTT I created several applets:</div> <div align="justify">1. IFTTT <a href="https://ifttt.com/create/if-location" target="_blank">Location service</a>: </div> <ul> <li> <div align="justify"> applet <strong>if</strong> ‘You enter an <u>area</u>’ <strong>then</strong> call the webhook url to set to ‘true’ the ‘arriving_home’ boolean</div> </li> <li> <div align="justify"> applet <strong>if</strong> ‘You exit an <u>area</u>’ <strong>then</strong> call the webhook url to set to ‘false’ the ‘arriving_home’ booelan</div> </li> </ul> <div align="justify">2. IFTTT <a href="https://ifttt.com/create/if-date_and_time" target="_blank">Date Time service</a>:</div> <ul> <li> <div align="justify"> applet <strong>if</strong> <u>17:00</u> (5pm) <strong>then</strong> call the webhook url to set to ‘true’ the ‘evening’ boolean</div> </li> <li> <div align="justify"> applet <strong>if</strong> 00:00 (0am) <strong>then</strong> call the webhook url to set to ‘false’ the ‘evening’ boolean</div> </li> </ul> <div align="justify">3. IFTTT <a href="https://ifttt.com/create/if-weather" target="_blank">Weather Undergrou<span style="color: rgb(34, 51, 68);">nd service</span></a>:</div> <ul> <li> <div align="justify"> applet if ‘Tomorrow’s low drops below’ <u>4°C</u> <strong>then</strong> call the webhook url to set to ‘true’ the ‘frost_tomorrow’ boolean</div> </li> <li> <div align="justify"> applet if ‘Tomorrow’s forecast calls for’ <u>snow</u> <strong>then</strong> call the webhook url to set to ‘true’ the ‘frost_tomorrow’ boolean</div> </li> <li> <div align="justify"> Since no trigger is available to set the boolean value ‘frost_tomorrow’ back on false, we will include in the apilio condition this value needs to be ‘changed’ at least within the last 24h. Weather Underground will typically provide the next day forecast at 7am.</div> </li> </ul> <div align="justify">4. IFTTT <a href="https://ifttt.com/create/if-maker_webhooks" target="_blank">Webhook service</a> (callback):</div> <ul> <li> <div align="justify"> This webhook applet will be called by apilio to trigger a desired action when all conditions are met. The name of the applet is important, as it will need to be registered within apilio in order to call the right applet. <strong>If</strong> the applet is called, <strong>then</strong> a desired action can be triggered, eg showing a notification on your mobile.</div> </li> </ul> <div align="justify"></div> <div align="justify">Again within apilio.io, I created 3 <a href="https://apilio.herokuapp.com/conditions" target="_blank">conditions</a>:</div> <ol> <li> <div align="justify"> at_home_condition –> linked to value of variable ‘arriving_home’</div> </li> <li> <div align="justify">evening_condition –> linked to value of variable ‘evening’</div> </li> <li> <div align="justify">frost_tomorrow_condition –> linked to value of variable ‘frost_tomorrow’ including the <u>timestamp restriction</u> the value has to be modified within 72000seconds. </div> </li> </ol> <div align="justify">Now, within apilio.io, a <a href="https://apilio.herokuapp.com/logicblocks" target="_blank">logic block</a> can be created, linked to the 3 conditions created above. When all conditions are met, the IFTTT webhook can be called by apilio. The name of the IFTTT webhook callback applet created before needs to be provided. Enable the ‘Automatic evaluation’ to evaluate the conditions defined in the logic block whenever any of the condition values changes.</div> <div align="justify">To test the complete setup, the different apilio URL’s to set the boolean values can be called manually to see if the resulting IFTTT webhook appled is called as soon as all conditions are met.</div> <div align="justify"></div> <div align="justify">In this example, only boolean variables have been used, which makes the link between a variable and the condition within apilio very straightforward. But whenever needed string or numeric variables can be defined as well. The conditions related to these variables can get more complex evaluations, such as containing some values or smaller, equal, etc.</div> <div align="justify"></div> <div align="justify">Feel free to comment or add extra ideas of interesting combinations that would be possible.</div> </blockquote> Update 19/12/2019: Since Apilio.io will soon become a paid only service, we're looking into moving all the logic to an alternative platform. <a href="https://sequematic.com/" target="_blank">sequematic.com</a> and <a href="https://switchur.com/" target="_blank">switchur</a> are possible options. <p>Uodate 12/05/2021: As IFTTT started to ask much more money to their connected manufacturers and even high monthly fees to their users on top, I decided to completely move away from IFTTT. Too bad for the nice community they build up, but alternatives seem to have become very mature in the mean time. I wrote a <a href="https://mytselection.blogspot.com/2021/05/home-assistant-free-or-cheap-home-iot.html">long detailed post about my move towards the excellent free Home Assistant alternative</a>.</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-35087254739328187632016-04-11T18:46:00.001+02:002018-03-15T22:17:51.333+01:00Outlook default reminder for sent mails<p>Since most of the time, I need to follow up my mails send out to make sure the expected actions are taken, I do add a reminder on my send email. So I can verify in a couple of days if I did get a reaction or the expected action has been taken in the meantime.</p> <p>In order to automate this, I added the outlook macro shown below. It’s based on the nice example provided by <a href="http://www.slipstick.com/developer/code-samples/set-flag-follow-up-using-vba/" target="_blank">Diane Poremsky</a>. I just improved it a bit to set the default reminder at 10am in 5 days (moving some days forward whenever the 5 days would end in the weekend). After sending an email, a little popup will show up indicating the reminder would be created for within 5 business days. The number of days can quickly be changed or the reminder can fully be discared using the ‘Cancel’ on the pop-up. After some days of usage, I’m very satisfied with the result. The popup will appear after sending the message, so the email will always be sent and won’t be blocked if the pop-up wouldn’t be noticed.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw3aOfI01zKy9N5hv2nIQt7paTjyaqwSCxeeD1gh45qfaA93H9GOeCVc7RDbf2fhVc7oiRFErz42wtDLulyCtmUQpe3VflO_qlFj_ubl7PCpbjH5eU8PvGHT3j4InmghKulDkuEszNkvk/s1600-h/OutlookReminderPopup%255B2%255D"><img title="OutlookReminderPopup" style="margin: 0px auto 5px; float: none; display: block; background-image: none;" border="0" alt="OutlookReminderPopup" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgniDyQczt0x1jMYRaODld8-D8H85yJcKAovXo9pgXM7K1dVTgmUxNIzKZb9eDAsNehMnpMMsTcMsvuQDGwg3n8_gZgusi-w_cU97iblhtlLm3pSwIMK-zGocTBMp82bpHKdtGSkj9CEPA/?imgmax=800" width="244" height="138" /></a></p> <p>Of course this is only useful when you often need a follow-up on your sent mails…</p> <pre class="brush: vb">'Instructions:
'myT 04/2016, <a href="http://myTselection.blogspot.com">http://myTselection.blogspot.com</a>
'Default reminder for sent mails: add a default reminder for every sent mail, default 5 working days with popup to cancel or confirm (and set number of days)
'Instructions:
'Open Outlook and press [ALT+F11] to open the VBS developer editor
'Navigate to 'Project 1 VbaProject.OTM' > Microsoft Outlook Objects > ThisOutlookSession
'Copy and Paste the macro below and close the VBS editor
'Make sure the line 'Private WithEvents olSentItems as Items' is saved at the top of the file
Private WithEvents olSentItems As Items
Attribute olSentItems.VB_VarHelpID = -1
Private Declare PtrSafe Function ShellExecute _
Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Long, _
ByVal Operation As String, _
ByVal Filename As String, _
Optional ByVal Parameters As String, _
Optional ByVal Directory As String, _
Optional ByVal WindowStyle As Long = vbMinimizedFocus _
) As Long
'Original source: <a href="http://www.slipstick.com/developer/code-samples/set-flag-follow-up-using-vba/">http://www.slipstick.com/developer/code-samples/set-flag-follow-up-using-vba/</a>
Private Sub Application_Startup()
'MsgBox ("test startup")
Dim objNS As NameSpace
Set objNS = Application.Session
' instantiate objects declared WithEvents
Set olSentItems = objNS.GetDefaultFolder(olFolderSentMail).Items
Set objNS = Nothing
End Sub
Private Sub olSentItems_ItemAdd(ByVal Item As Object)
'On Error Resume Next
Dim prompt As String
Dim dtmTemp As Date
Dim businessDays As String
Dim reminderTime As String
Dim dueDateDays As Integer
'MsgBox ("test item sent")
If TypeName(Item) = "MailItem" Then
businessDays = 5
dueDateDays = 14
reminderTime = "10:00:00 AM"
'Reminder in 5 days, 10 AM
dtmTemp = DateValue(Now + businessDays) & " " & TimeValue(reminderTime)
'skip days if reminder would result in weekend
Do While IsWeekend(dtmTemp)
dtmTemp = dtmTemp + 1
Loop
'InputBox(prompt[, title] [, default] )
businessDays = InputBox("Do you want to add a reminder for the email sent? " & vbCrLf & vbCrLf _
& " Subject: " & Item.Subject & vbCrLf _
& " To: " & Item.To & vbCrLf & vbCrLf & vbCrLf _
& "Set # business days for the reminder" & vbCrLf _
& " Default " & businessDays & " business days: " & WeekdayName(Weekday(dtmTemp), True, vbSunday) & " " & dtmTemp, _
"Add a reminder for the email sent?", businessDays)
'Exit sub if user press Cancel button or does not enter any text in the Input field.
If businessDays = vbNullString Then Exit Sub
dtmTemp = DateValue(Now + CInt(businessDays)) & " " & TimeValue(reminderTime)
'skip days if reminder would result in weekend
Do While IsWeekend(dtmTemp)
dtmTemp = dtmTemp + 1
Loop
With Item
.MarkAsTask olMarkThisWeek
' sets a due date in 14 days
.TaskDueDate = Now + dueDateDays
.ReminderSet = True
.reminderTime = dtmTemp
.Save
End With
End If
End Sub</pre>
<p>Update 27/06/2016: added check on item type to make sure no error occur when responding to an meeting request.</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-85978831919794413002015-04-23T15:14:00.001+02:002015-05-22T10:41:51.974+02:00Outlook warn when forgetting attachment<p>When an outlook message indicates an attachment is added, but the attachment is missing, the Outlook 2010 VBA macro below can warn you about this with a popup. This option became a standard option in Outlook 2013, as indicated on <a href="http://www.slipstick.com/outlook/email/attachment-missing-from-outlook-message/" target="_blank">this site</a>.</p> <p><a href="https://dl.dropboxusercontent.com/u/2328438/ThisOutlookSession.cls" target="_blank">This version of the macro</a> has been extended compared to many similar macro’s you could find online:</p> <ul> <li>It allows to configure multiple words to scan for, currently scanning for <em>attach</em>, <em>bijlage</em>, <em>bijgevoegd</em> (see inline VBA comments) </li> <li>It will only scan the new message body and the header. It will ignore the reply/forward message body content. </li> <li>It has been improved for html/rich text/plain text emails formatting </li> <li>It allows you to configure if attachments are part of your signature </li> <li>Non-mail outlook item types will not result in VBA errors (eg. when responding a meeting request) </li> </ul> <p>All feedback is welcome, since only tested on a English version of Outlook 2010.</p> <h3>Installation instructions:</h3> <ul> <li>Open Outlook and press [ALT+F11] to open the VBS developer editor </li> <li>Navigate to 'Project 1 VbaProject.OTM' > Microsoft Outlook Objects > ThisOutlookSession </li> <li>Copy and Paste the macro below and close the VBS editor or <a href="https://dl.dropboxusercontent.com/u/2328438/ThisOutlookSession.cls" target="_blank">import the ‘ThisOutlookSession.cls’ file</a></li> </ul> <h3>Source Office VBA macro script:</h3> <pre class="brush: vb">'myT 04/2015, <a href="http://myTselection.blogspot.com">http://myTselection.blogspot.com</a> <br />'Instructions: <br />'Open Outlook and press [ALT+F11] to open the VBS developer editor <br />'Navigate to 'Project 1 VbaProject.OTM' > Microsoft Outlook Objects > ThisOutlookSession <br />'Copy and Paste the macro below and close the VBS editor<br />Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean) <br />Dim m As Variant, t As Variant <br />Dim strBody As String <br />Dim intIn As Long <br />Dim intAttachCount As Integer, intStandardAttachCount As Integer, limitBody As Integer<br />On Error GoTo handleError<br />'Edit the following line if you have a signature on your email that includes images or other files. Make intStandardAttachCount equal the number of files in your signature. <br />intStandardAttachCount = 0<br />'If CheckProperty(Item, "HTMLBody") Then <br />'    strBody = LCase(Item.HTMLBody) & " " & LCase(Item.Subject) <br />'Else <br />'    strBody = LCase(Item.Body) & " " & LCase(Item.Subject) <br />'End If<br />On Error GoTo NoHtml <br />strBody = LCase(Item.HTMLBody) & " " & LCase(Item.Subject) <br />'Show Debug via View > Immediate Window (Ctrl+G) <br />'Debug.Print strBody<br />On Error GoTo handleError <br />'detect line break where previous messages (reply/forward) are starting, only scan the newly create message ignoring forwarded/replied text <br />'HTML original message splitter <br />limitBody = InStr(1, strBody, "<div style='border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm'>") <br />'RichText has HTMLBody but no border separator <br />If limitBody = 0 Then GoTo RichText <br />GoTo commonHandling<br />RichText: <br />    On Error GoTo handleError <br />    'could test on availability of 'text/rtf format' in body to make sure this is rich text case <br />    strBody = LCase(Item.Body) & " " & LCase(Item.Subject) <br />    'rich text original message splitter <br />    limitBody = InStr(1, strBody, "_____________________________________________") <br />GoTo commonHandling<br />NoHtml: <br />    On Error GoTo handleError <br />    strBody = LCase(Item.Body) & " " & LCase(Item.Subject) <br />    'detect line break where previous messages (reply/forward) are starting, only scan the newly create message ignoring forwarded/replied text <br />    'Plain text message splitter <br />    limitBody = InStr(1, strBody, "-----") <br />GoTo commonHandling <br />commonHandling: <br />    On Error GoTo handleError <br />    'Show Debug via View > Immediate Window (Ctrl+G) <br />    'Debug.Print strBody <br />    <br />    If limitBody = 0 Then limitBody = Len(strBody) <br />    <br />    <br />    intIn = InStr(1, Left(strBody, limitBody), "attach") <br />    intIn = intIn + InStr(1, Left(strBody, limitBody), "bijlage") <br />    intIn = intIn + InStr(1, Left(strBody, limitBody), "bijgevoegd") <br />    'Copy previous line to add extra keywords <br />    <br />    intAttachCount = Item.Attachments.Count <br />    <br />    If (intIn <> 0 And intAttachCount = intStandardAttachCount) Then <br />        <br />        m = MsgBox("It appears that you mean to send an attachment, " & vbCrLf & "but there is no attachment to this message." & vbCrLf & vbCrLf & "Do you still want to send without attachments?", vbQuestion + vbYesNo + vbMsgBoxSetForeground, "Missing attachment") <br />    <br />        If m = vbNo Then <br />            Cancel = True <br />        End If <br />    End If<br />handleError: <br />    If (Err.Number <> 0) Then <br />        t = MsgBox("Outlook Attachment Reminder Error " & Err.Description & ", " & Err.Number & ", " & Err.Source, vbExclamation, "Outlook Attachment Reminder Error") <br />    End If<br />End Sub</pre><br /><br /><p>Download the macro and instructions in a <a href="https://dl.dropboxusercontent.com/u/2328438/Outlook%20missing%20attachment%20warning%20macro.txt" target="_blank">TXT file</a>.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-43548913100984494802013-12-18T16:25:00.001+01:002013-12-18T16:27:19.006+01:00iTop running on Windows portable PHP/MySQL MoWeS<p>Based on <a href="http://mytselection.blogspot.com/2010/11/mowes-portable-os-commerce-22-30a.html" target="_blank">my previous topic</a> informing about the nice functionalities of <a href="http://www.moweshelp.chsoftware.net/" target="_blank">MoWeS</a> to get easily and quickly a PHP/MySQL environment, I did set up an <a href="http://sourceforge.net/projects/itop/" target="_blank">iTop</a> environment in order to discover and play with the CMDB/ITIL ticketing service desk tool.</p> <p>While doing so, I also discovered the MoWeS Mixer is not available any longer. Apparently the company Chsoftware behind this tools has stopped in 2012. But the MoWeS tool still works very well on Windows 7 x64 and new packages can easily be added by updating the <font face="Courier New">mowes.ini</font> and <font face="Courier New">packages.ini</font> files manually.</p> <p>In order to get iTop running, some other parameters had to be updated as well: extra memory allocation is to be allowed in <font face="Courier New">php.ini</font> by changing the <font face="Courier New">memory_limit</font> from <font face="Courier New">32M</font> to <font face="Courier New">64M</font>. The <font face="Courier New">php_soap.dll</font> extension needs to be loaded as well by uncommenting it in the <font face="Courier New">php.ini</font> file. For mysql, the allowed packets needed to be extended by adding a parameter <font face="Courier New">max_allowed_packet=500M</font> within the <font face="Courier New">[mysqld]</font> section of the <font face="Courier New">my.ini</font> file.</p> <p>I’ve created an archive containing <a href="https://dl.dropboxusercontent.com/u/2328438/MoWes-2.2.3%20iTop-2.0.2-1476-clean.rar" target="_blank">a clean MoWeS environment with clean iTop</a>. The iTop still needs to be configured using the web wizard upon first connection to the iTop site (localhost/itop) (mysql username: <font face="Courier New">root</font>, password: <empty>. I’ve also <a href="https://dl.dropboxusercontent.com/u/2328438/MoWes-2.2.3%20iTop-2.0.2-1476-demo.rar" target="_blank">a MoWeS environment with base demo iTop</a> installed (itop username: <font face="Courier New">admin</font>, password: <font face="Courier New">itop</font>).</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-73445931082590104992013-12-08T15:41:00.001+01:002013-12-08T15:52:31.982+01:00vCard contact export from Excel contact list<p>Recently I needed a way to easily convert a list with contact information into a vCard file, respecting the <a href="http://en.wikipedia.org/wiki/VCard" target="_blank">vCard syntax</a>, in order to import this vCard contact information into a phone.</p> <p>After some quick Google lookup I ended with <a href="http://egnatia.ee.auth.gr/~aki/dc/DesktopContacts0.8.xls" target="_blank">this nice Excel</a> created and shared by Savas Geivanidis.</p> <p>After entering all the contact information into the sheet as instructed, the export macro started executing it’s work and I ended with a vCard file as I wanted. <strong><em>But</em> </strong>the process took very long (more than 4 minutes), even if my contact list only contained 100 contacts.</p> <p>Since Savas was so kind to keep his VBS macro unprotected, I analyzed it and optimized it. The main performance issues I detected was the multiple loops and the cell deletions used in the code. A loop was set up to run over the contact data, and copy all data it into a new sheet. Next a loop was used to run through the data and delete all cell containing the value ‘no data’. Next the new sheet was saved into a text .vcf file.</p> <p>After rewriting the VBS macro I ended up with one loop, running through the data and saving the information immediately into a text file. The same list of 100 contacts now takes less than 1sec to export instead of more than 4 minutes. Some tests with 1000 contacts were positive as it took only 4sec or less to export. I ended up cleaning up the full sheet and creating a template of it to allow easy reuse. Feel free to use this <a href="https://dl.dropboxusercontent.com/u/2328438/vCardExporter1.0.xlt" target="_blank">version 1.0 of vCard Exporter</a> for any purpose.</p> <p>The optimized source code is shown below for illustration:</p> <pre class="brush: vb">Sub Create_vCard() <br /> <br />'<br /><br />' This Macro creates a vCard from an existing phonebook Excel list<br /><br />'__________________________________________________________________<br /><br />' Based on DesktopContacts v0.8 by Savas Geivanidis (savas@mycosmos.gr) egnatia.ee.auth.gr/~aki/dc/DesktopContacts0.8.xls<br /><br />' Major performance improvements by myT in vCardExporter v1.0<br /><br />'___________________________________________________________________<br /><br />    <br />    Application.ScreenUpdating = False<br /><br />    ' turns off screen updating<br /><br />    Application.DisplayStatusBar = True<br /><br />    ' makes sure that the statusbar is visible<br /><br />    <br />    'detect number of data contacts provided<br /><br />    'mainsheet = ActiveSheet.Name<br /><br />    mainsheet = "PhoneBook"<br /><br />    Range("A30000").Select<br /><br />    Selection.End(xlUp).Select<br /><br />    nod = ActiveCell.Row<br /><br />    Range("B30000").Select<br /><br />    Selection.End(xlUp).Select<br /><br />    nod1 = ActiveCell.Row<br /><br />    If nod1 > nod Then<br /><br />        nod = nod1<br /><br />    End If<br /><br />    <br />    'Check if data is provided<br /><br />    If nod <= 2 Then<br /><br />        popup = MsgBox("No contact data could be found. Fill in the contact data in the 'PhoneBook' sheet and provide at least a first or last name for each contact." & vbLf & "See the 'Information' sheet and http://myTselection.blogpsot.com for more information", vbExclamation + vbOKOnly + vbMsgBoxSetForeground, "vCard Exporter: No data found")<br /><br />        Application.ScreenUpdating = True<br /><br />        Exit Sub<br /><br />    End If<br /><br />    <br />    'request target filename<br /><br />    vcardname = Application.GetSaveAsFilename("", "vCard Files (*.vcf), *.vcf", , "Please select the name of the vCard to export")<br /><br />    If vcardname = False Then<br /><br />        Application.ScreenUpdating = True<br /><br />        Exit Sub<br /><br />    End If<br /><br />    start_time = Now()<br /><br />    Application.StatusBar = "Preparing VCARD data"<br /><br />    <br />    <br />    Set vcardFileSystemObject = CreateObject("Scripting.FileSystemObject")<br /><br />    Set vcardFile = vcardFileSystemObject.CreateTextFile(vcardname, True)<br /><br />    <br />    <br />    'copy formula for VCARD syntax to all rows with contact data<br /><br />    Range(Cells(1, 61), Cells(1, 101)).Copy<br /><br />    Range(Cells(3, 61), Cells(nod, 101)).PasteSpecial Paste:=xlPasteFormulas<br /><br />    Application.CutCopyMode = False<br /><br />    Range("A3").Select<br /><br />    <br />    Application.StatusBar = "Processing contact data..."<br /><br />    <br />    vcardFile.writeline ("")<br /><br />    For iRow = 3 To nod<br /><br />        For iColumn = 61 To 101<br /><br />            currentValue = Worksheets(mainsheet).Cells(iRow, iColumn).Value<br /><br />            Application.StatusBar = "Processing contact data " & Round(((iRow - 2) / (nod - 2)) * 100) & "%" '&" on row " & iRow & ", column " & iColumn & ", value: " & currentValue<br /><br />            If (currentValue <> "no data") Then<br /><br />                vcardFile.writeline (currentValue)<br /><br />            End If<br /><br />            If currentValue = "END:VCARD" Then<br /><br />                vcardFile.writeline ("")<br /><br />            End If<br /><br />        Next iColumn<br /><br />    Next iRow<br /><br />    vcardFile.Close<br /><br />    Application.StatusBar = "All " & nod - 2 & " contact data processed"<br /><br />    <br />    Worksheets(mainsheet).Range(Cells(2, 61), Cells(nod, 101)).ClearContents<br /><br />    Worksheets(mainsheet).Range("A3").Select<br /><br />    end_time = Now()<br /><br />    'DateDiff("s", start_time, end_time)<br /><br />    Application.StatusBar = "vCard export in " & PrintHrMinSec(DateDiff("s", start_time, end_time)) & "sec done into file: " & vcardname<br /><br />    Application.ScreenUpdating = True<br /><br />    <br />    If (nod - 2 = 1) Then<br /><br />        popup = MsgBox(nod - 2 & " contact is exported to:" & vbLf & "'" & vcardname & "'" & vbLf & vbLf & "Please send this file to your phone and run it in order to add this contact into it." & vbLf & vbLf & "Original development 'Desktop Contacts v0.8' by Savas Geivanidis (aki@egnatia.ee.auth.gr)" & vbLf & vbLf & "Major layout and performance improvements myT in 'vCard Exporter v1.0' - http://myTselection.blogpsot.com", vbInformation + vbOKOnly + vbMsgBoxSetForeground, "vCard Exporter: data exported")<br /><br />    Else<br /><br />        popup = MsgBox(nod - 2 & " contacts were exported to:" & vbLf & "'" & vcardname & "'" & vbLf & vbLf & "Please send this file to your phone and run it in order to add these contacts into it." & vbLf & vbLf & "Original development 'Desktop Contacts v0.8' by Savas Geivanidis (aki@egnatia.ee.auth.gr)" & vbLf & vbLf & "Major layout and performance improvements myT in 'vCard Exporter v1.0' - http://myTselection.blogpsot.com"", vbInformation + vbOKOnly + vbMsgBoxSetForeground, "vCard Exporter: data exported")<br /><br />    End If<br /><br />    Application.DisplayStatusBar = True<br /><br />End Sub<br /><br />'*********************** <br /> <br />'* This function calculates hours, minutes<br /><br />'* and seconds based on how many seconds<br /><br />'* are passed in and returns a nice format<br /><br />Public Function PrintHrMinSec(elap)<br /><br />  Dim hr<br /><br />  Dim min<br /><br />  Dim sec<br /><br />  Dim remainder<br /><br />  <br />  elap = Int(elap) 'Just use the INTeger portion of the variable<br /><br />  <br />  'Using "\" returns just the integer portion of a quotient<br /><br />  hr = elap \ 3600 '1 hour = 3600 seconds<br /><br />  remainder = elap - hr * 3600<br /><br />  min = remainder \ 60<br /><br />  remainder = remainder - min * 60<br /><br />  sec = remainder<br /><br />  <br />  'Prepend leading zeroes if necessary<br /><br />  If Len(sec) = 1 Then sec = "0" & sec<br /><br />  If Len(min) = 1 Then min = "0" & min<br /><br />  <br />  'Only show the Hours field if it's non-zero<br /><br />  If hr = 0 Then<br /><br />     PrintHrMinSec = min & ":" & sec<br /><br />  Else<br /><br />     PrintHrMinSec = hr & ":" & min & ":" & sec<br /><br />  End If<br /><br />  <br />End Function</pre> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4275131957855627760.post-82572262607413991212013-08-18T22:00:00.001+02:002015-10-21T10:02:50.726+02:00Google Apps Script demo – Send SMS on new important mail in GMail<p><a href="https://drive.google.com/">Google Drive</a> allows you to easily create and edit Documents, Presentations, Spreadsheets, Forms and Drawings. But not everyone might be familiar with the <a href="https://developers.google.com/apps-script/overview">Google App Script</a> integration functionalities similar to the Visual Basic scripting integration within Microsoft Office macro’s. </p> <p>Recently, I found <a href="https://developers.google.com/apps-script/articles/gmail_filter_sms">this tutorial to link GMail to Google Calendar</a> written by <a href="https://plus.google.com/u/0/116263732197316259248/about">Romain Vialard</a>.</p> <p>Based on this tutorial, I made the custom script below in order to get a free SMS’s from Google on every new unread e-mail marked as ‘Important’ within GMail. Setting up this little script will give you a brief indication of the power and possibilities of the <a href="https://developers.google.com/apps-script/overview">Google App Script</a> integration.</p> <ol> <li>In <a href="https://www.google.com/calendar/render?tab=mc">Google Calendar</a>, register your mobile phone within the ‘Settings’ > ‘Mobile Setup’, to enable SMS notifications. Sending SMS’s from within Google is free of charges, but it might be limited to 50 messages per day. </li> <li>In <a href="http://www.gmail.com">Gmail</a>, create a new label named 'NbSMS' (Notified by SMS). Once a new mail has been notified by SMS, this label will be assigned to the mail in order to make sure the notification is send out only once and the mail will be ignored by the script once this label is assigned. </li> <li>In <a href="https://drive.google.com/">Google Drive</a>, create a new Spreadsheet, give it any name. </li> <li>Choose the menu ‘Tools’ > ‘Script Editor’. </li> <li>Choose ‘Create script for: Blank Project’. </li> <li>Copy and paste the following script: <pre class="brush: vb">function sendText() {<br /> Logger.log('Start of sendText script');<br /> var today = new Date();<br /> var nowHour = today.getHours();<br /> var startTime = 8;<br /> var endTime = 24;<br /> <br /> Logger.log('The SMS notification will only run between: ' + startTime + ' and ' + endTime + ', current hour: ' + nowHour);<br /> if (nowHour <= startTime || nowHour >= endTime) {<br /> Logger.log('Quite time, no SMS notification will be sent between: ' + startTime + ' and ' + endTime + ', current hour: ' + nowHour);<br /> } else {<br /> // var events = cal.getEvents(new Date(startDateAndTime), new Date(endDateAndTime));<br /> //based on https://developers.google.com/apps-script/articles/gmail_filter_sms<br /> var notifiedBySmsLabel = 'NbSMS';<br /> var unreadPriority = GmailApp.getPriorityInboxUnreadCount();<br /> var unreadsFound = 0;<br /> Logger.log("Number of unread emails in your Priority Inbox : " +<br /> GmailApp.getPriorityInboxUnreadCount());<br /> if (unreadPriority > 0) {<br /> var threads = GmailApp.getPriorityInboxThreads();<br /> //threads.refresh();<br /> var now = new Date().getTime();<br /> var alreadyNotified = false;<br /> for(i in threads){<br /> if (!threads[i].isUnread()) {<br /> alreadyNotified = true;<br /> if(unreadsFound >= unreadPriority) {<br /> break;<br /> } else {<br /> continue;<br /> }<br /> } else { <br /> ++unreadsFound;<br /> }<br /> var threadLabels = threads[i].getLabels();<br /> for(y in threadLabels) {<br /> if(threadLabels[y].getName() == notifiedBySmsLabel) {<br /> alreadyNotified = true;<br /> break;<br /> }<br /> }<br /> if (!alreadyNotified) {<br /> var smsText = 'Mail: '+threads[i].getFirstMessageSubject() + ', from: ' + threads[i].getMessages()[0].getFrom();<br /> smsText = smsText + ' ' + threads[i].getMessages()[0].getPlainBody();<br /> Logger.log('Event with SMS will be created with content: ' + smsText);<br /> var event = CalendarApp.createEvent(smsText,<br /> new Date(now+60000),<br /> new Date(now+60000));<br /> event.setDescription(smsText);<br /> event.addSmsReminder(0);<br /> var label = GmailApp.getUserLabelByName(notifiedBySmsLabel);<br /> label.addToThread(threads[i]);<br /> }<br /> }<br /> //threads.refresh();<br /> Logger.log('All important messages treated and label ' + notifiedBySmsLabel + ' applied.');<br /> }<br /> }<br />}</pre><br /> </li><br /><br /> <li>Choose the menu ‘Resources’ > ‘Current project’s triggers…’ and add a new trigger. </li><br /><br /> <li>Select the function ‘sendText()’ > Events: ‘Time-driven’ > ‘Minutes timer’ > ‘Every 5 minutes’, and save the trigger. </li><br /><br /> <li>Save the script. </li><br /><br /> <li>Click the Run ► icon. A pop-up opens asking you for your authorization to access the Gmail and Google Calendar services. </li><br /><br /> <li>Click the Authorize button. </li><br /><br /> <li>Click the Run ► icon again. </li><br /><br /> <li>Choose the menu ‘View’ > ‘Logs’ to see the log output. </li><br /><br /> <li>Debugging is possible using the bug icon. Set some breakpoints by clicking on the line number next to the script. </li><br /><br /> <li>After one minute, you should receive a text on your mobile device, containing the subject of the important unread emails within GMail. </li><br /><br /> <li>To see what other functionalities are available within GMail, Google Calendar en other Google services, see the <a href="https://developers.google.com/apps-script/overview">Google App Script reference guide</a>. </li><br /><br /> <li>Some other interesting tutorial to work with Google Calendar events is available in <a href="http://www.acertainblog.com/2012/12/writing-google-apps-script.html">this blog post</a>. </li><br /></ol><br /><br /><p>Update 09/04/2014: optimized the labeling to only apply the label to messages which were not yet labeled, instead of all messages in priority inbox.</p><br /><br /><p>Update 21/10/2015: Google no longer supports to get notified by SMS for a calendar item. So the approach above won’t work any longer (calendar item will be created, but no sms will be received).</p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4275131957855627760.post-91199276958060651332013-08-08T11:50:00.001+02:002013-08-20T10:50:14.794+02:00Svn file full history<p>If you’d need to search in the full history of a file stored in SVN, the <a href="https://dl.dropboxusercontent.com/u/2328438/SvnFileFullHistory.bat">script</a> below can make an export of all changes ever applied into a file, starting for the first revision (full file) and adding each time an overview of all changes applied. </p> <p>If you remember you ever applied some changes in a file, but removed it afterwards, this can help to search in the full history. Only tested on text based files.</p> <p>For each revision, the revision number, author, timestamp and svn comments will be listed, followed by an overview of the removed lines, added lines, update lines etc. The initial revision will be a full extract of the original file.</p> <p><strong>Usage</strong>: save the <a href="https://dl.dropboxusercontent.com/u/2328438/SvnFileFullHistory.bat">SvnFileFullHistory.bat</a> script in an SVN folder next to the file for which you’d need a full history extract. Drop the svn file onto the bat script to start the script and retrieve the full history of the dropped svn file. The full svn file history will be saved in a txt file in the same folder and with the same base name as the file to extract, but adding “-FullSvnHistory.txt” behind the file name. The extract can take a while, depending on the number of revisions and the size of the svn file. But while the extract is ongoing, the “…-FullSvnHistory.txt” can be read already, reloading it to get future updates.</p> <p>A similar <a href="https://dl.dropboxusercontent.com/u/2328438/SvnFileFullHistory.sh">linux shell script</a> has been created as well (not fully tested).</p> <p>Based on information found on <a href="http://stackoverflow.com/questions/5622367/generate-history-of-changes-on-a-file-in-svn/5721533#5721533">stackoverflow</a> by user <a href="http://stackoverflow.com/users/222481/ladenedge">ladenedge</a>.</p> <p>Full <a href="https://dl.dropboxusercontent.com/u/2328438/SvnFileFullHistory.bat">script</a> content:</p> <pre class="brush: vb">@echo off <br />TITLE SVN - Full file history <br />REM Original source: http://stackoverflow.com/questions/282802/how-can-i-view-all-historical-changes-to-a-file-in-svn and http://stackoverflow.com/questions/5622367/generate-history-of-changes-on-a-file-in-svn/5721533#5721533<br />echo Copy this bat script next to the checked out svn file on which to get full svn history. Drag and drop the svn file onto the bat script to start fetching the info (or a open command window and provide the name of the svn file as first parameter to the bat script execution) <br />if "%1%"=="" pause <br />set file=%1 <br />set report=%file%-FullSvnHistory.txt <br />if [%file%] == [] ( <br />  echo Usage: "%0 <file>" <br />  exit /b <br />)<br />echo Retrieving svn history of file, please wait... <br />echo The report will be saved in the file: %report%. <br />echo To stop the process press Ctrl+c.<br />rem first revision as full text <br />for /F "tokens=1 delims=-r " %%R in ('"svn log -q %file%"') do ( <br />  svn log -r %%R %file% > %report% <br />  svn cat -r %%R %file% >> %report% <br />  goto :diffs <br />)<br />:diffs<br />rem remaining revisions as differences to previous revision <br />for /F "skip=2 tokens=1 delims=-r " %%R in ('"svn log -q %file%"') do ( <br />  echo. <br />  svn log -r %%R %file% >> %report% <br />  svn diff -c %%R %file% >> %report% <br />)</pre> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-34985270444272686472012-09-29T16:03:00.001+02:002014-02-17T12:20:35.143+01:00My Excel macro selection<p>Some easy to use Excel macro’s. All explained below are available in one <a href="http://dl.dropbox.com/u/2328438/ExcelMacrosExample.xltm" target="_blank">Excel Macro Example</a>.</p> <p>All macro’s are combined in one example Excel sheet template to test the usage of it.</p> <h2>Password Generation</h2> <pre class="brush: vb">Function RndPassword(vLength)<br /> 'This function will generate a random strong password of variable<br /> 'length.<br /> 'This script is provided under the Creative Commons license located<br /> 'at http://creativecommons.org/licenses/by-nc/2.5/ . It may not<br /> 'be used for commercial purposes with out the expressed written consent<br /> 'of NateRice.com<br /><br /> For x = 1 To vLength<br /> Randomize<br /> vChar = Int(89 * Rnd) + 33<br /> If vChar = 34 Then 'this is quote character, replace it with a single quote<br /> vChar = 39<br /> End If<br /> RndPassword = RndPassword & Chr(vChar)<br /> Next<br />End Function</pre><br /><br /><br /><br /><br /><br /><br /><br /><p>An example usage of this Password generation function: I have a list with on each row a button to generate a password in the cell next to the button. Each button is linked to the same macro (for easy copy paste of the row). The cell to fill with the new password is automatically detected based on the cell in which the button is drawn. Before setting the new generated password, a warning is shown in a message box.</p><br /><br /><br /><br /><br /><br /><br /><br /><p><a href="http://lh3.ggpht.com/-5CIRC2HXRU0/UGb_lL_0BkI/AAAAAAAAHWM/UBNhYozSvuc/s1600-h/ExcelMacroPasswordGeenration%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ExcelMacroPasswordGeenration" border="0" alt="ExcelMacroPasswordGeenration" src="http://lh6.ggpht.com/-xoecwRFpyp0/UGb_mNRH_wI/AAAAAAAAHWU/BaH8aWuh1s0/ExcelMacroPasswordGeenration_thumb%25255B1%25255D.png?imgmax=800" width="679" height="147" /></a></p><br /><br /><br /><br /><br /><br /><br /><br /><pre class="brush: vb">Sub Process_GenNewPassword()<br /> Dim LRange As String<br /> Dim RowOffset As Integer<br /> Dim ColumnOffset As Integer<br /> Dim newGenPassword As String<br /> 'Find cell that button resides in<br /> LName = Application.Caller<br /> Set bBox = ActiveSheet.Buttons(LName)<br /> LRange = bBox.TopLeftCell.Address<br /> RowOffset = 0 ' relative location of the row in which to work, same row used<br /> FirstNameColumnOffset = -5 ' relative location of firstname column in row to show in warning message box<br /> LastNameColumnOffset = -4 ' relative location of lastname column in row to show in warning message box<br /> PasswordColumnOffset = 2 ' relative location of column in row where the generated password should be set<br /> sResult = MsgBox("The password for user '" & Range(LRange).Offset(RowOffset, FirstNameColumnOffset).Value & " " & Range(LRange).Offset(RowOffset, LastNameColumnOffset).Value & "' will be replaced with a new random password!", vbExclamation + vbOKCancel, "New password generation")<br /> If (sResult <> 1) Then<br /> Exit Sub<br /> End If <br /> newGenPassword = RndPassword(10)<br /> Range(LRange).Offset(RowOffset, PasswordColumnOffset).Value = newGenPassword<br />End Sub</pre><br /><br /><br /><br /><br /><br /><br /><br /><h2>Checkboxes</h2><br /><br /><br /><br /><br /><br /><br /><br /><h3>Checkbox LinkedCell</h3><br /><br /><br /><br /><br /><br /><br /><br /><p>Whenever you need many checkboxes in your sheet, you'll probably need each checkbox to be linked to the cell it is residing on. <br /> <br /><br /><br /> <br />When copy-pasting rows or columns, the checkboxes will be copied as well, but they will still be linked to the same cell as the checkbox you started to copy-paste. The little macro shown below, will update each Checkbox on the active sheet and set it's linked cell to the cell on which the Checkbox is drawn.</p><br /><br /><br /><br /><br /><br /><br /><br /><pre class="brush: vb">Sub setCheckBoxLinkedCell()<br /> 'Loop through all Checkboxes in the active sheet<br /> 'Set for each Checkbox it's "LinkedCell" value to the cell on which the Checkbox is drawn<br /> Application.ScreenUpdating = False<br /> ' turns off screen updating<br /> Application.DisplayStatusBar = True<br /> ' makes sure that the statusbar is visible<br /> Application.StatusBar = "Updating linked cells of all Checkboxes in this sheet."<br /> For Each chk In ActiveSheet.Checkboxes<br /> LRow = ActiveSheet.Checkboxes(chk.Name).TopLeftCell.Row<br /> LColumn = ActiveSheet.Checkboxes(chk.Name).TopLeftCell.Column<br /> cA1 = Application.ConvertFormula("R" & LRow & "C" & LColumn, xlR1C1, xlA1)<br /><br /> ActiveSheet.Shapes.Range(Array(chk.Name)).Select<br /> With Selection<br /> .Value = xlOff<br /> .LinkedCell = cA1<br /> .Display3DShading = False<br /> End With<br /> Next chk<br /> Application.StatusBar = "Linked cells of all Checkboxes in this sheet are updated."<br /> Application.ScreenUpdating = True<br />End Sub</pre><br /><br /><br /><br /><br /><br /><br /><br /><h3>Save date on Checkbox check</h3><br /><br /><br /><br /><br /><br /><br /><br /><p>The macro below will set the date in the cell next to the Checkbox whenever the checkbox is checked. All checkboxes are linked to the same macro, for easy copy-pasting. The cell in which to save the date will be detected based on the checkbox location.</p><br /><br /><br /><br /><br /><br /><br /><br /><p><a href="http://lh5.ggpht.com/-6kgdnUq8T2g/UGb_m3TPp3I/AAAAAAAAHWc/_0xkKB9LXxM/s1600-h/ExcelMacroCheckboxDate%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px auto 5px; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ExcelMacroCheckboxDate" border="0" alt="ExcelMacroCheckboxDate" src="http://lh6.ggpht.com/-JLLbB3UZZeE/UGb_ngoxlxI/AAAAAAAAHWk/RjSq6nfkq98/ExcelMacroCheckboxDate_thumb%25255B1%25255D.png?imgmax=800" width="185" height="158" /></a></p><br /><br /><br /><br /><br /><br /><br /><br /><pre class="brush: vb">Sub Process_CheckBox()<br /> Dim cBox As CheckBox<br /> Dim LRow As Integer<br /> Dim LRange As String<br /><br /> LName = Application.Caller<br /> Set cBox = ActiveSheet.Checkboxes(LName)<br /><br /> 'Find address that checkbox resides in<br /> LRange = cBox.TopLeftCell.Address<br /> DateRowOffset = 0 ' row offset (relative to the checkbox location) in which the date should be set<br /> DateColumnOffset = 1 ' column offset (relative to the checkbox location) in which the date should be set<br /> <br /> 'Change date if checkbox is checked<br /> If cBox.Value > 0 Then<br /> Range(LRange).Offset(DateRowOffset, DateColumnOffset).Value = Date<br /><br /> 'Clear date if checkbox is unchecked<br /> Else<br /> Range(LRange).Offset(DateRowOffset, DateColumnOffset).Value = Null<br /> End If<br />End Sub</pre><br /><br /><br /><br /><br /><br /><br /><br /><h2>Create Folder Structure</h2><br /><br /><br /><br /><br /><br /><br /><br /><p>I copied my previously explained 'Create Folder Structure' in this <a href="http://dl.dropbox.com/u/2328438/ExcelMacrosExample.xltm" target="_blank">Excel Macros Example</a>. For all details, see <a href="http://mytselection.blogspot.be/2009/10/folder-structure-creator-excel-vbs.html" target="_blank">my previous blog post</a>. An import of an existing system folder structure is now added in there as well.</p><br /><br /><br /><br /><br /><br /><br /><br /><p><a href="http://lh5.ggpht.com/-nlcS42tCSpE/UGb_omAgGbI/AAAAAAAAHWs/M5OPNxfa96g/s1600-h/ExcelMacroCreateFolderStructure%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ExcelMacroCreateFolderStructure" border="0" alt="ExcelMacroCreateFolderStructure" src="http://lh6.ggpht.com/-tYSggsQxnwE/UGb_pWji5AI/AAAAAAAAHW0/mcWgWKTObW8/ExcelMacroCreateFolderStructure_thumb%25255B2%25255D.png?imgmax=800" width="674" height="103" /></a></p><br /><br /><br /><br /><br /><br /><br /><br /><h2>Run macro upon opening a workbook</h2><br /><br /><br />To run a macro whenever the workbook Excel sheet is opened, the <font face="Courier New">Sub Workbook_Open</font> in '<font face="Courier New">ThisWorkbook</font>' can be used. This is needed if you want to link some keyboard keys to specific macro's. <br /><br /><br /><br /><br /><br /><br /><br /><h3>Link keys to macro</h3><br /><br /><br />To link a keyboard key to a specific macro action, the <font face="Courier New">Application.OnKey "...<keyname>"</font><font face="Courier New"></font> can be used in the auto startup macro. The keys must be unlinked before closing the workbook. I used this functionality in my Exam Point Counter workbook, described <a href="http://mytselection.blogspot.be/2010/03/exam-point-counter-excel.html" target="_blank">in this previous blog post</a>. In '<font face="Courier New">ThisWorkbook</font>': <br /><br /><br /><br /><br /><br /><br /><br /><pre class="brush: vb">'startup macro<br />Private Sub Workbook_Open()<br /> Application.OnKey "t", "tKeyPressed"<br /> Application.OnKey "e", "eKeyPressed"<br /> Application.OnKey "s", "sKeyPressed"<br /> Application.OnKey "t", "tKeyPressed"<br />End Sub<br />Private Sub Workbook_BeforeClose()<br /> Application.OnKey "t"<br /> Application.OnKey "e"<br /> Application.OnKey "s"<br /> Application.OnKey "t"<br />End Sub</pre><br /><br /><br />In any module, for example <font face="Courier New">KeyboardAction</font>: <br /><br /><br /><br /><br /><br /><br /><br /><pre class="brush: vb">Sub tKeyPressed()<br /> Application.DisplayStatusBar = True<br /> Application.StatusBar = "t key linked to special action in KeyboardAction module"<br />End Sub<br />Sub eKeyPressed()<br /> Application.DisplayStatusBar = True<br /> Application.StatusBar = "e key linked to special action in KeyboardAction module"<br />End Sub<br />Sub sKeyPressed()<br /> Application.DisplayStatusBar = True<br /> Application.StatusBar = "s key linked to special action in KeyboardAction module"<br />End Sub</pre><br /><br /><br /><br /><br /><br /><br /><br /><h2>Search Lookup</h2><br /><br /><br /><br /><br /><br /><br /><br /><p>The search lookup functionality has also been described in details <a href="http://mytselection.blogspot.be/2010/11/excel-lookups-in-fomulas.html" target="_blank">in this previous blog post</a>. The macro is included in this <a href="http://dl.dropbox.com/u/2328438/ExcelMacrosExample.xltm" target="_blank">Excel Macro Example</a> as well. </p><br /><br /><br /><br /><br /><br /><br /><br /><p>The formula for E9 below looks like:</p><br /><br /><br /><br /><br /><br /><br /><br /><p> <a href="http://lh6.ggpht.com/-DSD4jyMOUMU/UGb_qM6bviI/AAAAAAAAHW8/VGT4KhvLj2U/s1600-h/ExcelMacroSearchLookupFomula%25255B7%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ExcelMacroSearchLookupFomula" border="0" alt="ExcelMacroSearchLookupFomula" src="http://lh6.ggpht.com/-GncuksvjClg/UGb_rG4_0qI/AAAAAAAAHXE/A-yPUS6uiR8/ExcelMacroSearchLookupFomula_thumb%25255B5%25255D.png?imgmax=800" width="290" height="38" /></a></p><br /><br /><br /><br /><br /><br /><br /><br /><p><a href="http://lh5.ggpht.com/-e3eboTdkHPI/UGb_r2fzIpI/AAAAAAAAHXM/ao-z8jztD_U/s1600-h/ExcelMacroSearchLookup%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ExcelMacroSearchLookup" border="0" alt="ExcelMacroSearchLookup" src="http://lh6.ggpht.com/-kQxnKb4-VKE/UGb_s55w0TI/AAAAAAAAHXU/8-eHZ2wCMN4/ExcelMacroSearchLookup_thumb%25255B1%25255D.png?imgmax=800" width="668" height="247" /></a></p><br /><br /><br /><br /><br /><br /><br /><br /><h2>Open Save</h2><br /><br /><br /><br /><br /><br /><br /><br /><p>A button with linked macro to navigate to a folder is shown in the module '<font face="Courier New">OpenSave</font>'. <br /><br /> <br /><br /><br /> <br /><br /><br /> <br />A basic example of the resource exporter I showed <a href="http://mytselection.blogspot.be/2009/04/excel-resource-exporter-special.html" target="_blank">in a previous post</a>, is included as well. It will export the example data set into a text file, and while doing so, all special characters will be converted (eg <font face="Courier New">&#233</font>). For rows in the list of AlternativeEncoding, an alternative encoding for special characters will be used (eg <font face="Courier New">\u00E9</font>). To make the sheet readable, the special characters can be converted back into readable special characters. The conversion is based on a hex2ascii converter, unicode encoding, html encoding and decoding.</p><br /><br /><br /><br /><br /><br /><br /><br /><p>When you would reuse this code, please note, in order to support <font face="Courier New">FileSystemObject</font>, you’ll need to add reference to Microsoft scripting runtime in your Excel workbook VBA. In order to do so, open the Visual Basic environment in Excel (<font face="Courier New">ALT+F11</font>) > Menu ‘<font face="Courier New">Tools</font>’ > ‘<font face="Courier New">Reference</font>’ > Enable ‘<font face="Courier New">Microsoft Scripting Runtime</font>’. If this isn’t done in the workbook, the error ‘<font face="Courier New">User defined type not defined</font>’ will appear when writing an export file on ‘<font face="Courier New">Dim fso As New FileSystemObject</font>’.</p><br /><br /><br /><br /><br /><br /><br /><br /><p><a href="http://lh3.ggpht.com/-1JcBGCWNIXY/UGb_tq4Wj_I/AAAAAAAAHXc/fzqvwa3qqYo/s1600-h/ExcelMacroOpenSave%25255B5%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ExcelMacroOpenSave" border="0" alt="ExcelMacroOpenSave" src="http://lh4.ggpht.com/-WSnqEdrkeF0/UGb_un2zqCI/AAAAAAAAHXk/PEf9Zih7y3U/ExcelMacroOpenSave_thumb%25255B3%25255D.png?imgmax=800" width="704" height="384" /></a></p><br /><br /><br /><br /><br /><br /><br /><br /><p>When the special characters are encoded using the macro, they will look like this:</p><br /><br /><br /><br /><br /><br /><br /><br /><p><a href="http://lh6.ggpht.com/-Oi-xCty6EKU/UGb_v3pstGI/AAAAAAAAHXs/3RZYuxiAhbk/s1600-h/ExcelMacroOpenSaveEncoded%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ExcelMacroOpenSaveEncoded" border="0" alt="ExcelMacroOpenSaveEncoded" src="http://lh5.ggpht.com/-sexOd-A7sAU/UGb_xFsuDxI/AAAAAAAAHX0/o_w2v0c6TMY/ExcelMacroOpenSaveEncoded_thumb%25255B1%25255D.png?imgmax=800" width="691" height="147" /></a></p><br /><br /><br /><br /><br /><br /><br /><br /><h2>PhoneBook vCard export</h2><br /><br /><p>A sheet in which all phonebook data can be added (can be exported from any other application) is now available. Once all contact information is set, it can be exported into a vCard format which is supported by many address and contact management applications. More details can be found in <a href="http://mytselection.blogspot.nl/2013/12/vcard-contact-export-from-excel-contact.html" target="_blank">this specific blog post</a>.</p><br /><br /><p><a href="http://lh5.ggpht.com/-vqeEp39RUgA/UwHwfu7RdZI/AAAAAAAA0WM/LKJXwMftKLw/s1600-h/image%25255B7%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-9Uz-51CFY4k/UwHwga45x5I/AAAAAAAA0WU/JelfKCui6ME/image_thumb%25255B5%25255D.png?imgmax=800" width="702" height="70" /></a></p><br /><br /><p>All code is available in the <a href="http://dl.dropbox.com/u/2328438/ExcelMacrosExample.xltm" target="_blank">Excel Macro Example</a>.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-5507884896136731002012-08-27T23:33:00.001+02:002012-08-27T23:33:26.631+02:00My Android selection<p>I recently moved to Android. Starting with a cheap but excellent <a href="http://www.archos.com/products/gen9/archos_80_g9/index.html?country=us&lang=en&p=2#a" target="_blank">Archos A80s G9</a> Turbo 16GB TI OMAP4430 dual core 1,2GHz with 3G usb stick. It was one of the first tablets to get <a href="http://update.archos.com/9/gen9/changes_firmware_archos_android_gen9it4.htm" target="_blank">official ICS support</a> and it has a very good <a href="http://forum.xda-developers.com/forumdisplay.php?f=1417" target="_blank">XDA support for rooting and custom roms</a>. Last week I replaced my old <a href="http://www.gsmarena.com/samsung_b7610_omniapro-review-378.php" target="_blank">Samsung B7610 Pro</a> Windows Mobile 6.5 phone for a <a href="http://www.gsmarena.com/sony_xperia_sola-4408.php" target="_blank">Sony Xperia Sola MT27i</a> Android 2.3.7 <a href="http://forum.xda-developers.com/showthread.php?t=1679648" target="_blank">rooted and custom rom SSpeed</a>. The <a title="Google Android Ice Cream Sandwich 4.0" href="http://www.android.com/about/ice-cream-sandwich/" target="_blank">ICS</a> update should become <a href="http://forum.xda-developers.com/showthread.php?t=1849570" target="_blank">available soon</a>.</p> <p>A list of my favorite applications I found very useful:</p> <ul> <li><a href="https://play.google.com/store/apps/details?id=com.teamviewer.teamviewer.market.mobile&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS50ZWFtdmlld2VyLnRlYW12aWV3ZXIubWFya2V0Lm1vYmlsZSJd" target="_blank">TeamViewer</a>: free easy VNC with no firewall configuration</li> <li><a href="https://play.google.com/store/apps/details?id=com.google.android.apps.reader&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5nb29nbGUuYW5kcm9pZC5hcHBzLnJlYWRlciJd" target="_blank">Google Reader</a>: RSS reader</li> <li><a href="https://play.google.com/store/apps/details?id=com.sand.airdroid&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5zYW5kLmFpcmRyb2lkIl0." target="_blank">AirDroid</a>: manage your Android device via browser in local network</li> <li><a href="https://play.google.com/store/apps/details?id=com.siber.roboform&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5zaWJlci5yb2JvZm9ybSJd" target="_blank">Roboform</a>: manage all your passwords securely and automated login/saving. Synchronized in cloud for full desktop synchronization and making sure you’ve your passwords everywhere in sync.</li> <li><a href="https://play.google.com/store/apps/details?id=com.pocketmax.phoneAlarm&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5wb2NrZXRtYXgucGhvbmVBbGFybSJd" target="_blank">PhoneAlarm</a>: never miss a new reminder/sms/mail/call by repeating the notification</li> <li><a href="https://play.google.com/store/apps/details?id=de.shapeservices.impluslite&feature=search_result#?t=W251bGwsMSwxLDEsImRlLnNoYXBlc2VydmljZXMuaW1wbHVzbGl0ZSJd" target="_blank">IM+</a>: connect all chat accounts at once (Google Talk, Facebook, MSN, Skype, Yahoo, etc)</li> <li><a href="https://play.google.com/store/apps/details?id=com.tribab.tricount.android&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS50cmliYWIudHJpY291bnQuYW5kcm9pZCJd" target="_blank">TriCount</a>: easy track and calculate group expenses</li> <li><a href="https://play.google.com/store/apps/details?id=mobi.goldendict.android&feature=search_result#?t=W251bGwsMSwxLDEsIm1vYmkuZ29sZGVuZGljdC5hbmRyb2lkIl0." target="_blank">GoldenDict</a>: offline dictionary compatible with the enormous Babylon dictionaries and custom Babylon dicts</li> <li><a href="https://play.google.com/store/apps/details?id=com.agilys.myshopi&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5hZ2lseXMubXlzaG9waSJd" target="_blank">myShopi</a>: grocery list to remember what to buy in which shop, integrated list of shops and their opening hours, coupons and recipes</li> <li><a href="https://play.google.com/store/apps/details?id=kr.aboy.tools&feature=search_result#?t=W251bGwsMSwxLDEsImtyLmFib3kudG9vbHMiXQ.." target="_blank">Smart Tools</a>: use compass, gyro, accelerometer, flashlight, camera to measure etc</li> <li><a href="https://play.google.com/store/apps/details?id=com.opera.browser&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5vcGVyYS5icm93c2VyIl0." target="_blank">Opera mobile</a>: browser</li> <li><a href="https://play.google.com/store/apps/details?id=mikado.bizcalpro&feature=search_result#?t=W251bGwsMSwxLDEsIm1pa2Fkby5iaXpjYWxwcm8iXQ.." target="_blank">Business Calendar</a>: many calendar look and feel options</li> <li>Weather Service Pro: weather info and widget, still able to show cached info while offline (unlike many others)</li> <li><a href="https://play.google.com/store/apps/details?id=com.shazam.encore.android&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5zaGF6YW0uZW5jb3JlLmFuZHJvaWQiXQ.." target="_blank">Shazam Encore</a>: record short music to get full info on artist, offline record possible to get full info later while online</li> <li><a href="https://play.google.com/store/apps/details?id=com.mobilepioneers.tvgidstv&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5tb2JpbGVwaW9uZWVycy50dmdpZHN0diJd" target="_blank">TVGids 2.0</a>: TV schedule overview compatible with Belgian tv </li> <li><a href="https://play.google.com/store/apps/details?id=com.digitaloutcrop.mixologist&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5kaWdpdGFsb3V0Y3JvcC5taXhvbG9naXN0Il0." target="_blank">Mixologist</a>: good looking bar and mix info</li> <li><a href="https://play.google.com/store/apps/details?id=com.nynix.knots3d&feature=more_from_developer#?t=W251bGwsMSwxLDEwMiwiY29tLm55bml4Lmtub3RzM2QiXQ.." target="_blank">Knots3D</a>: info on how to tie knots, with full usage info and 3D details</li> <li><a href="https://play.google.com/store/apps/details?id=com.edwardkim.android.carlocatorfull&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5lZHdhcmRraW0uYW5kcm9pZC5jYXJsb2NhdG9yZnVsbCJd" target="_blank">Car Locator</a>: locate your car, automatically save car location when disconnection Bluetooth car headset</li> <li><a href="https://play.google.com/store/apps/details?id=com.sygic.aura&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5zeWdpYy5hdXJhIl0." target="_blank">Sygic drive</a>: offline GSP navigation, download maps </li> <li><a href="https://play.google.com/store/apps/details?id=dk.tacit.android.foldersync.full&feature=search_result#?t=W251bGwsMSwxLDEsImRrLnRhY2l0LmFuZHJvaWQuZm9sZGVyc3luYy5mdWxsIl0." target="_blank">FolderSync</a>: file manager and synchronization of all cloud storage service (Amazon S3, Box.net, dropbox, ftp, google docs, google drive, netdocuments, sftp, SkyDrive, SMB, SugarSync, Ubuntu One, WebDAV)</li> <li><a href="https://play.google.com/store/apps/details?id=xcxin.filexpert&feature=search_result#?t=W251bGwsMSwxLDEsInhjeGluLmZpbGV4cGVydCJd" target="_blank">FileExpert</a>: filemanager with integrated zip and rar archive support</li> <li><a href="https://play.google.com/store/apps/details?id=AutomateItPro.mainPackage&feature=search_result#?t=W251bGwsMSwxLDEsIkF1dG9tYXRlSXRQcm8ubWFpblBhY2thZ2UiXQ.." target="_blank">AutomateIt</a>: automate tasks, alternative for <a href="https://play.google.com/store/apps/details?id=net.dinglisch.android.taskerm&feature=search_result#?t=W251bGwsMSwxLDEsIm5ldC5kaW5nbGlzY2guYW5kcm9pZC50YXNrZXJtIl0." target="_blank">Tasker</a></li> <li><a href="https://play.google.com/store/apps/details?id=com.mango.findmyphone&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5tYW5nby5maW5kbXlwaG9uZSJd" target="_blank">Find My Phone</a>: retrieve phone GPS location by sending special SMSs with password or make your phone ring on max volume level by sending specific sms (even when phone is muted and offline)</li> <li><a href="https://play.google.com/store/apps/details?id=com.binarybulge.android.apps.keyboard.full&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5iaW5hcnlidWxnZS5hbmRyb2lkLmFwcHMua2V5Ym9hcmQuZnVsbCJd" target="_blank">Ultra Keyboard</a>: keyboard with many customization options and multi clipboard</li> <li><a href="https://play.google.com/store/apps/details?id=slide.cameraZoom&feature=search_result#?t=W251bGwsMSwxLDEsInNsaWRlLmNhbWVyYVpvb20iXQ.." target="_blank">Camera Zoom FX</a>: camera app with special effects and optimized quality</li> <li><a href="https://play.google.com/store/apps/details?id=de.mrfloppycoding.galleryexcluder&feature=search_result#?t=W251bGwsMSwxLDEsImRlLm1yZmxvcHB5Y29kaW5nLmdhbGxlcnlleGNsdWRlciJd" target="_blank">Gallery Excluder</a>: exclude some folder to be shown in Android default image gallery</li> <li><a href="https://play.google.com/store/apps/details?id=com.microsoft.office.onenote&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5taWNyb3NvZnQub2ZmaWNlLm9uZW5vdGUiXQ.." target="_blank">OneNote</a>: MS Office OneNote synchronized notes</li> <li><a href="https://play.google.com/store/apps/details?id=com.evernote&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5ldmVybm90ZSJd" target="_blank">Evernote</a>: notes saved in cloud</li> <li><a href="https://play.google.com/store/apps/details?id=jp.joao.android.CallLogCalendar&feature=search_result#?t=W251bGwsMSwxLDEsImpwLmpvYW8uYW5kcm9pZC5DYWxsTG9nQ2FsZW5kYXIiXQ.." target="_blank">Call Log Calendar</a>: keep track of each call and SMSs in specific calendars</li> <li><a href="https://play.google.com/store/apps/details?id=name.udell.convertor&feature=search_result#?t=W251bGwsMSwxLDEsIm5hbWUudWRlbGwuY29udmVydG9yIl0." target="_blank">Convertor Pro</a>: convert any kind of sizes, currencies, dates, time, etc</li> <li><a href="https://play.google.com/store/apps/details?id=com.mantano.reader.android&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5tYW50YW5vLnJlYWRlci5hbmRyb2lkIl0." target="_blank">Mantano Reader</a>: ebook and pdf reader with good options and compatible with most formats</li> <li><a href="https://play.google.com/store/apps/details?id=com.google.android.apps.translate&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5nb29nbGUuYW5kcm9pZC5hcHBzLnRyYW5zbGF0ZSJd" target="_blank">Google translate</a>: translate text </li> <li><a href="https://play.google.com/store/apps/details?id=cn.wps.moffice_eng&feature=more_from_developer#?t=W251bGwsMSwxLDEwMiwiY24ud3BzLm1vZmZpY2VfZW5nIl0." target="_blank">Kingsoft Office</a>: excellent MS Office document viewer and editor , free!</li> <li><a href="https://play.google.com/store/apps/details?id=se.appello.android.neverlate&feature=search_result#?t=W251bGwsMSwxLDEsInNlLmFwcGVsbG8uYW5kcm9pZC5uZXZlcmxhdGUiXQ.." target="_blank">NeverLate</a>: traffic information</li> </ul> <p>Besides those, some other standard well know applications known by everyone (eg <a href="https://play.google.com/store/apps/details?id=com.facebook.katana&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5mYWNlYm9vay5rYXRhbmEiXQ.." target="_blank">facebook</a>, <a href="https://play.google.com/store/apps/details?id=com.google.android.gm&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS5nb29nbGUuYW5kcm9pZC5nbSJd" target="_blank">gmail</a>, <a href="https://play.google.com/store/apps/details?id=com.whatsapp&feature=search_result#?t=W251bGwsMSwxLDEsImNvbS53aGF0c2FwcCJd" target="_blank">whatsapp</a>, youtube, etc).</p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4275131957855627760.post-58056464419900886282011-09-21T09:13:00.001+02:002012-11-06T21:23:36.287+01:00USB drive daily usage: encrypt & sync<p>As I described in <a href="http://mytselection.blogspot.com/2010/09/userfriendly-encryption-on-usb-sticks.html">a previous post</a>, I advise the <a href="http://www.rohos.com/products/rohos-mini-drive/">Rohos Mini Drive</a> to be used on a USB Stick for daily usage. It does everything you can expect from an encryption tool, but has the benefit to have a unique non-admin account support with <a href="http://www.rohos.com/support/knowledge-base/rohos-disk-browser/">Rohos Disk Browser</a>. But for a user friendly daily usage of a USB drive, an automated synchronization process is required on top of a safe encryption mechanism: you always want the latest version of your files to be available on your own pc and USB stick. If you change anything on any location, all files should get updated immediately.</p> <p>For this I combine the strength of Rohos Mini Drive with <a href="http://www.goodsync.com">GoodSync</a>. GoodSync can be configured to monitor if a USB stick is attached to your system. If it detects the encrypted disk, it needs to launch the Rohos Mini Drive to give access to the encrypted files. Once the encrypted files are accessible by your PC, the files should be synchronized automatically. Since Rohos Mini Drive provides access to the files as a new virtual disk, GoodSynch can be configured to be launched as soon as this virtual disk becomes available. And at that moment, the full synchronization features of GoodSync can be started to make sure all latest versions of your files are available on your PC and on your USB stick.</p> <p>So the only missing part in this process, was the need to launch Rohos Mini Drive and mount the virtual disk automatically. I solved this using the little batch script below. It will find the correct drive, launch Rohos Mini Drive to mount the encrypted disk. The user will need to provide the password. Once the correct password is provided, the encrypted drive will be mounted (become available in Windows Explorer).</p> <p>GoodSync is configured to launch the batch script as soon as the USB stick is connected. The batch script will mount the encrypted disk and another GoodSync job will detect this virtual encrypted disk to become available. In this GoodSync job, the synchronisation of the local folder and encrypted USB Stick folder is configured.</p> <p>Some screenshots of the different configurations: </p> <pre class="brush: vb"><br />@echo off<br />set USB_STICK_NAME=SET_PERSONAL_USB_STICK_NAME<br />set PROCESS_NAME=Rohos Mini.exe<br />tasklist /FI "IMAGENAME eq %PROCESS_NAME%" 2>NUL | find /I /N "%PROCESS_NAME%">NUL<br />if "%ERRORLEVEL%"=="0" GOTO ROHOS_RUNNING<br />GOTO ROHOS_NOT_RUNNING<br /><br />:ROHOS_RUNNING<br /> exit<br /><br />:ROHOS_NOT_RUNNING<br /> set _Target=NotFound<br /> <br /> for /f usebackq %%a in (`Drives.exe -f %USB_STICK_NAME%`) do set _Target=%%a<br /> if "%_Target%" == "NotFound" (<br /> echo Unable to find target drive named "%USB_STICK_NAME%"<br /> goto :EOF<br /> )<br /> start "Rohos" "%_Target%\%PROCESS_NAME%"<br /> exit<br /></pre><br /><br /><br /><br /><p>Update 06/11/2012: I moved to using EncFS to encrypt my files on Windows/Cloud/Android instead of using Rohos Mini Drive. Rohos had some compatibility issues with Windows 7. This good <a href="http://www.funzt.info/?p=910#comment-169" target="_blank">blogpost</a> describes all tools needed to get started with EncFS on different environments. I’m only missing a decent portable solution that could work without admin rights.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-42170615517354245342011-08-22T16:14:00.004+02:002022-07-21T23:59:20.135+02:00Batch parameter modifierIn batch script, one can use the <a href="http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true">following modifier</a> to expand to full path etc.<br />
<br />
%1 first parameter provided to the script.<br />
%~1 Expands %1 and removes any surrounding quotation marks ("").<br />
%~f1 Expands %1 to a fully qualified path name.<br />
%~d1 Expands %1 to a drive letter.<br />
%~p1 Expands %1 to a path.<br />
%~n1 Expands %1 to a file name.<br />
%~x1 Expands %1 to a file extension.<br />
%~s1 Expanded path contains short names only.<br />
%~a1 Expands %1 to file attributes.<br />
%~t1 Expands %1 to date and time of file.<br />
%~z1 Expands %1 to size of file.<br />
%~$PATH:1 Searches the directories listed in the PATH environment variable and expands %1 to the fully qualified name of the first one found. If the environment variable name is not defined or the file is not found, this modifier expands to the empty string.<br />
%~dp1 Expands %1 to a drive letter and path.<br />
%~nx1 Expands %1 to a file name and extension.<br />
%~dp$PATH:1 Searches the directories listed in the PATH environment variable for %1 and expands to the drive letter and path of the first one found.<br />
%~ftza1 Expands %1 to a dir-like output line.<br />
%cd% current working direcotry.<br />
%~dp0 script directory.<br />
%~dp0..\ parent of script direcotry.<br />
<br />
SET Today=%Date: =0%<br />
SET TodayYYYYMMDD=%Today:~-4%%Date:~-7,2%%Date:~-10,2%<br />
SET Now=%Time: =0%<br />
<br />
In the previous examples, you can replace %1 and PATH with other batch parameter values.<br />
<br />
The %* modifier is a unique modifier that represents all arguments passed in a batch file. You cannot use this modifier in combination with the %~ modifier. The %~ syntax must be terminated by a valid argument value.<br />
<br />
You cannot manipulate batch parameters in the same manner that you can manipulate environment variables. You cannot search and replace values or examine substrings. However, you can assign the parameter to an environment variable, and then manipulate the environment variable.<div><br /></div><div><a href="https://ss64.com/nt/syntax-replace.html" target="_blank">String replace</a> in variables: <span style="font-family: "Lucida Console", "Andale Mono", Menlo, "Courier New", Courier, monospace; font-size: 16px;">SET VAR=%VAR:12345=Hello% </span>(replace 12345 with Hello in variable VAR)</div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4275131957855627760.post-47779012886251396272011-01-21T11:06:00.009+01:002011-01-23T13:30:52.239+01:00Salling Clicker - Windows Mobile<p><a href="http://www.google.be/search?q=salling%20clicker%203.5.0.860" target="_blank">Salling Clicker</a> is a remote control software. It lets you control popular <img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" border="0" align="right" src="http://www.comp.nus.edu.sg/~marianmi/res/sallingclicker.png" />applications from a mobile phone or handheld computer through a user interface similar to a portable media player. <br />You can choose to have the computer take action when you make or receive a phone call or get close to your pc. For instance, Salling Clicker can automatically mute the system volume while you're on the phone or automatically lock the pc while your gone. </p> <ul> <li>Control PowerPoint, iTunes, Windows Media Player, and more with your mobile device. </li> <li>Works with all major Bluetooth stacks (no configuration required). </li> <li>Amazingly easy-to-use WiFi connectivity for long-range control. </li> <li>Works with over 300 devices. </li> <li>Easily extend support for other applications using JavaScript or VBScript </li> </ul> <h3>Scripts</h3> Salling Clicker is installed with a set of default scripts (JavaScript for Windows) to support many different applications (PowerPoint, iTunes, MediaPlayer, etc.). On the <a href="http://www.salling.com/forums/viewforum.php?f=13" target="_blank">forums of Salling Clicker</a> many other custom scripts can be found, created and shared by the community. <br />I've created a set of personal scripts, some are base on the official scripts, some are based on the scripts found in the forums and some are completely new. I just list all the scripts I use: <br /> <ul> <li><a href="http://dl.dropbox.com/u/2328438/DeSlimsteMensSC.zip">De Slimste Mens</a>: as explain in this <a href="http://mytselection.blogspot.com/2010/12/de-slimste-mens-ter-wereld.html" target="_blank">separate blog post</a>, I've created a complete interface to control the Flemish game "De Slimste Mens". </li> <li><a href="http://dl.dropbox.com/u/2328438/myTMouseKeyboardSC.zip">myT Mouse Keyboard</a>: full control for mouse and keyboard. Specially made for Windows Mobile devices with hardware keyboard (tested on Samsung Omnia B7310 and HTC Wizard (Qtek9100)). Use the screen to move mouse, use 'Menu' button to get context menu (mouse right click), use 'Help' button for extra options. To get a good overview of all options, I’ve put <a href="http://www.dropbox.com/gallery/2328438/1/myTMouseKeyboard?h=5d1629" target="_blank">some screenshot online</a>. <br />Use hardware keyboard to send all keys. The used keys will be displayed on the main screen for easy action tracking. <br />The extra option are: <ul> <li>switching of Shift/Ctrl/Alt/Win keys </li> <li>sending special characters </li> <li>sending function keys (eg F1-F12, pause, insert, delete, etc) </li> <li>sending media keys (play next song, webbrowser control, etc) </li> <li>sending long text: text input on device </li> <li>open a file on pc: navigate to folder and open a file, recent file history, easy access to favorite locations (eg start menu, 'my documents', etc) </li> <li>Zoom screen using freeware <a href="http://technet.microsoft.com/en-us/sysinternals/bb897434" target="_blank">ZoomIt</a> application, all ZoomIt options supported (pen, break, etc) </li> <li>Show a screenshot of the pc on the main screen. </li> <li>Exchange data from pc clipboard to device and/or put text into the pc clipboard </li> </ul> </li> <li><a href="http://dl.dropbox.com/u/2328438/PowerPointSC.zip">PowerPoint</a>: distribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/MediaPlayerSC.zip">MediaPlayer</a>: distribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/MediaPortalSC.zip">MediaPortal</a>: from forum </li> <li><a href="http://dl.dropbox.com/u/2328438/MediaCenterSC.zip">MediaCenter</a>: from forum </li> <li><a href="http://dl.dropbox.com/u/2328438/iTunesSC.zip">iTunes</a>: distribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/BSPlayerSC.zip">BSPlayer</a>: from forum </li> <li><a href="http://dl.dropbox.com/u/2328438/VLCSC.zip">VLC</a>: from forum </li> <li><a href="http://dl.dropbox.com/u/2328438/WinDVDSC.zip">WinDVD</a>: from forum </li> <li><a href="http://dl.dropbox.com/u/2328438/MSNSC.zip">MSN Online status</a>: from forum </li> <li><a href="http://dl.dropbox.com/u/2328438/uTorrentSC.zip">uTorrent</a>: from forum </li> <li><a href="http://dl.dropbox.com/u/2328438/ShutdownControlsSC.zip">ShutdownControls</a>: shutodown/hibernate/standby/screen on-off/lock </li> <li><a href="http://dl.dropbox.com/u/2328438/DeviceInfoSC.zip">Device Info</a>: distiribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/DisconnectSC.zip">Disconnect</a>: distiribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/SystemVolumeSC.zip">System volume</a>: control pc volume </li> <li><a href="http://dl.dropbox.com/u/2328438/MuteWhileOnPhoneSC.zip">MuteWhileOnPhone</a>: distribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/CallNotifierSC.zip">CallNotifier</a>: distribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/LockComputerSC.zip">LockComputer</a>: distribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/MonitorAwaySC.zip">MonitorAway</a>: distribuated by Salling Clicker </li> <li><a href="http://dl.dropbox.com/u/2328438/ShowMessageSC.zip">Show Message</a>: show message on pc </li> </ul> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-54147661085216910372011-01-17T15:13:00.030+01:002012-05-22T20:48:57.538+02:00Toshiba Tecra S11<p>We recently replaced our old portable Lenovo ThinkPad T61 by the <a href="http://eu.computers.toshiba-europe.com/innovation/generic/b2b-tecra-s11" target="_blank">Toshiba Tecra S11</a>, Intel® Core™ i5 M520, 2,40GHz, 4GB Ram.<br />Although this new one is certainly not the best device available, as can be read in this <a href="http://www.notebookcheck.net/Review-Toshiba-Tecra-S11-11H-Notebook.29667.0.html" target="_blank">technical review</a>, it has some nice new features like a finger print reader, SmartCard reader, webcam, DisplayPort and a larger HDD (320GB).</p><br /><h2>Drivers</h2><br />When we received the laptop, not all drivers were installed correctly. Additional drivers can be download and should be installed in the provided order:<br /><ul><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/assist-20100128112828.zip">TOSHIBA Assist</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/tfmc-20100128115842.zip">TOSHIBA Extended Tiles for Windows Mobility Center</a>, Windows Mobility Center can be opened in Windows 7 using the shortcut key Win+X</li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/trustpm-20100128105712.zip">Infineon Trusted Platform Module Software</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/csutil-20110104161056.zip">Intel Chipset SW Installation Utility</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/heci-20100128111107.zip">Intel Management Engine Interface</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/amt-20100930140041.zip">Active Management Technology (AMT)</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/display-20110104165919.zip">Display Driver</a>, switch between displays in Windows 7 using the shortcut key Win+P</li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/stormng-20100128111144.zip">Intel Storage Driver</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/vap-20110117130811.zip">TOSHIBA Value Added Package</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/sound-20100128112343.zip">Realtek Audio Driver</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/sound-20100128111919.zip">NVIDIA HD Audio Driver 1.0.2.0</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/lan-20100128110309.zip">LAN Driver Intel</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/lan-20100128112550.zip">LAN Realtek</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/tpdrv-20100128105614.zip">ALPS Pointing Device Driver Touch Pad</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/carc-20110104170258.zip">Ricoh Card Reader</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/smr-20100128112058.zip">O2Micro Smart Card Reader Driver 1.1.4.207G</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/mdm-20100128121922.zip">TOSHIBA Software Modem</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/mrsu-20100128121830.zip">TOSHIBA Software Modem Region Select Utility</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/wlesslan-20091112152016.html">Wireless LAN Driver</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/confree-20110104170335.zip">ConfigFree</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/ndidrst-20110104170748.zip">TOSHIBA Network Device ID Registry Setting Tool</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/r-util-20110104170453.zip">TOSHIBA 180 Degrees Rotation Utility</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/fp-sw-20101203101016.zip">TOSHIBA Fingerprint Utility</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/fareg-20100128120225.zip">TOSHIBA Face Recognition</a> slows down Windows logon and not very effective</li><br /><li><a href="http://support1.toshiba-tro.de/tools/bluetooth/BT-stack.zip?Submit=Download">Bluetooth Stack for Windows by Toshiba</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/blt-mon-20100205112935.zip">Bluetooth Monitor</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/eco-20100526090803.zip">TOSHIBA eco Utility 1.1.12.64</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/hdsdal-20100325114839.zip">TOSHIBA HDD/SSD Alert 3.1.64.4</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/hddprot-20110104170615.zip">TOSHIBA HDD Protection 2.2.0.3</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/sest-20100128121757.zip">TOSHIBA Service Station V2.1.40</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/pchm-20100325114956.zip">TOSHIBA PC Health Monitor 1.5.1.64</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/pr-20100128112254.zip">PlayReady PC Runtime</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/usbsac-20100128122006.zip">TOSHIBA USB Sleep and Charge Utility 1.3.2.0</a> allows charging (power) external devices over USB, even while the pc is in sleep or hibernate mode</li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/webcam-20110104171019.zip">TOSHIBA Web Camera Application V1.1.2.6</a></li><br /><li><a href="http://support.toshiba-tro.de/tools/eula/calleula.asp?ID=77851">BIOS Upgrade</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/reti-20110104170857.zip">ReelTime</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/IRST-20110104165517.zip">Rapid Storage Technology Driver</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/regpatch-20100128112150.zip">Registry Patch</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/secas-20110104170933.zip">Security Assist</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/sest-20100128121757.zip">Service Station</a></li><br /><li><a href="http://eu.computers.toshiba-europe.com/tempro/ToshibaW7TEMPRO.exe">TEMPRO</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/tbt-20110104165556.zip">Turbo Boost Technology</a></li><br /><li><a href="http://support1.toshiba-tro.de/tedd-files2/0/wlmng-20100118172548.html">Wireless Manager</a></li><br /></ul><br /><p>Toshiba official software download site: <a href="http://eu.computers.toshiba-europe.com/innovation/download_drivers_bios.jsp?service=EU&selCategory=2&selFamily=5&selSeries=149&selProduct=1135&selShortMod=965&language=13&selOS=29&selType=all&yearupload=&monthupload=&dayupload=&useDate=null&mode=allMachines&search=&action=search&macId=&country=all&page=2&nextCl=true&startPage=1" target="_blank">http://aps2.toshiba-tro.de/</a>, <a href="http://be.computers.toshiba-europe.com/innovation/download_drivers_bios.jsp?LNG=10&service=BE&selCategory=2&selFamily=5&selSeries=149&selProduct=1135&selShortMod=965&language=13&selOS=30&selType=all&yearupload=&monthupload=&dayupload=&useDate=null&mode=allMachines&search=&action=search&macId=&country=3&page=1" target="_blank">64 bit drivers</a><br /><h2>Extras</h2><br /><ul><br /><li>The build-in SmartCard reader can be used to read the <a href="http://eid.belgium.be/nl/" target="_blank">Belgian eID cards</a>, and thus also to login on the <a href="http://www.taxonweb.be" target="_blank">Tax-On-Web pages</a>. After installation of the SmartCard driver (see above) and a reboot, install <a href="http://eid.belgium.be/nl/Hoe_installeer_je_de_eID/index.jsp" target="_blank">the eID software</a> and reboot again.</li><br /><li><a href="http://autosensitivity.codeplex.com/" target="_blank">AutoSensitivity</a> allows you to define different mouse sensitivities (speeds) for your touchpad and mouse and automatically switch between them (based on mouse connect / disconnect). This can be useful since the touchpad on this Toshiba is rather slow.</li><br /><li><a href="http://mytselection.blogspot.com/2009/02/my-windows-explorer-extentions.html" target="_blank">Windows Explorer extentions</a></li><br /></ul>
<p>
Update 22/05/2012: As I moved to Windows 64 bit, I added <a href="http://be.computers.toshiba-europe.com/innovation/download_drivers_bios.jsp?LNG=10&service=BE&selCategory=2&selFamily=5&selSeries=149&selProduct=1135&selShortMod=965&language=13&selOS=30&selType=all&yearupload=&monthupload=&dayupload=&useDate=null&mode=allMachines&search=&action=search&macId=&country=3&page=1" target="_blank">the link to download 64 bit drivers</a>.
</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4275131957855627760.post-27743050272350007082010-12-01T22:15:00.018+01:002016-01-22T10:40:59.666+01:00De Slimste Mens Ter Wereld<h1>
(Nederlandse versie: zie onder)</h1>
<a href="http://dekompagnie.be/" target="_blank" title="De Slimste Mens - Jonathan Huyghe">Jonathan Huyghe</a> has made a <a href="http://dekompagnie.be/DSM3x4.zip" target="_blank" title="De Slimste Mens v3 - 4 spelers">nice little flash tool</a> to let you play the popular Flemish <a href="http://www.een.be/programmas/de-slimste-mens-ter-wereld" target="_blank" title="De Slimste Mens Ter Wereld - één">TV show ‘De slimste mens ter wereld’ (The smartest person of the world)</a> at home.<br />
Because I found it quite complicated to prepare the game, I made a little <a href="http://dl.dropbox.com/u/2328438/DSM-Voorbereiding.xlt" target="_blank" title="Excel template 'DSM-Voorbereiding.xlt'">Excel template</a> that should make it more straightforward to make all preparations for it. It can easily generate the required ‘antwoorden.txt’ file with the correct syntax and it can print out cards with instructions to be used by the host during the game play.<br />
<br />
<b>Update 01/2016</b>: An alternative version (independent of the version discussed below) is now available with full online management of the quiz. It works very well and very easy to create your own quiz's! See: <a href="http://deslimstemens.nu/">http://deslimstemens.nu/</a><br />
<h2>
User Manual</h2>
<ol>
<li>Download this <a href="http://dl.dropbox.com/u/2328438/DSM3x4.zip" target="_blank" title="DSM flash v3x4 + Excel template voorbereiding">package</a> containing the original sources of Jonathan Huyghe + <a href="http://dl.dropbox.com/u/2328438/DSM-Voorbereiding.xlt" target="_blank" title="DSM-Voorbereiding Excel template afzonderlijk">Excel template ‘DSM-Voorbereiding.xlt’</a> </li>
<li>Open the file ‘DSM-Voorbereiding’ with Excel and allow macros to run.<a href="http://lh4.ggpht.com/_OiMAwW7QI68/TPbBjOpl4tI/AAAAAAAAEc8/YNN_b4d7GBw/s1600-h/EnableMacros%5B11%5D.png"><img alt="EnableMacros" border="0" src="http://lh3.ggpht.com/_OiMAwW7QI68/TPbBkM_I21I/AAAAAAAAEdA/WuN0rGWTLas/EnableMacros_thumb%5B5%5D.png?imgmax=800" height="337" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="EnableMacros" width="519" /></a> </li>
<li>Fill in all grey marked fields (names of players, questions, answers). The other fields are protected so no mistakes can be made by accident. </li>
<li>Some questions require .jpg images or .flv flash movies. The names and resolution of the files are put next to the questions. These files should be saved in the same folder next to the DSM .swf flash file manually. </li>
<li>Save the Excel sheet, it is advised to save it in the same folder as the DSM .swf file</li>
<li>Click on the top button ‘Exporteer antwoorden.txt’ to generate (or overwrite) the ‘antwoorden.txt’ file (in the same folder as the Excel file) based on the data provided in the sheet. This file is required by the DSM .swf flash tool. </li>
<li>Click on the top button ‘Print steekkaarten’ to get a print preview of the instruction cards that can be used by the game host. All instructions, questions and answers are clearly put together to print out and use during the play. The cards can be printed out 2 or <a href="http://dl.dropbox.com/u/2328438/DSMSteekkaartenPer4.pdf" target="_blank" title="Example game cards printed 4 per page PDF">4 per page</a> to make it easier to hold them during the game. </li>
</ol>
<br />
<br />
<h2>
Examples</h2>
<br />
I've made two quizzes using the Excel file with questions, images and movies: <br />
<ul> <br />
<li><a href="http://dl.dropbox.com/u/2328438/Quiz1.zip">Quiz1</a> </li>
<li><a href="http://dl.dropbox.com/u/2328438/Quiz2.zip">Quiz2</a> </li>
</ul>
<br />
<br />
<h2>
Extra</h2>
<br />
<ul> <br />
<li>If some changes are required to customize the layout etc, the sheets can be unprotected (no password is used) using the Excel menus. </li>
<li>To convert movie files the freeware tools <a href="http://www.formatoz.com/" target="_blank">Format Factory</a> and / or <a href="http://www.rivavx.com/?encoder" target="_blank">Riva FLV encoder</a> can be used. </li>
<li>To convert / edit the picture files, the freeware tool <a href="http://www.getpaint.net/" target="_blank">Paint.NET</a> can be used. </li>
<li>While exporting the 'antwoorden.txt' file, another file 'DSMData.txt' will be created (since v2.0). This 'DSMData.txt' file can be imported on a Windows Mobile pocket pc to get a very user friendly interface to control the flash quiz on a pc. For this, it will be required to install <a href="http://www.google.be/search?q=salling%20clicker%203.5.0.860">Salling Clicker</a> on the PC and Windows Mobile device. Next this <a href="http://dl.dropbox.com/u/2328438/DeSlimsteMens.zip">Salling Clicker 'De Slimste Mens' script</a> needs to be added in Salling Clicker. A new item will be available in Salling Clicker on the Windows Mobile device. I've put <a href="http://www.dropbox.com/gallery/2328438/1/DeSlimsteMens?h=31a227" target="_blank">some screenshots online</a>. </li>
</ul>
<br />
<br />
<br />
<h1>
(Dutch – Nederlands)</h1>
<a href="http://dekompagnie.be/" target="_blank" title="De Slimste Mens - Jonathan Huyghe">Jonathan Huyghe</a> heeft een <a href="http://dekompagnie.be/DSM3x4.zip" target="_blank" title="De Slimste Mens v3 - 4 spelers">mooi flash programmaatje</a> gemaakt om je thuis het populaire Vlaamse <a href="http://www.een.be/programmas/de-slimste-mens-ter-wereld" target="_blank" title="De Slimste Mens Ter Wereld - één">TV spel ‘De slimste mens ter wereld’</a> te laten spelen.<br />
Omdat ik het nogal omslachtig vond om het spel op te zetten, heb ik een Excel template gemaakt om alle voorbereidingen te vergemakkelijken en wat duidelijker te maken. Deze Excel laat toe om het bestand ‘antwoorden.txt’ te genereren met de juiste syntax. Ook kunnen steekkaarten afgeprint worden met alle instructies, vragen en antwoorden die nodig zijn tijdens het spelen van het spel.<br />
<br />
<b>Update 01/2016</b>: Een alternatieve versie voor het opzetten en spelen van eigen 'De Slimste Mens' (volledig losstaand van deze hierboven beschreven) is nu beschikbaar. Deze is volledig online beschikbaar. Deze kan volledig via de website <a href="http://deslimstemens.nu/" target="_blank">http://deslimstemens.nu</a> opgesteld en gespeeld worden. Dit werkt zeer vlot en hiervoor dienen dus ook de onderstaande instructies en Excel bewerkingen niet langer voor opgezet worden, volg gewoon de eenvoudige instructies op de <a href="http://deslimstemens.nu/" target="_blank">website</a>.<br />
<h2>
Handleiding:</h2>
<ol>
<li>Download dit <a href="http://dl.dropbox.com/u/2328438/DSM3x4.zip" target="_blank" title="DSM flash v3x4 + Excel template voorbereiding">zip bestand</a> met alle nodige bestanden van Jonathan Huyghe en de <a href="http://dl.dropbox.com/u/2328438/DSM-Voorbereiding.xlt" target="_blank" title="Excel template ‘DSM-Voorbereiding.xlt’">Excel template ‘DSM-Voorbereiding.xlt’</a>. </li>
<li>Open het bestand ‘DSM-Voorbereiding.xlt’ met Excel en sta het uitvoeren van macro’s toe.<a href="http://lh3.ggpht.com/_OiMAwW7QI68/TPbBkvmWzNI/AAAAAAAAEdE/VP0qpeAa3Tk/s1600-h/EnableMacros%5B15%5D.png"><img alt="EnableMacros" border="0" src="http://lh4.ggpht.com/_OiMAwW7QI68/TPbBlcUQxJI/AAAAAAAAEdI/yG--AUVezxU/EnableMacros_thumb%5B7%5D.png?imgmax=800" height="323" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 5px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="EnableMacros" width="505" /></a> </li>
<li>Vul al de grijze velden in (namen spelers, vragen, antwoorden). De andere velden zijn normaal beschermd zodat deze niet per ongeluk kunnen gewijzigd worden. </li>
<li>Voor sommige vragen is het nodig om .jpg afbeeldingen en .flv flash filmpjes beschikbaar te maken. De namen en de resoluties die hiervoor moeten gebruikt worden, staan aangegeven naast de vragen. Deze bestanden moeten manueel in de zelfde map als het DSM .swf bestand geplaatst worden. </li>
<li>Sla het Excel bestand op. <strong>Belangrijk hierbij</strong>: sla het bestand op als een <strong>Excel Macro-enabled bestand .xlsm,</strong> en niet als een standaard Excel .xlsx bestand, anders zullen de macro’s voor het exporteren verloren gaan. Het is aangeraden om het bestand in dezelfde map als het DSM .swf bestand op te slaan.</li>
<li>Klik op de bovenste knop ‘Exporteer antwoorden.txt’ om het bestand ‘antwoorden.txt’ automatisch aan te maken (of te overschrijven). Dit bestand zal in dezelfde map als het Excel bestand geplaatst worden. Het bestand ‘antwoorden.txt’ is nodig voor de werking van het .swf programma DSM. </li>
<li>Klik op de bovenste knop ‘Print steekkaarten’ om een print voorbeeld te krijgen van de steekkaarten. Deze steekkaarten kunnen tijdens het spel gebruikt worden en geven duidelijk de instructies, vragen en antwoorden aan. De steekkaarten kunnen per 2 of <a href="http://dl.dropbox.com/u/2328438/DSMSteekkaartenPer4.pdf" target="_blank" title="Voorbeeld steekkaarten 4 per blad PDF">per 4 per blad</a> afgedrukt worden om ze gemakkelijker vast te houden tijdens het spel. Eventueel kan hiervoor de gratis <a href="http://www.cutepdf.com/Products/CutePDF/writer.asp" target="_blank">CutePDF printer</a> gebruikt worden om naar PDF te printen <a href="https://www.dropbox.com/s/1budygjab1dayqu/CutePdf4sheetsPerPaper.png" target="_blank">met 2 of 4 per blad</a>.</li>
<li>Open het bestand ‘DSM3x4.swf’ met Internet Explorer om het spel te starten, volg vervolgens de instructies op de steekkaarten.</li>
</ol>
<br />
<h2>
Voorbeelden</h2>
<br />
Zelf heb ik 2 quizzen gemaakt, met de Excel met vragen, afbeeldingen en filmpjes: <br />
<ul> <br />
<li><a href="http://dl.dropbox.com/u/2328438/Quiz1.zip">Quiz1</a> </li>
<li><a href="http://dl.dropbox.com/u/2328438/Quiz2.zip">Quiz2</a> </li>
</ul>
<br />
<h2>
Extra</h2>
<br />
<br />
<ul> <br />
<li>Als er wijzigen nodig zijn in de layout enz. kan het nodig zijn om de bescherming van de Excel tabbladen te verwijderen. Dit kan eenvoudig via de Excel menu’s (er is geen wachtwoord gebruikt in de beveiliging). </li>
<li>Voor het omzetten van de filmpjes kunnen de gratis applicaties <a href="http://www.formatoz.com/" target="_blank">Format Factory</a> en / of <a href="http://www.rivavx.com/?encoder" target="_blank">Riva FLV encoder</a> gebruikt worden. </li>
<li>Voor het omzetten / bewerken van afbeeldingen, kan de gratis applicatie <a href="http://www.getpaint.net/" target="_blank">Paint.NET</a> gebruikt worden. </li>
<li>Bij het exporteren van het bestand 'antwoorden.txt', zal nu ook een bijkomend bestand 'DSMData.txt' aangemaakt worden. Dit bestand kan geïmporteerd worden op een Windows Mobile toestel om zo een zeer eenvoudige bediening van het volledige spel toe te laten. Hiervoor moet op de computer en de Windows Mobile PDA wel <a href="http://www.google.be/search?q=salling%20clicker%203.5.0.860">Salling Clicker</a> geïnstalleerd worden en vervolgens moet dit <a href="http://dl.dropbox.com/u/2328438/DeSlimsteMens.zip">Salling Clicker 'De Slimste Mens' script</a> toegevoegd worden. Een nieuw item zal beschikbaar zijn in Salling Clicker op het Windows Mobile toestel. <a href="http://www.dropbox.com/gallery/2328438/1/DeSlimsteMens?h=31a227" target="_blank">Enkele schermafbeeldingen.</a> </li>
</ul>
<br />
<br />
Update 2/12/2010 v1.4: Extra validation added for field lengths. Status bar message for export added. Added import functionality. <br />
<br />
Update 6/12/2010 v1.5: Added example quiz questions. <br />
<br />
Update 17/01/2011 v2.0: Added export for Salling ClickerUnknownnoreply@blogger.com31tag:blogger.com,1999:blog-4275131957855627760.post-23775825739221984572010-11-29T11:49:00.012+01:002010-12-03T10:23:57.243+01:00Personal expense sheet<p>With the formula I presented in my previous <a href="http://mytselection.blogspot.com/2010/11/excel-lookups-in-fomulas.html" target="_blank">blogpost on Excel look-ups</a> I created an <a href="http://dl.dropbox.com/u/2328438/RekeningOverzichtTemplate.xltm" target="_blank">Excel sheet to monitor my personal expenses</a>. <br />It is based on the data I extract from my different online banking systems (currently for the Belgian banks <a href="http://www.argenta.be" target="_blank">Argenta</a>, <a href="http://www.landbouwkrediet.be" target="_blank">Landbouwkrediet</a> and <a href="http://www.kbc.be" target="_blank">KBC</a>). </p><br /><p>In the 'Categories' sheet, different keywords linked to the category name need to be filled in, this could be the name of a company or the account number. The keyword will be looked up in the account and comment of each transaction. And this will make it possible to automatically categorize each bank transaction and automate some analysis/summary overview on the transactions.</p><br /><p>Every now and then, I copy the data from the online banking system into the 'data' Excel sheets. All expenses are categorized automatically with my formula in the column 'Auto Type', but if some exceptional category needs to be assigned, a 'Manual Type' column can be set to override the 'Auto Type'. Based on these categories, I created some sheets with an summary overview for each month and some averages per month and per year. This way, I can get a clean overview on our expenses and incomes and keep a copy of our banking data offline as well (since many banks only keep last 2 years online).</p><br /><br /><p>Based on my personal sheet I created <a href="http://dl.dropbox.com/u/2328438/RekeningOverzichtTemplate.xltm" target="_blank">this empty (dutch) template sheet</a>, but it will still need some custom changes to be usable for someone else. But it could be a nice starting point. </p>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4275131957855627760.post-87025603684374902582010-11-25T11:16:00.036+01:002015-04-23T13:21:07.392+02:00GIT usage (for an SVN user)I put together some stuff about git I found interesting. <br /> <h2>Installation</h2> <br /> <p>To get started with git on Windows, you'll probably want to download <a href="http://code.google.com/p/tortoisegit/downloads/list" name="TortoiseGIT download" target="_blank">TortoiseGIT</a> (and this requires <a href="http://code.google.com/p/msysgit/" name="msysigt" target="_blank">msysgit</a>)</p> <br /> <h2>Setup</h2> <br /> <p>To use git, a username and email need to be configured. This can be done in of TortoiseSVN -> Settings -> Git -> Config.</p> <br /> <h2>Migration from SVN</h2> <br />A very good manual to migrate your existing SVN repository to GIT can be found in this <a href="http://www.jonmaddox.com/2008/03/05/cleanly-migrate-your-subversion-repository-to-a-git-repository/" name="SVN to GIT migration manuel" target="_blank">blog post of Jon Maddox</a>. <br /> <h2><a href="http://stackoverflow.com/questions/871/why-is-git-better-than-subversion" target="_blank">Good to know</a></h2> <br /> <p>Git tracks content not files <br />Many revision control systems provide an 'add' command that tells the system to start tracking changes to a new file. Git's 'add' command does something simpler and more powerful: git add is used both for new and newly modified files, and in both cases it takes a snapshot of the given files and stages that content in the index, ready for inclusion in the next commit.</p> <br /> <p>Most SCM systems use Delta Storage systems - they store the differences between one commit and the next. Git does not do this - it stores a snapshot of what all the files in your project look like in this tree structure each time you commit. This is a very important concept to understand when using Git.</p> <br /> <p>There is only one Git Directory per project (as opposed to one per subdirectory like with SVN or CVS), and that directory is (by default, though not necessarily) '.git' in the root of your project.</p> <br /> <p>Git is much faster than SVN.</p> <br /> <p>In SVN, each file & folder can come from a different revision or branch. At first, it sounds nice to have this freedom. But what this actually means is that there is a million different ways for your local checkout to be completely screwed up.</p> <br /> <p>You have to tell SVN whenever you move or delete something. Git will just figure it out.</p> <br /> <p>Branches are cheap and easy to merge, so this is a good way to try something out.</p> <br /> <p>A single git repository can maintain multiple branches of development. The 'master' branch is a default branch that was created for you automatically. The 'git checkout branchname' command will switch between branches. The command 'git merge branchname' will merge changes from another branch in the current active branch.</p> <br /> <p>The 'pull' command performs two operations: it fetches changes from a remote branch, then merges them into the current branch.</p> <br /> <p>'git commit' commits locally, whereas 'git push origin master' pushes the master branch to the remote named 'origin').</p> <br /> <p>Git adds complexity. Two modes of creating repositories, checkout vs. clone, commit vs. push... You have to know which commands work locally and which work with "the server" (I'm assuming most people still like a central "master-repository").</p> <br /> <p>Git is MUCH better suited if some developers are not always connected to the master repository.</p> <br /> <p>Even if you don't have commit rights for a project, you can still have your own repository online, and publish 'push requests' for your patches. Everybody who likes your patches can pull them into their project, including the official maintainers.</p> <br /> <h2>Drawbacks of Git:</h2> <br /> <ul> <li>it's much harder to learn, because Git has more concepts and more commands. <br /> <ul> <li>many Git commands are cryptic, and error messages are very user-unfriendly</li> </ul> </li> <li>revisions don't have version numbers like in subversion </li> <li>you have to have a full copy of the repository, you can't work on partials </li> </ul> <br /> <br /> <h2>Git branching model</h2> <br /><a href="http://nvie.com/posts/a-successful-git-branching-model/" target="_blank">A very good post on git branching model</a> <br /><a href="http://github.com/downloads/nvie/gitflow/Git-branching-model.pdf" target="_blank">Pdf model overview</a> <br /> <ul> <li>Central repo with 'origin/master' and 'origin/development' branches (infinte lifetime) <ul> <li>'origin/master' branch reflects the production release</li> </ul> </li> <li>Supporting branches <ul> <li>feature branch: branches from development and merges into development, branch name different from master, develop, release-* or hotfix-*, exist in developers repos only, not in central origin </li> <li>release branch: branches from development and merges into development and master, branch name release-*, all feature branches must be merged in development before release branch is branched off </li> <li>hotfix branch: branches from development and merges into development, master and release, branch name hotfix-*</li> </ul> </li> </ul> <br /> <br /> <h2>Central Repo</h2> <br />If you like to setup a git central repository on Windows (for example to replace your <a href="http://www.visualsvn.com/server/" target="_blank">VisualSVN</a> environment), you can follow <a href="http://code.google.com/p/tortoisegit/wiki/HOWTO_CentralServerWindowsXP" target="_blank">this manual</a>. <p>Update 23/04/2015: A good tutorial and Git related info can be found on <a href="https://www.atlassian.com/git/tutorials" target="_blank">this Atlassian site</a>.</p> Unknownnoreply@blogger.com1