Clearly and Simply

Category: Tools

  • Microsoft Excel Project Calendar Template

    A ready-to-use Microsoft Excel Template for a dynamic Project Calendar, displaying regular Project Meetings and Deadlines for one selected week 

    Project Calendar Template IntroAs per the subtitle of this blog (see above), Clearly and Simply is about data analysis, modeling, simulation and data visualization.

    If you are a long time reader of this blog (and I am aware there are only a handful), you know that – from time to time – I also post something about Project Management.

    I already published posts here about

    Following this tradition, today’s post provides another little piece out of my Project Management Toolbox: a ready-to-use, dynamic Project Calendar Template in Microsoft Excel. Of course, including the template for free download.

    (more…)

  • Excel VBA Read and Write Performance Test

    What is the fastest way to read values from and write values to an Excel worksheet with VBA? And how much faster is it?

    Stop Watch / Photographer: Leo Reynolds (flickr.com)The most common and most effective recommendations to speed up slowly running VBA code in Excel are the following four:

    1. Turn off the screen updating while the code is running

    2. Set Excel’s calculation mode to manual while the code is running

    3. Don’t select (activate) objects (ranges, charts, shapes, etc.)

    4. Optimize the way of reading values from and writing values to the worksheet. Instead of looping through a range, read or write the entire block of data in one go with one single statement.

    Well, you have seen those before, right? Nothing new under the sun.

    However, how much can you speed up your code with these best practices?

    Well, for the screen updating and the calculation mode, it depends on how your specific workbook looks like. The more complex the display is (i.e. how much rendering Excel has to do to update the screen?) and the more and more complicated formulas you have in your workbook (i.e. how long does one recalculation take?), the higher the performance gain will be. Thus, it is hard to provide universally valid numbers on the potential performance increase.

    With regards to the avoidance of Select statements and the method of transferring data between Excel and VBA, however, a more or less generic test environment is possible.

    Today’s post provides a small Performance Test Tool to demonstrate how much faster your VBA code can be, if you are using the best method to read data from or write data to an Excel worksheet using VBA. As always, the article comes with the Excel workbook for free download.

    (more…)

  • List of all Files in a Folder and its Subfolders

    How to create a list of all filenames inside a specified folder and its subfolders including additional information like file types, sizes, dates and path lengths in Microsoft Excel

    List of all filenames in a folder and its subfolders - click to enlargeFrom time to time, I am taking the liberty to post something totally off topic (see here or here), i.e. something that has nothing to do with data analysis and data visualization.

    Today’s short article belongs to this category.

    A couple of weeks ago, I ran into a problem with my offline backup software. The application threw a few errors, reporting it could not backup a couple of my documents. The issue was easy to find: the path of those files exceeded the maximum path length of the Windows API (260 characters).

    It wasn’t so easy to fix, though, because the error log file of my backup software isn’t very helpful. I needed something to easily identify the files with a path length exceeding the 260 characters limitation, so I could shorten the folder and file names.

    Hence, I wrote a little tool which automatically creates a list of all files inside a specified folder and all its subfolders, including the file names, the paths, the file types, the dates (created, last modified, last accessed) and the lengths of the path and filename. This list sorted descending by path lengths made it easy to identify the files my backup software couldn’t handle.

    According to this Microsoft article in the Windows Developer Center, the maximum length limitation shall disappear in Windows 10, version 1607. The root cause for creating this workbook and code may go away soon, but I assume the little tool of easily getting a list of filenames inside a specified folder may be helpful on other occasions, too.

    If you are interested, here it is for free download:

    Download retrieve filenames from folder and subfolders (zipped Excel 2010 – 2016 workbook, 34.2K)

    Download, unzip and open the tool, enable macros, click on the import icon at the top of the sheet, select a folder and wait until the code is finished (the status bar at bottom left shows the progress).

    Please be advised that the code is not optimized for performance. Importing the ~41,000 files in my document folder took ~6 minutes on my machine. Not really fast, but since I am not doing this on a daily basis, it is good enough in my book.

    I hope this will be helpful for someone else, too.

    More posts on data analysis and visualization will come soon.

    Stay tuned.

  • Export Excel Dashboards (Improved Version)

    An improved version of the example workbook exporting Excel Dashboards to other applications

    Improvement - Photographer: Celestine Chua (flickr.com)The previous post provided an example Excel model with the option to export dashboards to other applications and file formats.

    The workbook came with one serious limitation: the PDF and XPS export routine created a separate file for each dashboard, instead of one file with all dashboards.

    In the article I stated, I wouldn’t see how to get around this without using an external PDF creator. It turns out that I simply couldn’t see the wood for the trees.

    It was (again!) Leonid Koyfman, long-time reader and contributor, who pointed me into the right direction. The idea is as simple as it is efficient: create a temporary Excel workbook, copy all dashboards to it (each dashboard on a separate sheet), use the ExportAsFixedFormat method of the workbook object to export the entire workbook (i.e. all dashboards) to one PDF or XPS file and finally close the temporary workbook without saving changes. So simple, so obvious. I should have figured that out on my own. But I didn’t. Many thanks to Leonid for the idea.

    Here is the improved version for free download:

    Download Export Excel Dashboard to other Applications – Improved Version (zipped Microsoft Excel 2010 – 2013 workbook, 210K)

    Stay tuned.

  • Export Excel Dashboards to other Applications

    How to export Microsoft Excel Dashboards to PowerPoint, Word, PDF, XPS or Pictures

    Export Excel to other ApplicationsEarlier this week I received an email from Andrea, one of my few, but faithful readers. Andrea stumbled across a post from 2010 describing how to export Excel dashboards to PowerPoint and asked if I was planning a follow-up post about exporting from Excel to Microsoft Word.

    Truth be told, not up to that point. I personally barely export from Excel at all, but if I do, I usually export to PowerPoint.

    However, I found Andrea’s idea interesting for several reasons: exporting Excel dashboards to other applications and file formats may indeed be helpful, e.g. for creating reports or papers in Microsoft Word. Furthermore the original implementation exported three different dashboards, but the best use case for a bulk export to another application is an Excel dashboard displaying one selected period out of many or a dashboard with various filter options. In this case it can be a real time-saver if you have the option to export e.g. all weekly reports of a quarter or a defined set of filter combinations in one go. Last, but not least, it is actually no big deal to adjust the original code for other applications and file formats.

    Today’s article provides an example workbook which allows to export the dashboards to PowerPoint, Word, PDF, XPS and some picture file formats (PNG, JPEG, GIF, BMP). As always, the post includes a link to the Excel workbook for free download.

    (more…)