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, 2015January 9, 2016 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: Access VBA DoCmd.OutputTo Method Access VBA DoCmd.TransferSpreadSheet Method Access Export all Queries to Excel 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...

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

Peoplesoft HCM Business Unit, SetID, TableSet

Posted on July 16, 2015August 8, 2015 by Wyman W

This Peoplesoft HCM tutorial explains Business Unit, SetID, TableSet. Peoplesoft HCM Business Unit, SetID, TableSet SetID If you look at the Table fields through Query Manager, you will find many Peoplesoft Tables contain SetID field. Tables that have SetID field include Location, Jobcode, Department, Salary Admin Plan, Employee Class, etc. SetID is a grouping for Business Unit in order to restrict or grant access to data within a table. If SetID contains only one Business Unit, the SetID ...

Excel VBA Worksheets.Copy Method to copy worksheet

Posted on July 16, 2015January 26, 2017 by Wyman W

This tutorial explains how to copy worksheets using Worksheets.Copy Method in Excel VBA, and demonstrate how to copy worksheet to another workbook. You may also want to read: Excel VBA Worksheets.Add Method to add new worksheet Excel Workbooks Open Method and check if workbook open Excel VBA Consolidate worksheets into one worksheet Excel VBA Worksheets.Copy Method to copy worksheet Excel VBA Worksheets.Copy Method is to copy worksheet in a workbook. You can copy worksheets from on...

Excel VBA Application WorksheetFunction Property

Posted on July 13, 2015 by Wyman W

This Excel tutorial explains how to use Application WorksheetFunction Property in Excel VBA. Excel VBA Application WorksheetFunction Property Excel VBA Functions and Excel Worksheet Functions are slightly different. Sometimes even though the Function names and syntax are the same, the results are different. There are many nifty Worksheets Functions but unfortunately they are not present in VBA, and vice versa. In order to fully utilize all Functions under any environment, we can do the f...

Excel Cell Function to check Cell format, address, contents

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

This Excel tutorial explains how to use Cell Function to check Cell format, address and contents. You may also want to read: Excel ColorIndex Property Excel verify Number format and convert Text to Number Excel Cell Function to check Cell format, address, contents Excel Cell Function is a very powerful Function that tells various information of a Cell. Cell Function has a list of arguments regarding Cell information. Syntax of Cell Function CELL(info_type, [reference]) [referen...

Excel VBA Copy Range and Paste Method

Posted on July 12, 2015June 2, 2017 by Wyman W

This Excel VBA tutorial explains how to copy Range and paste data using VBA Excel VBA Copy Range Method Range.Copy Method is a very convenient Method to copy and paste Range to destination in one line of code. All the formatting will be copied and pasted. You can copy a Range, a column, a row. Syntax Range.Copy(Destination) Destination is optional. If not specified, Excel copies the Range to the Clipboard. In some cases, we may just want to copy to Clipboard and then use Paste/PasteSp...

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

Access SQL WHERE Clause

Posted on July 6, 2015 by Wyman W

This Access tutorial explains how to use SQL WHERE clause to add criteria using AND OR keywords. You may also want to read: Access SQL SELECT, SELECT TOP, SELECT INTO Access SQL WHERE Clause In my previous post, I explained how to use SELECT statement to select desired fields. After selecting fields, the next step is to filter data. If no record in the table can match the criteria, no result will return. Access SQL WHERE Clause with "AND" Criteria Syntax For single criteria SELEC...