Excel VBA / Bonus materiale / Datakilder

Querytables

Man kan oprette forbindelse til en ekstern database med QueryTables objekt. QueryTables objektet har en add metode med syntaksen:

QueryTables.Add(Connection, Destination, Sql)

Connection er en tekststreng, der beskriver, hvordan der skal etableres forbindelse til databasen. Destination er en angivelse af, hvor informationerne skal afleveres og Sql er en tekststreng, der beskriver, hvilke informationer, der skal trækkes ud af databasen.

Connection

Som skrevet er Connection en tekststreng - den er temmelig lang og skal være præcis. Her er der et eksempel på sådan en:

ODBC;DSN=MS Access 97 Database;DBQ=C:\Program Files\Microsoft Office\
Office\Samples\Northwind.mdb;DefaultDir=C:\Program Files\Microsoft Office\
Office\Samples;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

ODBC står for Open DataBase Connectivity. Mange databaser bruger ODBC drivere til at give andre programmer adgang til deres data.

DNS står for Data Source Name

DBQ er sti og navn på den database du vil etablere forbindelse til.

Hvordan laver man så en connection streng?
En hurtig vej til en connection streng er at oprette en QueryTable manuelt og tage et kig på Connection strengen. Processen er følgende:

• I et almindeligt regneark oprettes en forbindelse til databasen via Data | Get External Data | Create new query
• Skift til VBA editoren
• I Immediate vinduet kan du nu skrive

?sheet1.Querytables(1).Connection (arbejder du i DK versionen skal du skrive ark1 i stedet for sheet1)

• Og du vil se Connection strengen, som du så kan copy-paste ind i din kode

Hvordan laver man så en SQL streng?
Ja, det gør man på samme måde!

?sheet1.querytables(1).commandtext  eller ?sheet1.querytables(1).sql (arbejder du i DK versionen skal du skrive ark1 i stedet for sheet1)

Og et eksempel

Sub LavQT()
  Dim sConn As String
  Dim sSql as String
  Dim oQt as QueryTable

  sConn = "ODBC;DSN=MS Access 97 Database;"
  sConn = sConn & "DBQ=C:\Program Files\Microsoft Office\ "& _
    "Office\Samples\Northwind.mdb; "& _
    "DefaultDir=C:\Program Files\Microsoft Office\Office\Samples; "& _
    "DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"& _


  sSql = "SELECT Customers.CustomerID, Customers.CompanyName, Customers.City "& _
    "FROM `C:\Program Files\Microsoft Office\Office\Samples\Northwind` "& _
    ".Customers Customers "& _
    "WHERE (Customers.City='Berlin') "& _
    "ORDER BY Customers.CompanyName"& _


  Set oQt = ActiveSheet.QueryTables.Add( _
      Connection:=sConn, _
      Destination:=Range("a1"), _
      Sql:=sSql)
  oQt.Refresh

End Sub

Lad være med at bruge makro optageren til dette - det bliver slet ikke til at overskue.

Lav om på udvalget

Man kan ændre en querytables udvalg ved ganske enkelt at ændre dens commandtekst:

Sub qry()
    Sheet1.QueryTables(1).CommandText = _
        "SELECT Salg.ID, Salg.Fakturanr, Salg.Dato, Salg.Kundenr, Salg.Firma, Salg.Sælger, Salg.Gruppe, Salg.Model, Salg.`Stk pris`, Salg.Antal, Salg.Total " & _
        "FROM `N:\Tekster\Dumps\salg`.Salg Salg " & _
        "WHERE (Salg.Gruppe LIKE '%" & Range("M1").Value & "%') " & _
        "ORDER BY Salg.Kundenr"
    Sheet1.QueryTables(1).Refresh
End Sub

Læs mere her: http://www.dicks-clicks.com/excel/ExternalData.htm#What_is

Hent en database du kan lege med her

<<- Ressourcer

Pivot tabeller ->>