Qlikview | SQL Server | Excel
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
One of the most common functions I use in Excel VBA is to select and copy a range of cells. The following examples will illustrate how to select a range of cells, copy them to another location, and then copy and paste a formula to a desired range. For starters, the following example is based on a … Read more
The following macro will loop through the files in specified folder and list them out in your Excel worksheet, based on a designated file type (i.e. anything with file extension of “XLSX”). The actual VBA code is quite simple and can be copied into your VBA Project (modify to suit your needs). As always, I encourage … Read more
The following macro will define a range of cells, then loop through each cell, interrogating each character. This works well if you are trying to determine how many instances of a given condition exist in each cell. The actual VBA code is quite simple and can be copied into your VBA Project (modify to suit your … Read more