Access Excel VBA delete folder if folder exists

This tutorial explains how to use Access / Excel VBA to delete folder if folder exists already. Access Excel VBA delete folder if folder exists Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder. In order to delete a folder, you can u...
More

Excel VBA delimit Cell value into rows

This Excel tutorial explains how to delimit cell value into rows using VBA and non VBA. You may also want to read: Convert cell contents into table Access Excel custom split Function to delimit text Excel VBA delimit Cell value into rows To demonstrate how to delimit Cell value into rows, I use email address as an example. One day I tried to copy a list of email addresses from Outlook into Excel.   When I paste to Excel 2013, all email addresses are pasted to a singl...
More

Excel VBA sort worksheet tab

This Excel VBA tutorial explains how to sort worksheet tab in alphabetic order (ascending order or descending order). You may also want to read: Excel Range Sort Method to sort data Use VBA Excel Function to sort data in ascending order Access Excel assign number for custom sorting sequence Sort worksheet tab by name When you have a lot of worksheets in a workbook, you may want to sort them in alphabetic order for easy searching. As a behavior of Excel sorting, when an arr...
More

Excel VBA consolidate multiple workbooks into one workbook

This Excel VBA tutorial explains how to consolidate multiple workbooks into one workbook. You may also want to read: Excel VBA Consolidate worksheets into one worksheet Excel VBA combine worksheets columns into one worksheet Consolidate multiple workbooks into one workbook Assume that you have a lot of workbooks, each workbook contains multiple worksheets. You may want to copy all worksheets of each workbook into a master workbook. For example, I have a folder called "sample" under...
More

Excel VBA reformat merged cells to row

This Excel tutorial explains how to use Excel VBA to reformat merged cells to row. You may also want to read: Fill Blank Rows or blank Cells Delete Blank Rows Seperate line break data into different rows Fill Blank Rows or blank Cells Excel VBA reformat merged cells to row It is difficult to decide the title of this post, I will illustrate with an example. Imagine a Table with column A being a merged cell, and column B are the items under the same group. When you apply...
More

Excel VBA generate non-duplicated random number

This Excel VBA tutorial explains how to generate non-duplicated random number using Excel VBA. You may also want to read: Generate random letter A to Z Generate random HKID Hong Kong ID Excel VBA generate non-duplicated random number In my previous post, I demonstrated how to assign random number to a value using Rank Function and Rand Function. In this post I will introduce a more direct approach to assign a random number with the help of VBA. In this approach, each time after a r...
More

Add password to all Excel workbook in folder

This Excel VBA tutorial explains how to add password to all Excel workbook in folder and how to mass remove password. You may also want to read: Excel VBA protect worksheet with password Excel VBA hide worksheet with password (xlVeryHidden) Protect VBA code with password Add password to all Excel workbook in folder In many organizations, employees are required to add password to Excel before sending workbooks through email, or before putting workbooks in network drive. If you have ...
More

Excel VBA combine worksheets columns into one worksheet

This Excel VBA tutorial demonstrates how to combine worksheets columns into one worksheet. You may also want to read: Excel VBA Consolidate worksheets into one worksheet Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet There are two types of combine worksheets Worksheets have the same structures / headers, create a blank worksheet and paste the data from different worksheets down the row Worksheets have different ...
More

Excel VBA compare worksheets

This Excel tutorial explains how to use Excel VBA to compare worksheets. You may also want to read: Excel compare Worksheets using Compare File Excel compare worksheets Compare worksheets is to compare worksheets of similar structures but different values. Excel has a built it function called Compare File in Office Professional Plus 2013 or Office 365 ProPlus. After comparison is complete, two worksheets are compared side by side and you get the below result indicating all the difference...
More

Excel VBA export Excel to Word

This Excel VBA tutorial explains how to export Excel to Word. You may also want to read: Export Excel to PDF Excel VBA export Excel to Word In the previous post, I demonstrated how to export Excel to PDF, which is very easy because there is already a built in function to do that (using Save As and choose PDF). However, there is no built in function to export Excel to Word. Fortunately Excel cell is actually a table in Word, we can simply copy the cells and paste to Word. This tutorial ex...
More