My 100th publication here at Experts Exchange is an article that presents a program called ArticlesVideosEE, which is able to download the Title, Views, Endorsements, and Points for each article and video whose link is in a list. You may read the article here:

How to download number of Views, Endorsements, Points for Experts Exchange Articles and Videos

This video Micro Tutorial is a supplement to that article, providing a demonstration of the program.

1. Download and install AutoHotkey

If you do not already have AutoHotkey installed, read this EE article:

AutoHotkey - Getting Started

Follow the instructions in that article to download and install AutoHotkey.

2. Download the ArticlesVideosEE program

If you have not yet downloaded the ArticlesVideosEE program, read my EE article:

How to download number of Views, Endorsements, Points for Experts Exchange Articles and Videos

Download the ArticlesVideosEE program attached at the bottom of that article.

3. Create a plain text file with the URLs of your EE articles and videos

Following the instructions in my article, use Notepad (or whatever text editor you prefer) to create a file with the URL list (each article/video URL on a separate line).

4. Run the ArticlesVideosEE program

In Windows/File Explorer (or whatever file manager you use), double-click the ArticlesVideosEE.ahk file to run it.

The above works because a standard installation of AutoHotkey (done in Step #1) associates the .AHK file extension with the AutoHotkey program.

Respond to the Browse For Folder prompt to navigate to the folder where you want the Results file stored.

Respond to the Browse For File…

Data was imported into Access from Excel. In part 1, we examined it, and learned how to do a few things in datasheet view like filtering, sizing columns, and showing totals. To get unique values, we'll make a query and then change it to a Totals query, and use an aggregate function to calculate something. We'll use Group By, Max, and Expression.

Here are the other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

Shuffle - 4 - Append Query
https://www.experts-exchange.com/videos/61037/Shuffle-4-Append-Query.html

1. To change a query to an aggregate query, click 'Totals'

in the Query Setup group of the Design ribbon tab

2. Group By the key field in the Total row

3. Change Group By to Max for other fields

4. Create an Expression with Count(ID) for the number of records

We have a Totals query, and change it to an Append Query, so we can use it to add records. Create an an expression for a calculated field. Get errors (oops!) when we run the append query, but see how to fix them :)

To Summarize, in part 1, we examined data and made a query. In part 2, we changed the query to a Totals query to get unique values and used an aggregate function in an expression. In part 3, we took a deep dive into table design. Now, we have created and run an append query. Records are now in the customers table, and we have two more tables
to put records in before the import from Excel is done. Look for those lessons in the future.

Here are other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 2 - Totals Query
https://www.experts-exchange.com/videos/61035/Shuffle-2-Totals-Query.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

1. Open a table in Datasheet view to look at information

2. Click the Filter dropdown at the top of a column to specify criteria for the rows displayed

3. Resize columns to be wider or narrower, double-click to best-fit

4. Add new records at the bottom

5. Look at each column and determine what type of object it is.

      There needs to be a table to describe each type of object.

Airtable is an online database application that's great for small and medium-sized companies. This video shows you how to move data between Airtable and Excel.

This video takes you through the steps to create dropdown validation menus in an excel spreadsheet.  For the purposes of the video, Microsoft Excel 2016 was used.

A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access.

The declaration statement defines the function name and its return data type. The values it needs to calculate are specified as parameters, or arguments.

Create an error handler that will exit without the user realizing there might have been a problem, and define a default value of zero for the function return value.

See how to open Excel and refer to it so code in Access can use it. Reuse the Excel application object to make susbsequent processing faster ... so, how do we clean that up?

Use the function in a query! Part 1 shows a query with the calculated column that calls this code:
Wrapper-1-Query-Show-result-of-Excel-Worksheet-Function-in-Access-Query.html

Even though topics may be technical, I do my best to consider that a beginner may be watching, so extra detail is added. Hopefully not so much that more advanced users turn away ... maybe you will comment and let me know your thoughts about this? Thanks!

have an awesome day,
crystal

1. Dimension oExcel as object at top of module

2. Name of function follows the "Function" keyword

3. Enclose function parameters in parentheses

4. Declare data type of return value

5. Set up error handler

For more information on the error handling, here is a short video: Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html

6. Define default return value

7. Validate parameters -- not done in this example

8. See if Excel is loaded and ready to use

if not, then open Excel in the background and use oExcel to represent it

9. Call the Excel function you want and pass parameters

10. Assign the result to the function return value

How can you see what you are working on when you want to see it while you to save a copy?

Add a "Save As" icon to the Quick Access Toolbar, or QAT.

That way, when you save a copy of a query, form, report, or other object you are modifying, you can still see what you are doing and can give your object a better name.

While Access is used to demonstrate,  you can do this in other Office products like Word, Excel, and PowerPoint too.

have an awesome day,
crystal

1. click on down arrow at end of QAT to Customize it

2. Choose "More Commands..." from the drop-down menu

3. choose to show 'All Commands'

4. click on the last command in the list of QAT commands on the right

   or whatever command you want to add the command below

5. double-click <Separator> in the left list to add it to the right list below what is selected

6. in the left list, doube-clicm Save As to addit below what is selected (now the separator)

7. click OK to close the Customize the Quick Access Toolbar dialog box

