Qlikview | SQL Server | Power BI | Excel
This request came from a subscriber who was looking for a macro to rename files in a given folder, and make a copy of the renamed file within the same folder. Once the files in the top level folder were processed, then cycle through each subfolder and do the same. The following macro will accomplish … Read more
This post demonstrates an Excel VBA macro to count the number of instances of a specified character within a range of cells. The macro prompts the user to input a character, then will loop through each cell and accumulate a count of instances the specified character appears. The final count is then displayed in a … Read more
This post will demonstrate a simple VBA macro to shade background colors for alternate rows in a specified range. This can come in handy when you need to reset the alternate colors for different files, or if you are looking to test out different combinations and want to cycle through them quickly. Version of Excel: … Read more
This post details a VBA macro to search for files based on an search string, then lists all files that have a partial match in the file name. The macro will search a specified folder (user prompted to select base folder), and will then loop through and search each subfolder until all have been examined. … Read more
The following post will detail how to compare the contents of two arrays (two-dimensional) and highlight cells in the update table where there are differences. The use case revolves around comparing and updated table against the original to see where cell values have changed. If a given value has indeed changed, the macro will highlight … Read more
The following post will compare the contents of one array against the contents of another. The use case revolves around taking a detail list and comparing each row against a master list, to make sure each row of the detail array exists. If the detail record does not exist, the cell background color of the … Read more
The following post provides an example of how to save an Excel file as a PDF Document, using VBA code. The macro first prompts you to enter a file name, then opens a second dialog window to pick the destination folder to save the PDF Document. There is also an option to determine which tabs … Read more
I received a question on post Excel VBA Rename Files and thought a separate post was warranted to cover the answer. In the following example, I will setup an Excel file to do the following: Allow user to pick a folder and list out the file content (file path and name) Allow user to pick … Read more
The following post illustrates how to split a delimited list of values in a cell, using a VBA module. I will demo a simple example where I received a semi-colon delimited list of email addresses, and I wanted to split them up into individual cells. I use this VBA code frequently, and figured it would be … Read more
The following post illustrates how to create a custom function in Excel, using a VBA module. In particular, I will create two functions that parse a cell containing a full name (First and Last) into two separate cells. I frequently need to run through this process, so I figured it would be a good way … Read more