Fast Excel Development Template v4.4

We have a new version of the Fast Excel Development Template. These are improvements arising from our team using this template to build dozens of planning tools and systems. It has been 15 months since the last update and we have a lot of things to share.

Much of this work has been done with dedication and programming brilliance by my friend and colleague: Gabriele Tettamanzi. We owe him a lot – version 4.4 of the Fast Excel Development Template is so much better due to his hard work and smart problem-solving.

There are a number of improvement areas, so here is a summary to get a context and then we will dive into the details.

Here is the download page for the Fast Excel Development Template v4.4.

Summary of Improvements to The Template

There have been a number of changes to make the automation work faster and more reliably. Version 4.3 had some limitations, particularly when running the automation with workbooks that contain a lot of data (30mb+). Version 4.4 has tools to ensure each workbook runs smoothly with substantial reduction in the risk of file corruption. As a result, it is more reliable than ever before. We will show you some examples and share best practices in using the template, particularly with larger systems. We will also show how to safely upgrade tools and modules that have been built in an older version.

The template has been made compatible with PowerQuery text queries. This means you can choose from “Get Data > Legacy Wizards/ From Text (Legacy)” (MS Query) or “Get Data > From File / From Text/CSV” (PowerQuery). There are pros and cons with each and we will talk about them. PowerQuery is very powerful and the template is much better for supporting it.

PowerShell

Another “Power” to you. Windows PowerShell is an automation and configuration management program. It is intended to replace the command prompt CMD.exe as the way to provide instructions to the operating system. PowerShell is the best way to do things that are outside the Office environment. Now there is a set of PowerShell tools, including a core function that means the whole of PowerShell is available from Excel. A more advanced topic, but I’ll show you some practical examples, such as stacking files to get an inventory history.

We have been looking ahead to see what is coming in terms of changes to Excel and Windows. The Template has been updated to be compatible with some of these. Changes include: reduced C-Drive access in Windows 11; Deprecation of VBScript and possible loss of FileSystemObject (FSO); Add-In Conflict with Windows 11; BAT, CMD and a move to PowerShell. Some of these are more technical and may not affect you today.

Finally, there are new features! Relatively small, but useful. They include: Stacking text files (without repeated headers); Bulk copying and conversion of files using a table; cleaning up formulas.

Detailed Tutorial on Using The New Template

How to Ensure Reliable Performance with No-Code Automation

The Fast Excel Development Template has had no-code automation since version 4.0, released October 2010. This means that you can build out your Excel workbook, sheet by sheet, using each template sheet. Query, Table, Stack, Pivot. Then, you can run the automation to build the VBA routine without ever having to go into the VBA Editor.

Since version 4.2, released October 2022, there is a new template: ModuleList. This allows you to build and run a system of modules, each module in its own workbook. Again, without needing to write any VBA code yourself. This is the no-code automation in action.

There is now a Quick-Key to toggle automation on and off: CTRL-Shift-A. You can now run the workbook automation without having to go into the Menu sheet and the “Switch Automation Off/On” button.

Even though the template now supports modular systems with multiple workbooks, the best practice is to run automation with one workbook open at a time. This is especially true if you have a lot of data in one or more workbooks. “A lot” is defined as 100k+ rows or multiple tables with 30-50k rows in it. However, the methods used to run the automation have been overhauled and made much more reliable, even in the cases where this practice is not followed.

One of the advantages of building with modules is that you can clear the data out, save and then run programmatically. This will close the workbook afterwards without saving so it remains as fresh as the day that it was born. Consequently, we do not generally have issues with workbooks acting as modules once they are complete. However, when you are developing, it is sometimes the case that the workbook has calculation sheets with lots of data and you use the automation.

There are a few known problems with using VBA to write VBA. The main one is “cruft” which is code junk that accumulates in the workbook file. If this builds up, then it can increase the risk of file corruption and crashes in the future. Particularly with large workbooks.

There is now a “Reset VBA Modules” button (scroll down on the Menu sheet) which helps keep the workbook free from corruption and the kind of problems that might crash an Excel file from using VBA to write VBA.

