chapter 21: working with large data re-visited spreadsheet-based decision support systems prof. name...

Chapter 21: Working with Large Data Re-Visited

Spreadsheet-Based Decision Support Systems

Prof. Name [email protected] (123) 456-7890University Name

2

Overview

21.1 Introduction 21.2 Creating Pivot Tables with VBA 21.3 Using External Data 21.4 Exporting Data 21.5 Applications 21.6 Summary

3

Introduction

Creating pivot tables using VBA

Importing data from text files or webpages using VBA

Importing data from databases

Creating basic queries using the SQL programming language

Exporting data using VBA

An application which allows a user to query a database from Excel

4

Creating Pivot Tables with VBA

In Chapter 6, we learned how to create pivot tables and pivot charts using the Pivot Table Wizard in the Excel Data menu.

We will now learn the properties and methods in VBA that will allow us to perform these tasks dynamically.

5

Creating Pivot Tables with VBA (cont)

The main pivot table object is PivotTables.

Pivot tables are used similar to the Chart and Shape objects in that we must use the ActiveSheet object before specifying a PivotTables object.

ActiveSheet.PivotTables(“PivotTable1”)

6


From this PivotTables object, we can further specify PivotFields, and from PivotFields we can specify PivotItems.

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Days to Arrive”)

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Days to Arrive”).PivotItems(“4”)

7


The ActiveSheet object’s main method is the PivotTableWizard method.

The PivotTableWizard method has several arguments: – SourceType argument specifies if our pivot table data is from the spreadsheet

(xlDatabase), an external source (xlExternal), multiple ranges (xlConsolidation), or another pivot table (xlPivotTable).

– SourceData argument is used to specify the specific data from this source type.

– TableDestination argument specifies a range where the table should be placed.

– TableName argument can be used to give a name to this table, such as “PivotTable1”.

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=Worksheets(“Data-Shipping”).Range(“B3:E27”), TableDestination:=Worksheets(“Pivot-Shipping”).Range(“A3”), TableName:=“PivotTable1”

8


The AddFields method is used to specify the pivot table layout. – This method is used to set row fields, column fields, and page fields of the

pivot table. – Each field is an optional argument of the method: RowFields,

ColumnFields, and PageFields. – If there is more than one field for a particular argument, then use the Array

function to specify them. – The AddToTable argument takes True/False values to determine if these

new fields should be appended to the current table or replace existing fields, respectively.

ActiveSheet.PivotTables(“PivotTable1”).AddFields RowFields:= Array(“Max Weight, lbs”, “Days to Arrive”), ColumnFields:=”Shipping Company”

9


For the PivotTables object, there are several other properties and methods to discuss.

The RowGrand and ColumnGrand properties specify whether or not grand totals should be calculated for row or column fields, respectively. – The possible values for these properties are True or False.

ActiveSheet.PivotTables(“PivotTable1”).RowGrand = True

10


Another property that may be used often is the Format property. – This can be used to apply a pre-defined report or table format to your pivot

table.

– The values for this property can be an xlReport value or xlTable value.

ActiveSheet.PivotTables(“PivotTable1”).Format xlReport10

ActiveSheet.PivotTables(“PivotTable1”).Format xlTable2

11


A useful method of the PivotTables object is the RefreshData method. – This method is equivalent to pressing the exclamation point icon on the Pivot

Table Toolbar.

– If any changes are made to the data from which the pivot table was created, refreshing the data will update the pivot table data.

ActiveSheet.PivotTables(“PivotTable1”).RefreshData

12


One last useful method of the PivotTables object is GetPivotData. – This method has the same functionality as the GETPIVOTDATA function

defined in Chapter 6.

– For a specific item in a given row or column field, this method will find the corresponding value from the data field.

ActiveSheet.PivotTables(“PivotTable1”).GetPivotData(“DataFieldName”, “RoworColumnFieldName”, “ItemName”)

13


There are some other useful properties of the PivotFields object.

The Orientation property sets the data fields of a pivot table. – This property takes the values xlDataField, xlRowField,

