Excel macro and vba
Download
1 / 29

Excel Macro and VBA - PowerPoint PPT Presentation


  • 357 Views
  • Uploaded on

Excel Macro and VBA. Recording/Editing Macro. Recording macro: Tools/Macro/Record new macro Editing macro: Tools/Macro/Macros Excel’s macros are VBA procedures. Learning VBA with Macro. Spreadsheet operations: Copy/Paste/Fill/Clear/Delete Format cells Adding a worksheet

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Excel Macro and VBA' - chaylse


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Recording editing macro
Recording/Editing Macro

  • Recording macro:

    • Tools/Macro/Record new macro

  • Editing macro:

    • Tools/Macro/Macros

  • Excel’s macros are VBA procedures


Learning vba with macro
Learning VBA with Macro

  • Spreadsheet operations:

    • Copy/Paste/Fill/Clear/Delete

    • Format cells

    • Adding a worksheet

    • Insert/Delete rows, cols

    • Window scroll

  • Command Bar commands


Data sort subtotal commands
Data/Sort/Subtotal Commands

  • Range("A3:F25").Sort Key1:=Range("C4"), Order1:=xlAscending, Key2:=Range("B4"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

  • Selection.Subtotal GroupBy:=2, Function:=xlAverage, TotalList:=Array(6), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

  • Note: Use Excel Object Model to study Range object’s Sort and Subtotal methods. (Selection returns a range object)


Data pivot table
Data/Pivot Table

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!R3C1:R24C6").CreatePivotTable TableDestination:="", TableName:= "PivotTable2", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

ActiveSheet.Cells(3, 1).Select

ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Race", ColumnFields:="Sex"

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Salary")

.Orientation = xlDataField

.Caption = "Average of Salary"

.Function = xlAverage

End With


Pivotcaches
PivotCaches

  • Represents the collection of memory caches from the PivotTable reports in a workbook. Each memory cache is represented by a PivotCache object.

  • PivotCache

    • Represents the memory cache for a PivotTable report.


Pivotcaches add method
PivotCaches’ Add Method

  • Adds a new PivotTable cache to a PivotCaches collection. Returns a PivotCache object.

  • PivotCaches.Add(SourceType, SourceData)

  • SourceType: PivotTableSourceType can be one of these XlPivotTableSourceType

    • xlDatabase

    • xlExternal

  • SourceData:

    • Required if SourceType isn't xlExternal. Can be a Range object.

    • For an external database, we can create a recordset and assign the recordset object to pivotCahce’s Recordset property.

    • objPivotCache.Recordset = rstRecordset


Pivotcache s createpivottable method
PivotCache’s CreatePivotTable Method

  • Creates a PivotTable report based on a PivotCache object

  • pivotCacheObj.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)


Pivottable s addfields method and pivotfields property
PivotTable’s AddFields Method and PivotFields Property

  • AddFields method:

    • ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Race", ColumnFields:="Sex“

    • ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array("Race", "Sex"), ColumnFields:="IncomeGroup"

  • PivotFields property:

    • A collection of PivotField object.

    • PivotField object’s properties:

      • Caption

      • Function

      • Orientation


Rewrite the macro
ReWrite the Macro

Sub MyPivotTable()

Dim objPivotCache As PivotCache

Set objPivotCache =ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!A3:f24")

objPivotCache.CreatePivotTableTableDestination:=Sheet2.Range("A3"),TableName:="EEOPivotTable"

ThisWorkbook.Sheets("sheet2").Activate

ActiveSheet.Range("a3").Select

ActiveSheet.PivotTables("EEOPivotTable").AddFields RowFields:="Race", ColumnFields:="Sex"

With ActiveSheet.PivotTables("EEOPivotTable").PivotFields("Salary")

.Orientation = xlDataField

.Caption = "Average of Salary"

.Function = xlAverage

End With

End Sub


External data source with ado
External Data Source with ADO

Sub ADOPivotTable()

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

cn.Open ("provider=microsoft.jet.oledb.4.0;data source=c:\salesDB.mdb;")

Set rs = cn.Execute("select * from customer", adCmdText)

Dim objPivotCache As PivotCache

Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

Set objPivotCache.Recordset = rs

objPivotCache.CreatePivotTable TableDestination:=ActiveSheet.Range("A3"), TableName:="CustomerRating"

ActiveSheet.PivotTables("CustomerRating").AddFields RowFields:="City", ColumnFields:="Rating"

With ActiveSheet.PivotTables("CustomerRating").PivotFields("CID")

.Orientation = xlDataField

.Caption = "Number of Customer"

.Function = xlCount

End With

cn.Close

Set rs = Nothing

Set cn = Nothing

End Sub



Macro to import a query
Macro to Import a Query

Sub ImportQry()

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;DBQ=C:\SalesDB.mdb;DefaultDir=C:\;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxS"), Array( _

"canRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _

)), Destination:=Range("A1"))

.CommandText = Array( _

"SELECT CUSTOMER.CID, CUSTOMER.CNAME, CUSTOMER.CITY, CUSTOMER.RATING FROM `C:\SalesDB`.CUSTOMER CUSTOMER WHERE (CUSTOMER.RATING='a')" )

.Name = "Query ACustomer"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.SourceConnectionFile = "C:\Query ACustomer.dqy"

.Refresh BackgroundQuery:=False

End With

End Sub


Worksheet s querytables properties
Worksheet’s QueryTables Properties

  • A collection of QueryTable objects.

  • Each QueryTable object represents a worksheet table built from data returned from an external data source.


