Excel verify Text format and convert number to text

Posted on July 11, 2015 by Wyman W

This Excel tutorial explains how to verify Text format in a column. If it is a Number then convert Number to Text. You may also want to read: Excel verify Number format and convert Text to Number Excel verify Text format Three kinds of Cell contents are considered as Text 1) Non-number - such as alphabet, symbol 2) A mix of number and non-number 3) Number with small triangle on the top left of the Cell Number can be in two forms in Excel - Text and Number. If a Number is a ...

Excel VBA DIR Function

Posted on July 2, 2015July 2, 2015 by Wyman W

This Excel tutorial explains how to use Excel VBA DIR Function and loop through workbooks in a folder. You may also want to read: Excel loop workbooks in folders and subfolders with FileSystemObject Excel VBA DIR Function DIR Function returns the first file name that matches the criteria in Function argument (pathname and attributes). DIR Function is usually used with loop in order to manipulate multiple files of specific name. Syntax of DIR Function Dir(pathname, [attributes]) ...

Excel export Excel to PDF with VBA

Posted on June 27, 2015March 31, 2019 by Wyman W

This Excel tutorial explains how to export Excel to PDF with and without VBA, how to export all worksheets to one PDF, and export all worksheets to separate PDF. You may also want to read: Select multiple worksheets using Excel VBA How to Convert Excel to PDF (non VBA) Excel export Excel to PDF (non-VBA) In Excel, you can easily export Excel to PDF by navigating to File > Save As, in the Save As Type, you can save a file as different file types. Below is the available types in E...

Excel consolidate all Comments in the workbook

Posted on June 26, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to consolidate all Comments in the workbook using Macro. You may also want to read: Excel show all comments in worksheet Excel delete all comments in Workbook Excel consolidate all Comments in the workbook Previously I wrote a post about how to show all Comments on the Cell (unhide it), in this tutorial I will show a Macro that consolidate all comments in the workbook. VBA Code - consolidate all comments Public Sub consolComments() counter = 1 ...

Excel show all comments in worksheet

Posted on June 23, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to show all comments and hide all comments in workbook. You may also want to read: Excel consolidate all Comments in the workbook Excel delete all comments in Workbook Excel show comments in worksheet Comment is a yellow dialog box that you can write comment regarding specific Cell. To insert a Comment, right click on a Cell and then select Insert Comment When we insert a Comment into a Cell, there would be a red triangle on the top right of ...

Excel Macro remove space in prefix and suffix

Posted on June 10, 2015September 10, 2015 by Wyman W

This Excel tutorial explains how to use Excel Macro to remove space in prefix and suffix in all worksheets. Excel Macro remove space in prefix and suffix Most recently I performed data validation on Excel spreadsheet in which the data contained a lot of spaces in prefix and suffix. I guess this happened because they delimited the data string using space. For example, seperating first name from full name. Suffix with space is not possible to identify by eye, but I had to remove all spaces ...

Excel loop workbooks in folders and subfolders with FSO

Posted on May 5, 2015March 26, 2021 by Wyman W

This Excel tutorial explains how to loop workbooks in folders and subfolders with FileSystemObject (FSO). You may also want to read: Excel VBA Dir Function to loop through workbooks in folder Add password to all Excel workbook in folder Excel VBA search text in multiple Workbooks in folder Excel loop workbooks in folders and subfolders FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, since we are discu...

Excel find all external links and broken links in workbook

Posted on April 13, 2015December 26, 2016 by Wyman W

This Excel tutorial explains how to find all external links and broken links in workbook using Find and Replace, Relationship Diagram, Macro. You may also want to read Excel VBA Workbook LinkSources Method Excel VBA refresh closed workbook Excel find all external links and broken links in workbook There are several ways to find external links and broken links in workbook, some are easy to use but have limitations, I will talk about each method in this article. Note that this article ...

Excel VBA refresh closed workbook

Posted on April 11, 2015May 29, 2017 by Wyman W

This Excel tutorial explains how to refresh a closed workbook. You may also want to read: Excel VBA refresh all Pivot Table or Pivot Cache Excel VBA AskToUpdateLinks Property Excel refresh closed workbook First of all, to refresh closed workbook involves opening the workbook, refresh and then close it, it is impossible to refresh closed workbook without opening it, but we can open a workbook by vba without seeing it physically opened. Second, ask yourself what kind of refresh you ...

Excel timer countdown for test using Application.OnTime

Posted on April 4, 2015September 10, 2015 by Wyman W

This Excel tutorial shows how to make Excel timer countdown for test using Application.OnTime You may also want to read: Excel VBA Application.OnTime Method to schedule event with timer Send Email in Excel VBA using CDO and OLMailItem Excel timer countdown for test using Application.OnTime If you are going to job interview, you may be asked to do an Excel test. I have been thinking it would be awesome to make a countdown in the worksheet to tell the interviewee how much time is left. ...