Access Excel VBA copy workbook or file

Posted on January 15, 2017 by Wyman W

This Access Excel VBA tutorial explains how to copy workbook or file. You may also want to read: FSO File Methods Worksheets.Copy Method to copy worksheet Access Excel VBA copy workbook or file There are two common ways to copy workbook or file FSO.CopyFile Method FileCopy Function I have explained how to use different FSO Methods in my previous post, click to see more details. Using FSO.CopyFile Method to copy workbook You should be able to run FSO in Excel 2013. If you fa...

Sort Data in Access Report

Posted on January 9, 2017September 21, 2017 by Wyman W

This Microsoft Access tutorial explains how to sort data in Access Report, including sort data in custom order. You may also want to read: Add Auto number in Access Query Add sorting in Access Table field Sort Data in Access Report In Access Report Design View, you can sort data at the Group, Sort, and Total section, in this tutorial I will explain how to sort data in Detail and sort Grouped data. Example Suppose we have the below table that contains employee data. employee_tbl ...

Create Matrix Report in Access Report

Posted on January 3, 2017May 1, 2019 by Wyman W

This Microsoft Access tutorial explains how to create Matrix report in Access Report using Crosstab Query and Expression. You may also want to read: Access Crosstab Query Excel VBA Convert Text in Columns and Rows into Matrix Table Access Matrix Report Matrix Report is like an Excel Pivot Table, where you can place fields in row and column, and then apply Aggregate Functions on a field value such as Count, Sum, Max. In newer version of Microsoft Access, Pivot Table function has been...

Access Crosstab Query

Posted on December 29, 2016May 1, 2019 by Wyman W

This Microsoft Access tutorial explains how to create Crosstab Query and demonstrate how to add grand total. You may also want to read: Create Matrix Report in Access Report Access replace Crosstab Query with Expression Excel VBA Convert Text in Columns and Rows into Matrix Table Access Crosstab Query Crosstab Query is a Matrix Report, the most commonly known Matrix Report is Excel Pivot Table, where you can place fields in row and column, and then apply Aggregate Functions on a fi...

Access Update Table Records

Posted on December 27, 2016 by Wyman W

This Microsoft Access tutorial explains how to update table records from another table or query. Access update table records You can always update table records manually by viewing the table and then enter the value. With Update Query, you can update multiple table records that meet defined criteria, or update table records from another table. Note that you may update data in an Access table, but you cannot update data in linked table. For example, if your Access table is linked to Excel sp...

Access Excel capitalize first letter in a sentence

Posted on December 19, 2016March 17, 2019 by Wyman W

This Access Excel tutorial explains how to capitalize first letter in a sentence using VBA custom Function. Capitalize first letter in a sentence In Excel spreadsheet, there is a Function called Proper, which converts all the first letter of each word to capital letter. Example Using Proper Function he is a boy. she is a girl. He Is A Boy. She Is A Girl. he is a boy. his name is John. mary is a girl. He Is A Boy. His Name Is John. Mary Is A Girl. Mr. smith is happy ...

Access VBA change Report Record Source

Posted on December 18, 2016December 18, 2016 by Wyman W

This Access tutorial explains how to change Access Report Record Source using VBA in order to modify Report criteria automatically. Change Report Record Source When you create an Access Report, you can specify the data source of the report in Report Record Source, which can be a Query or Table. Type the Table / Query name in the Record Source as below. To add criteria to the Report, there are three ways Method 1 - Write SQL Type the full SQL statement directly in Record S...

Access Excel remove text in brackets

Posted on December 11, 2016December 28, 2016 by Wyman W

This Access Excel VBA tutorial explains how to remove text in brackets using custom Function. You may also want to read: Change text color in brackets Extract text in brackets Access Excel remove text in brackets I was inspired to create this post because the HR system I am using (Workday) is very annoying, some data fields are actually a combination of two different fields, the additional data field is displayed in brackets in the prefix. For example, an employee has a English name ...

Access delete table records

Posted on November 5, 2016 by Wyman W

This Access tutorial explains how to delete table records and delete records that exist in another table in Microsoft Access. Access delete table records You can always modify or delete table data manually by viewing the table and then press the delete button in keyboard. With Query, you can delete specific rows of record that meet the criteria. Note that you may delete data in an Access table, but you cannot delete data in linked table. For example, if your Access table is linked to Excel spr...

Access Excel VBA Array

Posted on October 24, 2016May 6, 2018 by Wyman W

This VBA tutorial explains how to create and use VBA Array in Access and Excel VBA. You may also want to read: Store unique array item with Scripting Dictionary Access Excel VBA Array In Excel worksheet, you can convert a formula arguments to Array by pressing CTRL+SHIFT+ENTER, click here to see example. In VBA, Array is a variable that can store multiple data of the same data type. There are two types of Array in VBA One-dimensional Array Two-dimensional Array For most...