Querytables add method
QueryTables’ Add Method

  • Creates a new query table. Returns a QueryTable object that represents the new query table.

  • QueryTableObj..Add(Connection, Destination, Sql)

  • Connection   Required Variant. The data source for the query table. Can be one of the following:

    • A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form "ODBC;<connection string>".

    • An ADO or DAO Recordset object.

    • A Web query. A string in the form "URL;<url>".

    • Data Finder. A string in the form "FINDER;<data finder file path>" (*.dqy or *.iqy).

  • Destination   Required Range. The cell in the upper-left corner of the query table destination range.

  • Sql   Optional Variant. The SQL query string to be run on the ODBC data source. This argument is optional when you're using an ODBC data source. You cannot use this argument when a text file, or ADO or DAO Recordset object is specified as the data source.


Querytable object s properties and methods
QueryTable Object’s Properties and methods

  • Properties:

    • FieldNames

    • RefreshPeriod

    • RecordSet

  • Methods:

    • Refresh

    • Delete


Delete all querytables from a worksheet
Delete All QueryTables from a Worksheet

Sub RemoveAllQryTables()

Dim i As Integer

For i = ActiveSheet.QueryTables.Count To 1 Step -1

ActiveSheet.QueryTables.Item(i).Delete

Next i

End Sub


Rewrite macro to import data with odbc dsn
ReWrite Macro to Import Data with ODBC DSN

Sub myImportQry2()

Dim qt As QueryTable

Dim mySQL As String

mySQL = "select * from customer"

Set qt = ActiveSheet.QueryTables.Add("ODBC;DSN=salesDBDSN", Range("a1"), mySQL)

With qt

.FieldNames = True

.RefreshPeriod = 0

.Refresh BackgroundQuery:=False

End With

End Sub


Customizing the macro select a table query name from a listbox and retrieve its records
Customizing the Macro:Select a Table/Query Name from a ListBox and Retrieve its Records


Private Sub UserForm_Initialize()

ListBox1.AddItem ("customer")

ListBox1.AddItem ("custord")

End Sub

Private Sub CommandButton1_Click()

Call MyImportQry(ListBox1.Value)

End Sub

Sub MyImportQry(dataSource As String)

Dim qt As QueryTable

Dim mySQL As String

mySQL = "select * from " & dataSource

Set qt = ActiveSheet.QueryTables.Add("ODBC;DSN=salesDBDSN", Range("a1"), mySQL)

With qt

.FieldNames = True

.RefreshPeriod = 0

.Refresh BackgroundQuery:=False

End With

End Sub


Import web query
Import Web Query

  • Web query extension:

    • iqy


Sub importWebQry()

With ActiveSheet.QueryTables.Add(Connection:="FINDER;C:\ dchaoHP.iqy",

Destination:=Range("A1"))

.Name = "dchaoHP"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.WebSelectionType = xlAllTables

.WebFormatting = xlWebFormattingNone

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.WebDisableRedirections = False

.Refresh BackgroundQuery:=False

End With

End Sub


Simplify with default properties
Simplify with Default Properties

Sub impWebQry()

Dim qt As QueryTable

Set qt = ActiveSheet.QueryTables.Add(Connection:="FINDER;C:\dchaoHP.iqy", _

Destination:=Range("A1"))

With qt

.Name = "dchaoHP"

.FieldNames = True

.Refresh BackgroundQuery:=False

End With

End Sub


Customizing the macro
Customizing the Macro

  • In order to use the macro to import any web queries, we can use arguments to provide:

    • Query name

    • Destination

    • Query table name


Sub impWebQry(qryNamePath As String, destinationCell As Range, qryTableName As String)

Dim qt As QueryTable

Set qt = ActiveSheet.QueryTables.Add(Connection:="FINDER;" & qryNamePath, destination:=destinationCell)

With qt

.Name = qryTableName

.FieldNames = True

.Refresh BackgroundQuery:=False

End With

End Sub


Using finder to import database query
Using Finder to Import Database Query Range, qryTableName As String)

Sub FinderQry()

Dim qt As QueryTable

Set qt = ActiveSheet.QueryTables.Add(Connection:="FINDER;C:\queryCustomer.dqy", destination:=Range("A1"))

With qt

.Name = "qryCustomer"

.FieldNames = True

.Refresh BackgroundQuery:=False

End With

End Sub


Refedit control
RefEdit Control Range, qryTableName As String)

  • Let user to select a range.

  • The selected range is returned as text with the control’s Text property.


Refedit example
RefEdit Example Range, qryTableName As String)

Private Sub CommandButton1_Click()

Dim selectedRange As Range

Set selectedRange = Range(RefEdit1.Text)

MsgBox (RefEdit1.Text)

End Sub

Private Sub UserForm_Activate()

‘Show the current selection when open the form

RefEdit1.Text = Selection.Address

End Sub


Refedit example1
RefEdit Example Range, qryTableName As String)

Private Sub CommandButton1_Click()

Dim selectedRange As Range

Set selectedRange = Range(RefEdit1.Text)

Call ImportQry(ListBox1.Value, selectedRange, TextBox1.Text)

End Sub

Private Sub UserForm_Initialize()

ListBox1.AddItem ("customer")

ListBox1.AddItem ("custord")

Worksheets.Add

End Sub

Sub ImportQry(qryName As String, destination As Range, qtName As String)

Dim mySQL As String

mySQL = "select * from " & qryName

Dim qt As QueryTable

Set qt = ActiveSheet.QueryTables.Add("ODBC;DSN=salesDBDSN", destination, mySQL)

With qt

.FieldNames = True

.Name = qtName

.RefreshPeriod = 0

.Refresh BackgroundQuery:=False

End With

End Sub


ad