xlColumnField, and xlPageField or the respective fields.

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Cost”).Orientation = xlDataField

There is another possible value for the Orientation property which is xlHidden. – This will hide all of the values of the specified field. – This property can be useful not only to set the data field, but also to change

any previously set fields to be different field types or to be removed from the pivot table all together.

14


There are two properties which can be used to make calculations (sum, average, min, max, etc). – These are the Function property and SubTotals property.

The Function property is used for data fields. – To use this property simply specify the type of calculation you want to be

made on the named field.

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Cost”).Function = xlMin

15


The SubTotals property is used for non-data fields. – With this property you must specify an index number, or numbers, which

represent the type of sub totals you want to show for the given field.

– These index values are 2 = sum 3 = count 4 = average 5 = max 6 = min others

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Max Weight, lbs”).SubTotals(6)

16


There is one main property that is used often with the PivotItems object which is the Visible property. – Using this property is similar to clicking on the drop-down list of values for a

field in a pivot table and checking or un-checking the values which you want to be displayed.

– The values for this property are True and False, much like we have seen in uses of the Visible property with other objects.

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Days To Arrive”).PivotItems(“1”).Visible = True

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Days To Arrive”).PivotItems(“8”).Visible = True

17


One last useful property is the ShowPivotTableFieldList property which is used with a Workbook object. – This property has True or False values which can be set to show or hide the

pivot table field list of the pivot tables in the workbook.

ActiveWorkbook.ShowPivotTableFieldList = True

18

Figure 21.3

One complete pivot table code example

19

Using External Data

Importing Data– Text Files and Webpages

– Databases

Performing Queries with SQL

20

Importing Data

We will first describe how to import data from text files and web addresses in VBA.

We will use an object called QueryTables.

This object is referred to using a Worksheet object.

ActiveSheet.QueryTables

21

Importing Data (cont)

To import data, we will simply add a QueryTable object using the Add method.

The Add method has two arguments: – Connection requires the type of data being imported and the actual location

of the data.

– Destination argument is the location on the spreadsheet where you would like to place the imported data.

22


The Connection argument enables us to clarify if we are importing data from a text file or a webpage.

If we are importing data from a text file, we would define the Connection argument as follows.

Connection:= “TEXT; path”

Here, the path is the actual location of the text file on your computer given by some string value.

23


The path value can also be given dynamically by prompting the user for the path value and storing the path name in a string variable.

This path value would have to be concatenated with the TEXT specification.

Dim UserPath As String

UserPath = InputBox(“Enter path of text file.”) Connection:= “TEXT; “ & UserPath & “

24


In creating dynamic imports, you may prefer to let the user browse for a file rather than enter the path.

To display an explorer browse window, we use the GetOpenFilename method associated with the Application object. – This method presents the user with a browse window and allows them to

select a file.

– The name of the file is returned as a string value.

Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

25


The FileFilter argument gives you the option of limiting the type of file the user can select. – “Text Files (*.txt), *.txt”

The Title argument allows you to give a title to the browse window that will appear.

The MultiSelect has the values True or False to determine if a user can select more than one or only one value, respectively.

Dim UserPath As StringUserPath = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Select a

file to import.", , False) Connection:= “TEXT; “ & UserPath & “

26


If we are importing data from a webpage, we would define the Connection argument as follows:

Connection:= “URL; actual URL”

Here, the actual URL is the URL of the website.

Again, this value could be taken from the user dynamically.

27


The Destination argument value is simply a range.

Columns and rows will be created for the data appropriately.

The output range for the entire table of data will begin in the Destination range.

Destination:=Range(“A1”)

28

Importing Text Code

The necessary properties for importing a text file basically describe how the text is organized in the file so that the values are imported correctly.

With ActiveSheet.QueryTables.Add (Connection:=“TEXT;C:\MyDocuments\textfile.txt",

Destination:=Range("A1")) .Name = "ImportTextFile" .FieldNames = True .RowNumbers = False .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileCommaDelimiter = True .Refresh BackgroundQuery:=FalseEnd With