Another performance gain in running the Update macros comes from a better use of setting calculations to manual, especially for Stacks, and then returning to the original setting at the end of the routine. This is automated and applied for every run.

One more change to improve performance: You can now use the binary .xlsb format for modules. Binary Excel files are leaner and do not have the xml content clutter that is in a usual Excel file. It is stored in binary, so has a smaller file size (about 50%), faster run (about 20%) and is more stable as there is less to get corrupted. If you have modules that have a lot of data then it is worth considering saving them as .xlsb. The default is still .xlsm but if you add the .xlsb file extension to the filename in the ModuleList template then it will work for binary files.

How to Upgrade An Existing Workbook to the New Development Template

This assumes that the existing workbook has been built with an older version of our Fast Excel Development Template. There is no way to apply the template to a workbook that has been built without following the Fast Excel Development Method. All template function depends on good discipline: headings in row 10, controlled content in rows 6:9, continuous tables of data, no space sheet names and so on.

This process will work for any workbook developed with a Template 4.0 and above. It should work for workbooks built with Template 3.0 but it has not been tested extensively. Be aware and never try this upgrade without Save-As first.

Currently, you need to perform this process inside the VBA editor. This process is not included in the “No-Code” operations that are possible with the template.

The template now has full support for using PowerQuery to create text file queries. As you may have noticed, in previous tutorials we have tended to use the “Legacy Wizard” to do text file queries. The Legacy Wizard uses the older Query method, MS Query.

If you have Excel 2016+ and do this: Data > Get Data > From File > From Text/CSV (or Data > From Text/CSV) then you are using PowerQuery.

If you have Excel older than 2016 OR do this: Data > Get Data > Legacy Wizard > From Text (Legacy Wizard) then you are using MS Query.

MS Query is simpler and faster for simple queries, both to build the query and to refresh. PowerQuery is much more powerful, but has a learning curve. It can be more sensitive to changes in input data. We will be sharing some more content on this difference. Showing you how to do the same thing in each and also some examples of what PowerQuery can do that MS Query cannot. PowerQuery can replace some calculation tables (for example, exploding tables) and make development faster.

The main reason we have used MS Query is that some versions of Excel (before 2016) does not have PowerQuery included so we show the same method that everyone can use. However, it is getting more popular and the percentage of users who have older versions of Excel without it is getting smaller and smaller. Meanwhile, there are ever more advantages in using PowerQuery the current query method.

There are some differences in how PowerQuery works and we have made the template supports it. There are some small changes to the named ranges on the Query sheet. You may not even notice and it will work the same as before.

The main change is that the “Update Text File Locations” function now supports text queries created in PowerQuery. So, if you want to switch to a new file location for your input files and you have PowerQuery text queries then this will work in the same way. Make sure you have files ready in the new location- one for every Text Query sheet. The function will tell you what is missing but will not make the change until all is present and correct.

Clear or Leave Data in Query

There is one new parameter, PARA_Keep_Query_Data. If this is set to zero, then every time the query sheet runs, it will first delete the query table. Previously, the new query just overwrote the old one so it had the same effect. But if there is no file to refresh then in the old version, the data remained in the workbook. you may or may not wish for this. There are good reasons for either option so we give you the choice.

For example, I often develop tools to be shared as a single Excel file. If the user has access to the inputs then it will refresh, otherwise it still needs to run with something and the existing data is good enough to show a result. In this case, set PARA_Keep_Query_Data =1 and the old data will remain in the query table.

On the other hand, sometimes a file that is absent means “no data”. And your calculations will recognise this possibility. In this case, set PARA_Keep_Query_Data =0 and you will have a blank table.

How to Use PowerShell from the Fast Excel Development Template

There is a VBA Module called PowerShellTools. This is certainly not a no-code feature so might be a little more advanced topic. PowerShell is a command line program. You type instructions that follow code syntax into a terminal. For those of us who grew up with point-and-click, this is a very different way of interacting with a computer but we get an idea that many things are possible with the command-line.

Essentially, anything that you want to do outside the Office environment will need the help of a program like PowerShell. Our other methods for doing this were: .BAT (batch files), VB Script and FSO methods, some of which have an uncertain future and are described below in the compatibility section.

The key innovation in the new Fast Excel Development Template is a highly flexible function to run any PowerShell code from VBA. Let’s take an practical example and I’ll describe the old and new methods and show you how they worked and why the PowerShell method is better.

As you know, we have a Stack Template. It has a maximum of 4 tables. So if you need more, you need to stack a stack. This works, say for up to 16 tables. Then you need a stack of a stack of a stack …you get the point. For >16 tables this is not practical. Plus, if the number of files to stack is always changing, then you can’t create new queries every time you have a new file to add. So, we look for alternative ways to stack files or tables. If you have a lot of tables to stack, it is likely that they are identical in structure (same headings) and produced from another system as text files. So, rather than bringing in each file into a Query template and then building multiple stacks, we can do it much faster.

Why would you want to stack a lot of text files? One useful application is creating an archive. Let’s say you want to keep an inventory history. Most MRP systems do not do this as standard. But it can be very useful for analytics. If you run your Fast Excel planning system every day, then you can output the onhand inventory as a text file with a datestamp in the filename and a third column in the table. Output it into an archive folder. Then, if you stack all these files together you have a daily inventory history for every item. But you will have 365 files for every year of deployment. So we need a way to stack an ever-growing list of text files into a single file.

The Old Way of Stacking Text Files – Batch Files

In the older version of the template, we had a Template called StackFiles_Bat. It would be helpful if this meant a little flying mammal came to help us with data manipulation. But actually this is a template for a .BAT file or Batch file. So, you had the VBA copy this code, save it as a text file with .BAT extension, and then run it in windows using a Shell command in VBA, which ran it in CMD. It was a very clever technique discovered by Dave Williamson it worked very well. Super fast (1000+ files in seconds).

The problem with this method, like many of the other issues below relating to compatibility, is security. IT Managers do not like users running things like .BAT files as it is a security risk. There has been a recent vulnerability “BatBadBut” that highlight this risk. So, you can be sure that any method that relies on BAT files is facing an uncertain future and may already be prohibited for some Admin Policies.

The New Way of Stacking Text File – PowerShell

The PowerShell tools are in a VBA module called: PowerShellTools. No surprises there. There are two functions: StackFiles and StackFilesTable. The first is older, faster but repeats headers in the stack. The second one is newer and slightly slower (by a few more seconds for 100+ files) and has a cleaner output with the only headings at the top of the output table and everything below is data.

Each of these functions takes a folder, a file type and a name for the output file. It will stack everything in the folder that fits the file type (CSV, txt) and output a file in the Data folder or somewhere else you specify. A much cleaner way to stack text files and it stays on the right side of good security practice by using PowerShell.

If you were to look into the code, each of these functions uses another function called “PS_Execute”. This is a wrapper which means you can use VBA to “wrap” around a block of PowerShell code. The “PS” stands for “PowerShell” and not “Production Scheduling”.

The StackFiles function uses this twice with a line of code in each one. Well, actually total of 3 lines as it links two together with a “|” character. The StackFilesTable function uses it twice and deploys a total of 6 lines. There is no limit and you could use this PS_Execute to run complex programs in PowerShell. In practice, for longer programs and other applications it might be easier to create a .ps1 file. For instance, if you want to run a Fast Excel System on autopilot and have it as a scheduled task, running a ps1 PowerShell file from Microsoft Task Manager is the best way.

But if you want to run PowerShell commands from VBA then the PS_Execute function is very useful and the StackFiles are a good example.

Other PowerShell Functions

There is also a function to copy entire folders (CopyDirToDir) and the old way uses FileSystemObject (discussed below). And ListFiles which produces a list of files in a given location; the old way also used .BAT.

VBScript

Visual Basic Scripting Edition (VB Script) is being deprecated and support will be removed from Windows, starting with Windows 11.

