Excel Days360 Function to calculate day difference

Posted on October 3, 2016October 4, 2016 by Wyman W

This Excel tutorial explains how to use Excel Days360 function to calculate day difference between two dates. Excel Days360 Function You can easily calculate the number of days between two dates by a simple deduction using Excel formula, however when you try to calculate the number of years between two dates, most people simply divide the day difference by 365. However, because every month has different days, and due to leap year, dividing the difference by 365 is totally incorrect. Excel D...

Access Mid Function to extract substring

Posted on September 26, 2016 by Wyman W

This Access tutorial explains how to use Access MID Function to extract substring from a string. Access MID Function MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string. Other than Access, Mid Function can also be used in Excel worksheet, Excel / Access VBA. Access MID Function is commonly used with Instr Function (...

MS Word VBA add footer to all word documents in folder

Posted on September 25, 2016September 21, 2017 by Wyman W

This MS Word VBA tutorial explains how to add footer to all word documents in a folder automatically. MS Word VBA add footer to all word documents in a folder A couple of years ago, my manager asked me to add the keyword Public / Confidential / Restricted to all Word documents footers to meet compliance requirement (this is a common practice), I painfully did it manually for hundreds of documents. In fact it could have been done automatically. In some of my previous posts, I have demonstrate...

Excel VBA convert cell contents into table

Posted on August 5, 2016 by Wyman W

This Excel VBA tutorial explains how to convert cell contents into table using Exel VBA. Excel VBA convert cell contents into table Yesterday when I tried to copy a table from a website to Excel, all the table contents are pasted into a single cell instead of copying the whole table. I believe there are workarounds to copy table but since this is an Excel blog, I want to write a Macro to convert cell contents back into a table. Previously I also wrote a post to convert cell contents into ro...

Excel currency converter template

Posted on August 4, 2016August 4, 2016 by Wyman W

This Excel tutorial explains how to create a currency converter template to convert one currency to another currency. Excel currency converter template In my previous post, I have demonstrated how to get currency exchange rate using VBA custom function and get the exchange rate via website. However I didn't make a convenient currency converter template to convert one currency to many different currencies or convert many different currencies to a single currency. In this tutorial, I will show y...

Excel world time converter template

Posted on August 1, 2016August 4, 2016 by Wyman W

This Excel tutorial explains how to create a world time converter template in Excel. Excel world time converter template There are a lot of websites that allow you to enter time of one country and convert to time of another country, or even convert to time of multiple countries. These websites work absolutely fine with the exception that they do not allow you to save your searched result. It is absolutely time wasting to select your desired country from the country list of hundreds of countrie...

Excel automatically refresh pivot table

Posted on July 19, 2016 by Wyman W

This Excel tutorial explains how to automatically refresh pivot table using Macro / VBA and Pivot Table Options. Excel automatically refresh pivot table You may have a pivot table that links to data in the same workbook or external data, and you may want to refresh pivot table automatically in order to see the most updated data. In this tutorial I will show several ways to automatically refresh pivot table. Method 1 - Automatically refresh pivot table when opening the file This is the simple...

MS Access SQL select MAX record of each group

Posted on July 19, 2016 by Wyman W

This MS Access tutorial explains how to use Access SQL to select MAX record (MAX row) of each group. Access SQL select MAX record In Microsoft Access, you can select fields you need to Group and then use MAX to get the max record of each Group. However, the result only returns the grouped fields but not all the fields in the original table. In this tutorial I will show two methods to select all fields. Example - Select Max data Table1 below stores the performance rating of each employee in d...

Access Excel VBA delete all workbooks in folder

Posted on June 2, 2016 by Wyman W

This VBA tutorial explains how to delete all workbooks in folder using Access VBA and Excel VBA. Access Excel VBA delete files in folder 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 files, you can use FSO Fil...

Access Excel VBA delete folder if folder exists

Posted on June 1, 2016June 2, 2016 by Wyman W

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...