29

Figures 21.4, 21.5, and 21.6

An example text file imported to Excel using VBA

30

Importing Webpage Code

To import a webpage, there are a few new properties needed.

With ActiveSheet.QueryTables.Add(Connection:= "URL;http://www.webpage.com", Destination:=Range("C1"))

.Name = “WebpageQuery1" .FieldNames = True

.RowNumbers = False

.WebSelectionType = xlSpecifiedTables

.WebFormatting = xlWebFormattingNone

.WebTables = "1"

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.Refresh BackgroundQuery:=FalseEnd With

31

Figures 21.7, 21.8, and 21.9

An example webpage imported to Excel using VBA

32

Importing Databases

There are two main objects used to import data– Connection

– Recordset

The Connection object establishes the communication to a particular database.

There are two main methods used with this object– Open method uses a ConnectionString argument to define the path to the

database.

– Close method does not have any arguments.

A Connection should be opened and closed every time a query or import is made from the database.

33

Importing Databases (cont)

To define a Connection object variable, we use a data type called ADODB.Connection.

We declare the variable as an ADODB.Connection data type and then use the Set statement to define the connection value of our variable.

We define our connections to be new connections using the New statement.

Dim cntMyConnection As ADODB.Connection

Set cntMyConnection = New ADODB.Connection

34


Now, we need to define the data provider, or database type, and data source, or filename, of this connection. – These values will be given to the ConnectionString argument of the Open

method.

– The data provider we will usually use can be defined as: “Microsoft.Jet.OLEDB.4.0”.

– The data source should be the filename of the database plus the path of the file.

Dim dbMyDatabase As String

dbMyDatabase = ThisWorkbook.Path & “\MyDatabase.mdb”

35


Now we have the data provider and data source; we can either assign these values directly to the ConnectionString argument or we can use a String variable.

The ConnectionString argument value has two sub arguments named Provider and Data Source for the data provider and data source, respectively.

Dim CnctSource As String

CnctSource = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & dbMyDatabase & “;”

36


The complete code to open a connection is:

Dim cntMyConnection As ADODB.Connection, dbMyDatabase As String, CnctSource

Set cntMyConnection = New ADODB.Connection

dbMyDatabase = ThisWorkbook.Path & “\MyDatabase.mdb”

CnctSource = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & dbMyDatabase & “;”

cntMyConnection.Open ConnectionString:=CnctSource

37


After closing a Connection, we clear the Connection value by setting it to Nothing.

The complete code to close a connection is:

cntMyConnection.Close

Set cntMyConnection = Nothing

38


The Recordset object is used to define a particular selection of data from the database that we are importing or manipulating. – We will again use a variable to represent this object throughout the code; to

define Recordset object variables, we use the ADODB.Recordset data type.

– We again use the Set statement to assign the value to this variable as a New Recordset.

Dim rstFirstRecordset As ADODB.Recordset

Set rstFirstRecordset = New ADODB.Recordset

39


The arguments for the Open method of the Recordset object are – Source

– ActiveConnection

The Source argument defines the data that should be imported.– The Source value is a string which contains some SQL commands.

– Similar to the data source value and ConnectionString value discussed above, we can use a String variable to define these SQL commands to use as the value of the Source argument

Dim Src As String

Src = “SELECT * FROM tblTable1”

40


The ActiveConnection argument value is the name of the open Connection object you have previously defined.

rstFirstRecordset.Open Source:=Src; ActiveConnection:=cntMyConnection

To copy this data to the Excel spreadsheet, we use the Range object and a new method: CopyFromRecordset. – This method only needs to be followed by the name of the Recordset variable

you have just opened.

Range(“A1”).CopyFromRecordset rstFirstRecordset

41


In each procedure where we are importing or manipulating data from a database, we type the following.

Dim rstFirstRecordset As ADODB.Recordset, Src As String

Set rstFirstRecordset = New ADODB.Recordset

Src = “SELECT * FROM tblTable1”

rstFirstRecordset.Open Source:=Src; ActiveConnection:=cntMyConnection

Range(“A1”).CopyFromRecordset rstFirstRecordset

42


When we are done using this Recordset, we should clear its values; we do this using the Set statement with the value Nothing.

Set rstFirstRecordset = Nothing

43


In applications where you plan to make multiple queries to a database, we recommend creating a function procedure which can be called for each query.

Function QueryData(Src, OutputRange) dbUnivInfo = ThisWorkbook.Path & "\UniversityInformationSystem.mdb"Set cntStudConnection = New ADODB.ConnectionCnctSource = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbUnivInfo & ";“ cntStudConnection.Open ConnectionString:=CnctSource Set rstNewQuery = New ADODB.Recordset rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnectionRange(OutputRange).CopyFromRecordset rstNewQuery Set rstNewQuery = NothingcntStudConnection.CloseSet cntStudConnection = Nothing

End Function

44

Performing Queries with SQL

Structured Query Language (SQL) is the code used to perform queries, or filter the data which is imported.

SQL commands are used to define the Source argument of the Open method with the Recordset object.

You can define the Source to be all values in a particular database table or pre-defined query or you can create a query as the value of the Source argument.

45

SQL (cont)

The basic structure of SQL commands is1. A statement which specifies an action to perform;

2. A statement which specifies the location of the data on which to perform the action;

3. A statement which specifies the criteria the data must meet in order for the action to be performed.

Some basic action statements are – SELECT

– CREATE

– INSERT

46

Figure 21.10

Consider a table from a University System database.

This table, called tblStudents, contains student names, IDs, and GPAs.

47

SQL (cont)

The SELECT statement selects a specific group of data items from a table or query in the database.

The phrase appearing immediately after the SELECT statement is the name or names of the fields which should be selected.

SELECT StudentName FROM tblStudents

48

SQL (cont)

To select everything in a table, that is all fields, use the asterisks mark (*) after the SELECT statement.

We must also specify the location of this field, that is the table or query title from the database.

We do this using the FROM statement.

49

SQL (cont)

We can also include a criteria filtering in the query.

The most common criteria statement is WHERE.

The WHERE statement can use sub statements such as– <, >, = for value evaluations.

– BETWEEN, LIKE, AND, OR, and NOT for other comparisons.

SELECT StudentName FROM tblStudents WHERE GPA > 3.5

50

SQL (cont)

Other criteria statements include – GROUP BY

– ORDER BY

ORDER BY can be used with the WHERE statement to sort the selected data; this data can be sorted in ascending or descending order using the statements ASC or DESC respectively.

SELECT StudentName, GPA FROM tblStudents WHERE GPA > 3.0 ORDER BY GPA DESC

51

SQL (cont)

In a SELECT statement, we can also perform simple aggregate functions.

Simply type the name of the function after the SELECT statement and list the field names which apply to the function statement in parenthesis.

One common function statement is COUNT. – Using SELECT COUNT will return the number of items (matching any given

criteria) instead of the items themselves.

SELECT COUNT (StudentName) FROM tblStudents WHERE GPA > 3.5

52

SQL (cont)

Other functions include– MIN

– MAX

– AVG

SELECT AVG (GPA) FROM tblStudents

53

SQL (cont)

In VBA, SQL statements always appear as a string; that is, they are enclosed by quotation marks.

If your criteria checks for a particular string value, you must use single quotation marks to state that value.

=”SELECT GPA FROM tblStudents WHERE StudentName = ‘O. Peterson”

54

SQL (cont)

Now suppose instead of specifying our own criteria, we want the user to determine which name to search for. – We can use an Input Box and a variable, in this example called StudName, to

prompt the user for this value. – Then we can include this variable in place of the criteria value in the SQL

statement.

=”SELECT SSN FROM tblStudents WHERE StudentName = ‘” & StudName &” ’”

Note that we have to include the single quotation marks around the criteria value; therefore, we have concatenated the variable name followed by the ending single quotation mark.

55

SQL (cont)