This is NOT the same as Visual Basic for Applications (VBA). VBA will be continued to be supported for the foreseeable future on all versions of Excel running on the desktop (as opposed to Excel on the Web, where it has never been supported).

The impact to the Fast Excel Development Template from VB Script going away is limited. The main dependency in the older templates is with something called FileSystemObject (FSO). We used FSO for a small number of functions in the template versions 4.3 and earlier. In version 4.4 this has been removed and we are working with newer methods of doing the same thing. If it doesn’t mean much then you can ignore this as functions give you the same result, just a slightly different method.

C Drive Access (And any FilePath that is User Dependent)

Another new Parameter is PARA_User_Folder. This is dynamic and should update upon opening the file. The User Folder is the standard root for both Documents and OneDrive. C:\Users\<User Name>\Documents and C:\Users\<User Name>\OneDrive – <Org Name>

So if you have source files in a folder location that is different for different users, you can build the filepath in a way that will update when the new user opens the workbook.

For example, if the network location PARA_FilePath_Data_Netwk is a OneDrive folder, then the address might be something like this:
C:\Users\Kien Leong\OneDrive – PS\Planning\Data

As you can see, this is specific to my Windows account.

I can now build the filepath using the named range as the user dependent bit and a string for the rest of it, as you see in Figure 7.

Our standard approach for the local data folder is to use the root of the C drive. This is precisely because it is universal for all windows users and the document folder is user-dependent, usually C:\Users\<User Name>\Documents.

This method assumes access to the C Drive. In Windows 11, depending on the version, user account role and security settings, there are more restrictions on creating folders and files directly in C Drive. Microsoft wants to keep users in the User Folder.

To anticipate this, it is possible to use the similar technique with the User_Folder to set the local filepath PARA_FilePath_Data_Local dynamically, using a similar method.

Currently, you would still need to use the “Update Text File Locations” function to change the filepaths- this is not done automatically. WE did consider doing this, but it adds a lot more burden to the template, requires files to be set up and opens up a lot more questions for beginner users. But you could add it to a master routine, or even on Workbook_Open if you are confident to useit and know that the files will exist in that location.

CMD and .BAT Batch files

As described above, PowerShell removes the need to use CMD and .BAT. These functions are not going anywhere, but there are security issues with them and it makes sense to use the best practice for working with files and functions outside the Office environment.

How to Use the New Functions in the Fast Excel Development Template v4.4

PowerShell Stack Text Files -without repeating headers.

There has been a function called StackFiles since v4.3. This takes a filepath, filename and file type and will stack all text files in that location and type, outputting the results in a new text file, named accordingly. This is a raw stack- very fast with one downside is that the headers are repeated. It is great for stacking more than 4 files and can work on hundred of text files in seconds. This is left unchanged.

There is a new function called StackFilesTable with the same parameter arguments and it does the same thing, except the headers are removed for every file after the first one. It is slower with a cleaner result.

A new function FileCopyFromTable allows you to list a number of files in various target locations and copy them in bulk to various other locations. It will convert between these formats: .txt, .csv, .xlsx, .xlsm, xlsb, xls.

The input is a named range that represents a 2-column table with the full filepath and file name where you want to copy from and to.

Remove Redundant Sheet Names in Formulas that Reference the Same Sheet

Perhaps you noticed that in Excel, when you write a formula that has all ranges on the same sheet, the formula ranges have no sheetnames in them. Once you navigate to another sheet, the formula will always include “SheetName!A10” even if the range is on the same sheet. This leads to unnecessarily long formula with clutter, especially if sheet names are long. It makes development and debugging harder with more noise in the formula bar.

We now have a macro that cleans this. “Remove_Redundant_SheetNames_In_Formulas” or Ctrl-Shift-L. Try it on a sheet with long formulas and see the difference.

Summary and Download

As ever, you can download the Fast Excel Development Template v4.4 on this page. We will be running tutorials and more content so make sure you are registered and signed up to our mailing list to get alerts. Sign up here.