Access VBA DoCmd.TransferText Method

Posted on January 10, 2016June 12, 2017 by Wyman W

This Access VBA tutorial explains how to import and export Access Objects to text using DoCmd.TransferText Method. You may also want to read Access VBA Export all Queries to Excel Access VBA import txt using DoCmd.TransferText Method Syntax of DoCmd.TransferText Method DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage) Name Required/Optional Data Type Description TransferType Optional AcTextTransferType T...

Microsoft Access VBA Export all Query to Text File

Posted on January 10, 2016January 10, 2016 by Wyman W

This Access tutorial explains how to use Access VBA to export all Query to Text file. You may also want to read: Microsoft Access Export all Queries to Excel Access VBA DoCmd.TransferText Method Export Access Objects to Text (non VBA) There are two kinds of export that we can use to export an Access object to Text file. Assume that we want to export Query1 to Text. Right click on the Query > Export > Text File This page has a check box Export data with format...

Enable and Disable Protected View in Excel

Posted on January 9, 2016January 30, 2017 by Wyman W

This Excel tutorial explains how to enable and disable Protected View in Excel and other Office applications. Protected View in Excel Protected View feature is used to protect your computer from virus when you try to open Excel workbook from the Internet or other unsafe locations, or open attachment in Outlook email. The protection is achieved by disabling some features and making the Workbook read only. Protected View is a feature that you can enable or disable in Option. When the Protecte...

Microsoft Access Export all Queries to Excel

Posted on January 8, 2016June 1, 2016 by Wyman W

This Access tutorial explains how to export all Queries to Excel. You may also want to read: Access VBA auto generate mass report by group to Excel Microsoft Access Export all Queries to Excel Assume that you have created three Queries in Access database, all you want to export them to Excel. Press ALT+F11 > insert a Module > paste the below code Public Sub export_Excel() Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb() For Each ...

Microsoft Access UNION and UNION ALL

Posted on January 6, 2016October 26, 2016 by Wyman W

This Access tutorial explains how to use UNION and UNION ALL in Access Query. You may also want to read: Excel VBA Union Method UNION and UNION ALL in Access UNION and UNION ALL are used to combine two Query results vertically into one Query, therefore both Queries require to have the same number of fields and same data types. When two Queries are combined, there could be rows of record that are exactly the same (duplicates). UNION removes all duplicates while UNION does not remove an...

Excel find the last row using Worksheet formula

Posted on January 4, 2016 by Wyman W

This Excel tutorial explains how to find last row using Excel worksheet formula. Excel find the last row Finding the last row in Excel is very important especially for creating dynamic data Range, which can be used for setting dynamic Print Area, dynamic Data Validation list, dynamic data source for Pivot Table, etc. When you google this topic, most article talks about how to find the last row using VBA but not the non-VBA solution. In my previous post I have also detailed different way...

Excel set default font

Posted on January 3, 2016January 30, 2017 by Wyman W

This Excel tutorial demonstrates how to set default font type in Excel. Excel set default font This is a quick tutorial to show you where to set default font type in Excel. You probably need to know this after you upgrade to a new version of Excel. I will demonstrate how to do so in Excel 2013, click here for 2010 version. Click on FILE tab Click on Options on the left menu Under the General page, you will find the default font and font size. Select the font you w...

Excel dynamic Data Validation list

Posted on January 3, 2016 by Wyman W

This Excel tutorial explains how to create dynamic Data Validation list that automatically adjusts the data Range. You may also want to read: Excel Dynamic Print Area Excel dynamic data range Excel graph dynamic data range Create Excel Data Validation list Excel Data Validation list restricts users to select values from a drop down box. When the input is different from the predefined list, an error message will pop up. Before we create a dynamic Data Validation list, let's brief...

Run Access Macro using RunCode

Posted on January 2, 2016September 16, 2017 by Wyman W

This Access tutorial explain how to run Access Macro using RunCode. You may also want to read: Run Excel Macro from Access VBA Run Access Macro Assume that you have already written a Sub Procedure helloWorld(). To run the Macro, you can press ALT+F11 and run the Procedure by pressing F5. Alternatively, go back to the Access Windows, double click on the Module on the left hand side and run the Procedure by pressing F5. If you are writing a Macro for users, they pro...

Excel Share Workbook

Posted on January 2, 2016January 9, 2016 by Wyman W

This Excel tutorial explains how to use Share Workbook function to allow multiple users to open the workbook at the same time. Excel Share Workbook Share Workbook function allows multiple users to open the workbook at the same time. Assume that we have a staff list workbook that is placed in the network share drive. Click on REVIEW > Share Workbook Check the box "Allow changes by more than one user at the same time. This also allows workbook merging" &gt...