Now let us incorporate these SQL statements into our database query code. – As mentioned in the previous section, we will use a string variable to assign

the value of the SQL commands. – We will then use this variable in the Source argument of the Open method of

the Recordset object.

Dim StudName As StringStudName = InputBox(“Please enter name of student whose GPA you want.”

Src = “SELECT GPA FROM tblStudents WHERE StudentName = ‘” & StudName & “’”

rstFirstRecordset.Open Source:=Src; ActiveConnection:=cntMyConnectionRange(“A1”).CopyFromRecordset rstFirstRecordset

56

Exporting Data We can also use SQL to export data.

We can place data into a previously created Access database using the CREATE and INSERT SQL commands.

The CREATE statement can be used to create a new table in the database. – The corresponding location statement for the CREATE command is TABLE. – The name of the new table is given after the TABLE statement. – The name of the table is followed by the name of the fields for the new table;

these are listed in parenthesis with a description of the data type the field should hold.

– You must also include a CONSTRAINT command to specify the primary key of the table.

– You would give a name to this key, specify that it is the PRIMARY KEY, and then list the selected field.

=”CREATE TABLE tblCourses (CourseName TEXT, CourseNumber NUMBER, FacultyAssigned TEXT) CONSTRAINT CourseID PRIMARY KEY (CourseNumber)”

57

Exporting (cont)

Once you have created a table, you can use the INSERT statement to enter values for each field.

The INSERT statement is always followed by the INTO location statement. – The name of the table into which you are entering values is listed after the

INTO statement. – The field names for which you are entering values should then be listed in

parenthesis; that is, you may not want to enter values for all fields. – Then the values are listed after a VALUES statement in the same order in

which the corresponding fields were listed.

=”INSERT INTO tblCourses (CourseName, CourseNumber, FacultyAssigned) VALUES (‘DSS’, 234, ‘J. Smith’)”

58

Exporting (cont)

You can also use the UPDATE statement to change values in a previously created table.

The UPDATE statement uses the SET location statement and the same criteria statements used with the SELECT command.

=”UPDATE tblStudents SET GPA = 3.9 WHERE StudentName = ‘Y. Zaals’”

59

Applications

Transcript Query– We will develop an application which performs dynamic database queries

using a pre-developed Access database.

60

Description

This database contains information on students, faculty, courses, sections, and grades; there are six tables and one query.

In this application, we will allow the user to query the database to retrieve transcript data for a particular student.

This transcript data will include every course the student has taken with the details of the course and section as well as the grade they earned.

We will then evaluate all grades to calculate the selected student’s overall GPA.

61

Figure 21.11

The tables and queries from MS Access

62

Figure 21.12

The spreadsheet

63

Figure 21.13

The query function procedure

64

Figure 21.14

The initial procedures

65

Figure 21.15

The “tblStudent” table from Access.

66

Figures 21.16 and 21.18

The student choice form

67

Figure 21.17

The form code

68

Figure 21.19

The “qryCourseID” query from Access

69

Figure 21.20

The “tblCourse” table from Access

70

Figure 21.21

The “tblSection” table from Access

71

Figure 21.22

The transcript query code

72

Application Conclusion

The application is now complete.

Transcript queries can be made for any student selected from the form.

73

Summary

The main pivot table object is PivotTables. We must use the ActiveSheet object before specifying a PivotTables object. To create a pivot chart in VBA simply use the Chart object.

There are two main systems used in VBA for communicating with external data sources: DAO and ADO. (We use ADO in this chapter.) There are two main ADO objects used to import data: Connection and Recordset.

Structured Query Language (SQL) is the code used to perform queries or filter the data which is imported.

Variables can be used to make queries dynamic with Input Boxes, User Forms, or by simply taking values the user has entered in a spreadsheet.

74

Additional Links

(place links here)

chapter 21: working with large data re-visited spreadsheet-based decision support systems prof. name...

Documents

pivot table data

pivot charts

pivot table wizard

pivot table xlpivottable

pivot table layout

current table

vbaimporting data

pivottables object