General Information

This project involves wri4ng VBA code to automa4cally generate sales data into Excel from an external database. It is worth 25% of your final mark in the module.

Submission and Due Date

You should submit your work online, via QMPlus, before 18:00 on 15 January 2024.

Your submission should  include 2 files:

1.   Project2 .xlsm : An Excel workbook file. This should contain a VBA module as well as buOons to call the relevant func4ons.

2.   Project2Code.pdf : A pdf containing the code from your VBA module. You should copy and paste from the VBA editor into an appropriate text editor (e.g. MS Word). Then,

ensure that the code forma8ng is correct and convert it to a PDF for submission.

In accordance with QMUL Academic Regula4ons (3.48.i) assignments that are submiOed late without valid extenua4ng circumstances will be marked as follows:

For every period of 24 hours, or part thereof, that an assignment is overdue there shall be a deduc:on offive per cent of the total marks available (i.e. five marks for an assessment marked out of 100). ACer seven calendar days (168 hours or more late) the mark shall be reduced to zero, and recorded as 0FL (zero, fail, late).

Plagiarism

All work you submit should be your own. In par4cular, you should not copy or submit another student’s code or any code you find online without providing proper aOribu4on. All cases of plagiarism will be pursued according under QMUL’s Academic Misconduct Policy.

General Requirements

.    You should follow standard coding conven4ons: make sure your code is properly indented, use informa4ve variable names, declare all variables, and provide

explanatory comments where necessary.

.    Your Excel sheet should also adhere to best prac4ces for Excel development.

Important fixed ranges should be named so that names can be referenced in code.

.    You should not submit code that does not compile. It is beOer to complete only part of the project and submit clean code for this than to submit a full aOempt that does not run at all because of compila4on errors.

Project Overview

The goal of this project is to produce a small applica4on in Excel that queries a database and automa4cally produces several worksheets containing sales data summaries for specified periods.

Download the files Project2.xlsm and SalesData.accdb from QMPlus. The Excel workbook in Project2.xslm has a sheet named Main. The inten4on is that a user can enter one row per

desired report in this sheet, star4ng at row 6 (under the column labels), including the name of the report, a star4ng date, and an ending date in columns A, B, and C respec4vely. The

user should then be able to click a buOon to automa4cally connect to the given Access

database, retrieve a list of records for each date period given, and copy these records to a

new worksheet with the specified name. That is, the buOon should create one worksheet for each row the user enters, containing the data for the period corresponding to this row.

Each such worksheet should contain (in column A) a list of dates star4ng at the given start date and ending at the given end date. For each listed date, column B should show the total value of the sales and profit across all orders from the database that were made on this date. You will accomplish this by using an SQL query. For each desired report you should:

.    Open a connec4on to the database.

.    Create an appropriate SQL query to obtain the data for this report.

.    Execute your query to obtain a recordset.

.    Create a new worksheet, named according to the name in the specifica4on.

.    Copy the records from the recordset into the new sheet.

.    Format the data in the new sheet correctly.

An example of poten4al inputs and desired outputs are shown at the end of this document.

Project Details

The following ques4ons are designed to help walk you through what is required by wri4ng several separate VBA func4ons. Your func4ons should all be placed in the module Main in  Project2.xlsm.

A helper func4on Open_Database_Connection is already provided there. This works   as described in lectures: it takes a string containing the path to the database, and returns an ADODB.Connec4on object represen4ng a connec4on to this database. You should not modify the func4on Open_Database_Connection.

1)   Create a func4on CreateQueryCommand that takes no parameters and returns an ADODB.Command object represen4ng the SQL query that returns sales and profit data for all orders between these dates. You should create and add parameters to the Command object for these dates.

5)   There are many things that might go wrong: a user might give a bad sheet name, give the same sheet name twice, give a bad path to the database, etc.

a.   If there is an error in processing one specific row of Main, your error handler should skip this row, and (if necessary) delete any worksheet that may have   been created for this row. You should then show a message to the user indica4ng what the error was, and place “Error” in column D next to this row. Acer doing this, your code should carry on processing the remaining rows.

b.   If there is an error crea4ng the database connec4on, you should display a message to the user and terminate without processing any rows.

6)   Write a subrou4ne CleanUpSheets that takes no arguments. Your subrou4ne

should delete all sheets except for the sheet Main, and clear any cells in column D

that contain the text “Error”. Create a buOon on the sheet Main that the user can use to call this subrou4ne.

When dele4ng worksheets here (and in the error handler for part 5), you should temporarily set the Application.DisplayAlerts property appropriately to ensure that the user is not prompted regarding the dele4on.

Marking Guidelines

Your project will be marked according to the following general scheme:

.    60% Correctness of all func4onality

.    20% Error handling

.    20% Style, including

o Correctly formaOed code

o Using informa4ve variable names

o Using good prac4ce when coding, including using named ranges in Excel where appropriate

o Professional appearance of final project

If your project contains code which does not compile, it will receive at most a 50% so make sure that your code does not contain any compila4on errors!

Part marks will be awarded in the case that you cannot implement all func4onality, but any code that you do submit should be properly formaOed and free from any compilaGon errors. If some code is repeatedly causing a compila4on error that you cannot figure out, you can also comment this out and men4on the problem to show your work.

The remaining reports should have been generated successfully, and now appear as separate worksheets:

These worksheets should appear as follows: