Access VBA create Query using CreateQueryDef

This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method. Access VBA create Query Sometimes we don't want to create Query in Query Design View, because you may create a Query with criteria depending on a variable. To create Query in Access VBA, you can directly write a SQL statement in Access VBA, and then create a Query using the SQL statement with CreateQueryDef Method. Syntax of CreateQueryDef expression.CreateQueryDef(Name, SQLText) Name Require...
More

Access Excel VBA rename workbook or file

This Access Excel VBA tutorial explains how to rename workbook or file in VBA using Name As. Access Excel VBA rename workbook or file It is very easy to rename a workbook or file in VBA using the below syntax. Name and As are the keywords. Name currentFilePath As newFilePath For example, suppose we have a file called test.xlsx in Desktop, and we want to rename it as test2.xlsx, run the below Procedure Public Sub renameWorkbook()     Name "C:\Users\WYMAN\Desktop\test.xlsx" As "C:\Users\WYMA...
More

Excel Security Warning – Macros have been disabled

This Excel tutorial explains how to solve the problem of Excel Security Warning - Macros have been disabled. Excel Security Warning - Macros have been disabled This Excel Security Warning is one of the most annoying warnings in Excel. If you click on the Options button, you will see the below message. In fact, this message does say something about the reason for this alert, it explained that this error is caused by encrypted Macro. What it means encrypted is that the Workbook contai...
More

Access Excel VBA copy workbook or file

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

Excel VBA combine rows into cell

This Excel VBA tutorial explains how to combine rows into Cell. You may also want to read: Separate line break data into different rows Delimit Cell value into rows Excel VBA combine rows into cell In my previous post, I demonstrated how to delimit a Cell into separate rows,  one example is that you copy a email list and you want to put each email address into different rows. From: To: In this  post I will do a reverse version, I will combine rows back into cell, so t...
More

Sort Data in Access Report

This Microsoft Access tutorial explains how to sort data in Access Report, including sort data in custom order. 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 Employee ID Employee Name Department Team Salary 001 David HR Compensation 10000 ...
More

Excel create Pivot Table using PowerPivot

This Excel tutorial explains how to create Relationship and create Pivot Table using PowerPivot. You may also want to read: Create Relationship in Microsoft Access PowerPivot in Excel In Excel version 2016, PowerPivot is pre-installed and you can see the option in the ribbon tab. For previous versions of Excel, some versions cannot support PowerPivot. If you are not using Excel 2016, I recommend you to google search how you can install PowerPivot in your version, you may need to...
More

Create Matrix Report in Access Report

This Microsoft Access tutorial explains how to create Matrix report in Access Report using Crosstab Query and Expression. 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 removed, you can only create Matrix Report through Crosstab Query, or write expression in Access Report. In this po...
More

Access Crosstab Query

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 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 field value such as Count, Sum, Max. In newer version of Microsoft Access, Pivot Table function has been removed...
More

Access Update Table Records

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