Microsoft Access 365: Link Data to Excel with Macros and VBA

Name: Microsoft Access 365: Link Data to Excel with Macros and VBA
Rating: 4.7 (539 reviews)

Create perfectly formatted, ready to distribute Excel files in seconds from inside Microsoft Access!

Created byBruce Myron

Last updated 3/2021

English

What you'll learn

Create links between Access and Excel that instantly update report data

Course content

4 sections • 20 lectures • 2h 25m total length

Introduction1:54
Important NOTE on DOWNLOADABLE RESOURCES0:19

Database Overview and Export Problems11:28
Learn to link Access data to Excel, run date-based queries, export to Excel reliably, and build a four sheet sales report with data slices and a pivot table.
Modify the Query to Ask for Dates and Add them to the Query Result12:32
Add a Report Dates table and get Customer Totals10:26
Create a report dates table from the orders by date query, compute customer totals for the date range, and automate refreshing the sales by customer table via append and macro.
Modify the Customer Totals table and Add Top Ten Table7:36
Rename subtotal to order total, set subtotal to average and count for order totals, and build a top 10 sales-by-customer table with a macro and warnings control.
Add the Order Summary Table10:05
Create an order summary table from sales data with a make table query using order dates, report dates, year, month, and totals from order products for Excel pivot analysis.
Clean Up the Navigation Pane by Creating a Custom Group9:23
Create a custom navigation group in Microsoft Access, rename it to sales report, and move empty report dates, empty order summary, and related queries into it while hiding unneeded objects.

Correction of Error in Query Used0:29
Create the Excel File and Link to the Data7:56
Link Excel to Access by loading orders by date data, hide the last two of seven columns, and load the data into an Excel table.
Add More Sheets to the Excel File8:19
Add Subtotals and Use the Filters5:40
Discover how to add subtotals and counts in an Excel table using the subtotal function, reference the table column, and apply filters to summarize data.
Add Data Slicing to Our Order Summary Report5:50
Create the Pivot Table and Prepare File for Distribution8:21

Download new database and update the Excel query connections5:22
Create a VBA Function that will Prep the XL File6:37
Create a VBA function in a module with Visual Basic editor to prep an Excel file by declaring string variables for the file, folder, and path from the current project.
Get the NewXLFile Name and Copy the Excel file9:41
Obtain the new excel file name with a vba input box, validate input with nz, and handle blank input. Debug and prepare to copy sales report to the new file.
Add VBA Code to Check Existence of the Files7:07
Verify existence with the Dir function; alert if missing; kill the target file if present; copy to the new Excel file in the same folder.
Open the Copied Excel File, Refresh Connections then Remove Them8:40
Add our new program to the macro and call from a form8:14
Attach a VBA program to a macro, call it from a form button, and run the do orders by date macro to generate sales reports in Excel.

Requirements

A very basic knowledge of Access and Excel

Description

Learn How to Link Microsoft Access Tables to Excel Spreadsheets, then Update the Data Instantly!

This course shows you how to link Access to Excel so you can run a query for any time period and have it instantly update when you refresh ALL of your linked data in multiple Excel sheets. Using the techniques from this course will save you hours of time every time you create your reports!

From a Student -

"I was looking for a course that jumps right into examples and walks through the methodology in Access and Excel. This class does that perfectly. Plus, you get to see Bruce's thought process, which I believe will help in future Access/Excel projects." - Jonathan Sanborn

From another Student -

"This course is very detailed and deals with real world solutions to everyday tasks. I have used the techniques for several of my daily and weekly reports and it's amazing with a click of a button I can process the reports instantly where it took hours a week before.

The course is a fantastic step by step tutorial. Bruce breaks it down and simplifies the process and procedures so you can quickly master them and implement them in your project.!" - Stephen Davanzo

The method starts out using queries and macros and then adds an introduction to VBA PROGRAMMING. If you have never used Microsoft Access VBA, this is an excellent introduction and a very practical example of how VBA can your life easier! Anyone with just a very basic knowledge of Access and Excel can easily accomplish all the tasks in this course!

* Videos show you every step from the initial Access tables to finished Excel report!

* There is no fluff and you can go through the entire course in about 2.5 hours

From another Student -

"This is an excellent course. Easy to follow with great explanations of what to do and why. I have learned so much and will refer back to this course. I can see this course and the information it teaches being very, very helpful in real world situations where you need to access database data in excel and use excel functionality to manipulate the data further. Awesome!" - Bronwyn Wilson

Think of how much time this will save you! Any time you need to prepare Excel files for distribution, it will only take seconds! Using these techniques can save many hours every month!

And remember - you don't have to know anything more than the bare basics of Access and Excel to learn how to instantly send data from Access to Excel. Click the Take This Course button and start learning right now!

Another Student -

"I have taken several of Bruce's courses and they all are the same, Simply Excellent! I would take this course 2nd in his series after the Intro to Access for Excel Users. The material is for real world users that need to know how Access projects work in the real world situations. This instructor is very easy to understand and follow with a vast amount of practical experience to draw from. Enjoy!" - Rodney

Required Software

You should have a subscription to Microsoft 365 and have the full Office suite installed.

Who this course is for:

Anyone who would like to save lots of time in making periodic reports

Microsoft Access 365: Link Data to Excel with Macros and VBA

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 2min

Prepare the Access Data6 lectures • 1hr 2min

Setup the Excel File and Link to Our Database6 lectures • 37min

Create VBA Program to Prep the Excel File for Distribution6 lectures • 46min

Requirements

Description

Who this course is for: