Access Excel extract file name from file path

Posted on December 7, 2015December 26, 2016 by Wyman W

This Excel tutorial explains how to extract file name from file path using VBA and without VBA. Excel Access VBA extract percentage from text Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Excel extract file name from file path Assume that you have a file path such as C:\Program Files\Google\GoogleToolbarNotifier\a.jpg If you want to extract file name a.jpg from the path in Excel, first we need to think about how to de...

Excel Access VBA extract percentage from text

Posted on September 1, 2015April 30, 2019 by Wyman W

This Excel / Access tutorial explains how to extract percentage from text. Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Access Excel extract file name from file path Excel extract text in brackets Excel Access VBA extract percentage from text Recently I got a report that contains percentage but it is mixed with other text. For example, Salary increased by 10% We are unable to do any calculation unless we extract the 1...

Access Excel remove line break and carriage return

Posted on August 22, 2015December 26, 2016 by Wyman W

This Access / Excel tutorial explains how to remove carriage return and remove line break in a text. You may also want to read: Excel VBA separate line break data into different rows Access Excel remove carriage return and remove line break Recently our company uploaded employee data in spreadsheet data to the new system. One user reported that her home address shows some special code (something like hex code) that is supposed to be a space. Since the code appears after a comma, I suspec...

Access VBA DoCmd.TransferSpreadSheet Method

Posted on July 20, 2015August 28, 2017 by Wyman W

This Access tutorial explains how to use DoCmd.TransferSpreadSheet Method to export and import files. You may also want to read: Access VBA DoCmd.OutputTo Method Access VBA auto generate mass report by group to Excel Access Export all Queries to Excel Syntax of DoCmd.TransferSpreadSheet Method TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA) Name Required/Optional Description TransferType Optional The type of transfe...

Access VBA DoCmd.OutputTo Method

Posted on July 19, 2015February 5, 2017 by Wyman W

This Access tutorial explains how to use DoCmd.OutputTo Method and DoCmd.TransferSpreadSheet Method to export different objects to specific file types, for example, export Query to xlsx. You may also want to read: Access VBA auto generate mass report by group to Excel Access VBA DoCmd.TransferSpreadSheet Method Access Export all Queries to Excel Syntax of DoCmd.OutputTo Method DoCmd.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding, Output...

Access VBA auto generate mass report by group to Excel

Posted on July 19, 2015June 6, 2018 by Wyman W

This Access tutorial explains how to auto generate mass report by group, for example, auto generate reports by different department and then export to Excel. You may also want to read: Excel VBA filter value then copy data to new worksheet Access VBA auto generate mass report by group to Excel Assume that we have a staff list of 1000 employees in 20 departments, our goal is to export 20 staff list to Excel, one department for each staff list. There are several ways to achieve this task: ...

Access Excel VBA day month year difference between two dates

Posted on July 18, 2015July 7, 2017 by Wyman W

This Access Excel tutorial explains how to find complete year difference, month difference and date difference between two dates. You may also want to read: Excel DATEDIF Function to calculate date difference Access Excel VBA difference between two dates (year difference, month difference, day difference) In Excel worksheet, there is a Function called DateDif that can calculate difference between two dates in complete year, month or day. In VBA, there is no DateDif Function. There is ...

Access Excel add leading zero

Posted on May 5, 2015December 26, 2016 by Wyman W

This Access / Excel tutorial explains how to add leading zero using Text Function or using custom Function to pad other text in suffix or prefix. Excel add leading zero (non-VBA) Number in Excel can come in two forms - Text or Number. For number in Number format, you may not add zero in prefix For number in Text format, you can add a zero in prefix In order to convert a Number to Text, add a ' in the prefix. For example, instead of typing 00123, type '00123 Sometimes you receive ...

Access Excel FSO File Methods

Posted on May 5, 2015May 5, 2015 by Wyman W

This Access / Excel tutorial explains how to use FSO File related Methods including FileExists, DeleteFile, MoveFile, CopyFile You may also want to read: Loop workbooks in folders and subfolders Access Excel FSO Folder Methods FSO File Methods FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, I will only explain the use of some important File related Methods. For details, please read the MSDN lib...

Access Excel FSO Folder Methods

Posted on May 4, 2015May 5, 2015 by Wyman W

This Access / Excel tutorial explains how to use FSO Folder related Methods including FolderExists, CreateFolder, DeleteFolder, MoveFolder, CopyFolder You may also want to read: Loop workbooks in folders and subfolders FSO File Methods FSO Folder Methods FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, I will only explain the use of some important Folder related Methods. For details, please read...