8. Enjoy faster saving!

Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac Newton by several years in publishing his work. Actually, it was Jacob's brother Johann Bernoulli (1667-1748), an academic who made sense of Leibniz's notes and was primarily responsible for getting Leibniz's "Calculus" published. In fact, it was Johann that coined the term "Integral" (Leibniz called it sums), and may have even been responsible for the calculus symbology. If the name Bernoulli sounds familiar and you're an engineer, it's probably Johann's son Daniel Bernoulli you recall.

For over 300 years, mathematicians and statisticians have used the Binomial Distribution to group and model real world observations and is the basis of many other statistical distributions.

Here are my spreadsheets if you want to take a look at them:
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153243/N-16-BBD.xlsm
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153244/N-35-BBD.xlsm
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153245/N-40-BBD.xlsm
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153246/N-64-BBD.xlsm

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custom styles in multiple workbooks.

To follow along with this tutorial, you can use your own files or download the files I use in the video:
www.flisser.com/classfiles/excel-styles.zip
When you want to format cells in Microsoft Excel, you can do it manually, by selecting fonts, font color and size, background colors and borders, or you can do the formatting quickly and automatically using styles. If you used styles in other programs, you’ll be familiar with the concept: a style is a mixture of formatting that you can apply over and over, like paint.

There are two advantages to using styles:

• Speed. When you have a lot of cells to format, it’s faster to apply a style than to apply all the formatting features individually. And if you need to change a formatting feature of all the cells – like just the color or the font – changing the style definition will immediately update the cell formatting.

• Consistency. When formatting a lot of cells, it’s easy to make a mistake and select a slightly different color or font size. But when you apply styles, the exact, same formatting gets applied every time.
• Excel has built-in styles that you can use, and you can also modify them and create your own.

…

This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel.

Part 1 of this series discussed basic error handling code using VBA.
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Part 2 went in depth on how the VBA  to copy values to blank cells works, and how to loop.
http://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html

Although helpful, it is not necessary to watch parts 1 and 2 before this lesson.

This lesson runs code to see what it does and then breaks working code so we can explore errors.  We run and fix, debug, compile, use and not use Option Explicit, step through code while it is running, look at the watch window to see values of variables, set and clear breakpoints, stop, continue running, and learn how debugging and error handling work.

01. For a list of macros, press Alt-F8

   When you are in an Excel Workbook, press Alt-F8 for a list of Macros.

02. To go to VBA, press Alt-F11

   When you are in an Excel Workbook, press Alt-F11 to go to the Visual Basic Editor (VBE) where you can write Visual Basic for Applications (VBA).

03. To watch variable values, press Ctrl-W

   When you are in VBA code, press Ctrl-W to open the Watch window and set expressions to watch the value of.  If a variable name is highlighted when Ctrl-W is pressed, it will be filled in the Expression.

04. Stop

   Add a Stop statement to the code to cause the code to stop on that line when it runs.

05. To single-step, press F8

…

This Experts Exchange lesson shows how to use VBA to loop through rows in Excel.  In order to sort, filter, and use database features, there needs to be a value in each column for every row. When data arrives with values missing, code to copy values where it is blank can be run.

This lesson shows the manual process to fill blanks, and the VBA code to implement it.

This is Part 2 in a 3-part series to discuss error handling in Excel.

Part 3 of this series will add errors to the code and then show how to fix them.  Part 1 of this Experts Exchange series suggested basic error handling code.

01. Set up the error handler

   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Dimension Variables

   Declare variable names and data types that will be used in this procedure.

03. Initialize Variables

   Initialize the values of variables that will be used in this procedure.

04. Calculate Variables

   Calculate the values of variables that be determined.

05. Give User a Chance to Back Out

   Issue a message box to the user and allow them to stop the process.

06. Determine the Last Row

   Determine the last row on the worksheet that needs to be written to.

07. Loop through each Row of Data and Save or Write Value

   Loop through each row and either save the value that is there, or write the last value saved if a value is needed.

08. Continue Looping until Done

   Continue looping until all values in the specified column are written.

09. Exit Code

   After the procedure code, a line label for the exit code (Proc_Exit: ) is used to signify what happens at the end of the procedure. This is code to gracefully exit.

10. Error Handling Code

…

In a previous video Micro Tutorial here at Experts Exchange, I explained how to get a free, one-month trial of Office 365, which provides the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. The previous tutorial ended at the point of downloading the installer for the Office 2016 desktop modules for Windows. This new tutorial goes through the installation process for those applications.

1. Run the downloaded installer

Using Windows/File Explorer (or whatever file manager you prefer), locate the downloaded installer for the Office 2016 apps that are included as part of the Office 365 Home subscription. The name may vary depending on your operating system, but it will look something like this:

Setup.<lots of other characters here>.exe

Run it (usually, via a double-click, but that depends on your file manager and settings) and then click the "Run" button on the "Security Warning" dialog.

2. Accept the User Account Control dialog

Depending on your User Account Control (UAC) settings, you may or may not get the UAC dialog. If you do, click the "Yes" button.

3. Wait until all Office 2016 apps are installed

Although it says, "We'll be done in just a moment", grab a cup of coffee.

4. Check for the Office Tools shortcuts

Check to make sure that the installer created a "Microsoft Office 2016 Tools" program group, with two shortcuts in it.

5. Check for the Office shortcuts

Check to make sure that the installer created shortcuts for all of the Office 2016 apps. It does not…

This Micro Tutorial will demonstrate how marketers working with data will be able to use VLookups because it is the best technique to handle data sets.