VBA For Excel

							1




    Importing From Files
2

             Open File




     XML                 Workbooks Open
                             Method

     Text

    Access
3

    Data Sources & Destination Objects

     OLEDB

     ODBC                       QUERY TABLE

      Text

      ADO

      HTML

                                 LIST OBJECT



      XML
4

                   Using A Query Table



    External   CONNECTION
    Database
                                QUERY TABLE
                   SQL




               worksheet
5

                 Create Query Table

    Set qt = ActiveSheet.QueryTables.Add( _

        Connection:="text;" & filepath, _
        Destination:=Range("h1"))

        qt.Refresh
6

                 Connect Via ODBC

Set qt = ActiveSheet.QueryTables.Add( _
  Connection:="ODBC;DSN=My_DSN", _
  SQL := "Select * from Categories“ _
  Destination:=Range("h1")
)

    qt.Refresh
7

               Connect Via OLEDB



Set qt = ws.QueryTables.Add( _
Connection:="OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;
       Data Source=C:\NorthWind.accb ", _
Sql:="select * from categories", _
Destination:=Range("a1"))
8

     Using ADO – Establish A Connection
Sub ConnectToAccess()

Dim conn As   New ADODB.Connection
Dim rs As     New ADODB.Recordset

conn.ConnectionString = _
"Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\NorthWind.mdb;"

conn.Open
9

     Using ADO – Establish A Recordset
rs.Open "Applications", conn,
  adOpenDynamic, adLockOptimistic
rs.MoveFirst
debug. print rs.fields(1).value
rs.Close
Set rs = Nothing

End Sub
10

                    Connecting To Oracle
 Function Oracle()

     Set conn = New ADODB.Connection
     conn.ConnectionString =
     "Provider =MSDAORA; User ID=test;
      Data Source=ora1;        Password=manager;"

     conn.Open

     If conn.State = adStateOpen Then
         Set Oracle = conn
     End If

     End Function
11

                Connecting To SQLServer
 Function SQLServer()

     Set conn = New ADODB.Connection
     conn.ConnectionString =
     "Provider='SQLOLEDB';Server='MySqlServer';
     Database='Northwind';
     Integrated Security='SSPI';"
     conn.Open

     If conn.State = adStateOpen Then
         Set SQLServer = conn
     End If

     End Function
12

     Connect To Web Page – Two Tables

Sub Get_One_Table()
  url = "c:\test_tables.htm"
  Set qt = ws.QueryTables.Add( _
  Connection:="URL;" & url, _
  Destination:=Range("a1"))
  With qt
     .WebSelectionType = xlSpecifiedTables
     .WebFormatting = xlWebFormattingNone
     .WebTables = “1,3"
  End With
  qt.Refresh
End Sub
13

      Connect To Web Page – All Tables

Sub Get_All_Tables()
 url = "c:\test_tables.htm"
  Set qt = ws.QueryTables.Add( _
  Connection:="URL;" & url, _
  Destination:=Range("a1"))
  With qt
     .WebSelectionType = xlAllTables
     .WebFormatting = xlWebFormattingNone
  End With
  qt.Refresh
End Sub
14




     Types And Classes
15

         Defining Your Own Type ( Record )
 Type MyRecord
     price As Single
     units As Integer
      revenue As Single
  End Type
 ___________________________________
 Sub UseMyType()
   Dim test As MyRecord
     test.price = 5.67
     test.units = 5
     test.revenue = test.price * test.units
 End Sub
16

     Defining Your Own Class And A Property

     Private p_name As String

     ' Read the property value
     Public Property Get Name() As String
        Name = p_name
     End Property

     ' Write the property value
     Public Property Let Name(var As String)
        p_name = var
     End Property
17

        Defining You Class And A Method


     Public Sub My_Object_Method1()
       ------------
       ------------
     End Sub
18

                  Initialise Your Class


     Private Sub Class_Initialize()
      Me.Name = "Jim"
      p_name = "Fred"
     End Sub