1 / 18

Importing From Files

ringo
Télécharger la présentation

Importing From Files

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. 1 Importing From Files

    2. 2 Open File

    3. 3 Data Sources & Destination Objects

    4. 4 Using A Query Table Add to Worksheets Collection of Query Tables Define outside connection Define internal destination Define SQL (if necessary) Refresh Add to Worksheets Collection ofQuery Tables Define outside connection Define internal destination Define SQL (if necessary) Refresh

    5. 5 Create Query Table Set qt = ActiveSheet.QueryTables.Add( _ Connection:="text;" & filepath, _ Destination:=Range("h1")) qt.Refresh

    6. 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 7

    8. 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. 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. 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. 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 12

    13. Connect To Web Page All Tables 13

    14. 14 Types And Classes

    15. 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 You declare a type at the top of a module which is then available for any variable in any sub-routine in the current project.You declare a type at the top of a module which is then available for any variable in any sub-routine in the current project.

    16. Defining Your Own Class And A Property 16

    17. Defining You Class And A Method 17

    18. Initialise Your Class 18

More Related