Qlikview | SQL Server | Power BI | Excel
This post demonstrates how to find files in a folder and subfolders that match a user-entered search string. The user is prompted to select a folder, then enters a search string (file name or partial file name). The VBA code then searches each folder and subfolder to find any files that match the string. Finally, … Read more
Excel VBA Find String in Range – This post shows three examples of how to use the Excel VBA Find method, searching a range of cells for a specific string. This is a good technique to employ if a user would like to see where a list of strings appear in a workbook. The samples will … Read more
Earlier this week, I received a request to provide a quick and easy way to rename a list of files in a specific folder. Each month, the user gets a bunch of PDF files placed in this folder, then needs to manually rename each file for downstream use. As you can imagine, this is a very … Read more
Last week, I encountered a situation where one of my developers needed to populate an Excel Template with 100+ values from a SQL query using a C# program. There was an existing template in place that receives these values for a report distribution. Issues were coming up when users began making updates (inserting rows, deleting … Read more
In some of my VBA projects, I need to loop through a range of cells and insert a blank row between groups of data, based on a value change in a specific sorted column (Excel dataset is sorted by the respective column). This gives the user a clear visual look at data groupings, and allows them … Read more
In many of my VBA projects, I need to loop through a range of cells and break up a large dataset into smaller datasets, typically moving the subsets of data to their own worksheets. Many times, the range of data I need to loop through is the distinct list of items in a particular column. … Read more
This post details a VBA macro to list all files that exist in a given folder, and will loop through and get the files in each subfolder until all have been examined. A comment was made on the original post , but I decided to create a new post to cover this topic. The original … Read more
The following post (Excel VBA – Import SQL Server Data) will demonstrate how to establish a connection to SQL Server, then pull data into your Excel Worksheet. Further, I will show how to setup a couple buttons for clearing the data in Excel and executing the SQL command to import refreshed data. This is a simple … Read more
The following macro (Excel VBA Loop Worksheets) will demonstrate how to loop through each worksheet of a given workbook using a couple different types of loops. Further, I will show how to select a range of cells from a table, and then build new worksheets based on unique values within that range. In the following examples, I copy … Read more
I was working on a report which gets exported from a Qlikview application. To add some finishing touches, I needed to evaluate a range of cells, formatting the cell based on the cell value. In cases where the range of cells was small, this could easily be done manually. However, when you are dealing with … Read more