1 / 74

Chapter 21: Working with Large Data Re-Visited

Spreadsheet-Based Decision Support Systems. Chapter 21: Working with Large Data Re-Visited. Prof. Name name@email.com Position (123) 456-7890 University Name. Overview. 21.1 Introduction 21.2 Creating Pivot Tables with VBA

jeanette-holt
Télécharger la présentation

Chapter 21: Working with Large Data Re-Visited

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Spreadsheet-Based Decision Support Systems Chapter 21: Working with Large Data Re-Visited Prof. Name name@email.com Position (123) 456-7890 University 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • The ActiveSheet object’s main method is the PivotTableWizard method. • The PivotTableWizard method has several arguments: • SourceTypeargument 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. Creating Pivot Tables with VBA (cont) • The AddFieldsmethod 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Creating Pivot Tables with VBA (cont) • 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. Importing Data (cont) • 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. Importing Data (cont) • 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. DimUserPath As String UserPath = InputBox(“Enter path of text file.”) Connection:= “TEXT; “ & UserPath & “

  24. Importing Data (cont) • 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. Importing Data (cont) • 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. DimUserPath As String UserPath = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Select a file to import.", , False) Connection:= “TEXT; “ & UserPath & “

  26. Importing Data (cont) • If we are importing data from a webpage, we would define the Connection argument as follows: Connection:= “URL; actual URL” • Here, the actualURL is the URL of the website. • Again, this value could be taken from the user dynamically.

  27. Importing Data (cont) • 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:=False End 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:=False End 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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. Importing Databases (cont) • 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.Connection CnctSource = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbUnivInfo & ";“ cntStudConnection.Open ConnectionString:=CnctSource Set rstNewQuery = New ADODB.Recordset rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnection Range(OutputRange).CopyFromRecordset rstNewQuery Set rstNewQuery = Nothing cntStudConnection.Close Set 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 is • A statement which specifies an action to perform; • A statement which specifies the location of the data on which to perform the action; • 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

More Related