Hämta data från Internet direkt till ett Excelkalkylark

Posted by on

Nivå: Medelsvår

Med Excels QueryTables kan man bygga applikationer som hämtar data från en specifik plats på Internet, rakt ner till en arbetsbok i Excel. Antag t.ex. att du skulle vilja ha en arbetsbok i Excel där du samlar aktuella boräntor. Det första vi måste ta reda på är vilka tjänster på nätet som vi kan använda oss av. Eftersom jag är en “text-tv fantast” så har den statliga televisionen (självklart finns det kommersiella alternativ:) lagt ut text-tv utlagd på Internet.

Vad sägs om att ha ett makro som automatiskt går ut på Internet och hämtar ner aktuella boräntor, direkt till en Excelarbetsbok? Vi försöker skapa ett makro som gör detta! Nedan visar jag principen för hur ett sådant makro kan se ut:

Sidan vi skall hämta data ifrån

För att förstå stegen i makrot föreslår jag att vi först besöker SVT-text, med aktuella boräntor och den sida där t.ex Nordea’s boräntor presenteras. Klicka här för att se hur den sidan ser ut. Väl på sidan kan vi notera följande. Nordeas boräntor presenteras på sidan nummer 3 av totalt 5 sidor. (Tittar man högst upp till höger, kan man se att varje sida är numrerad. I detta fall skrivs den sida vi är intresserade av som 3(5)). Denna information, dvs. vilken tabell webbsidan med våra data har, skall vi notera. Den skall vi använda i vår kommande programmering.

Projektet

Koden vi lägger in vårt Excel-makro ser i sin helhet ut på följande sätt:

Sub aktiekurs()

Range(”A1:A9″).Select

 Selection.Cut

    Application.CutCopyMode = False

    Selection.EntireColumn.Insert

‘Hämta data från SVT-Text

Set shfirstqtr = Workbooks(1).Worksheets(1)

Set qtqtrresults = shfirstqtr.QueryTables _

    .Add(Connection:=”URL; http://svt.se/svttext/web/pages/232.html”, _

    Destination:=shfirstqtr.Cells(1, 1))

With qtqtrresults

    .WebFormatting = xlAlternateArraySeparator ‘ xlAllTables

    .WebSelectionType = xlSpecifiedTables

    ‘Hämta data från exempelvis Nordeas tabell – sidan 3 på Text-TV

    .WebTables = “3″

    .Refresh

End With

Range(”A4″).Select

    Selection.Delete Shift:=xlUp

Range(”A10:B22″).Select

    Selection.Delete Shift:=xlToLeft

‘Formatera Rubrik…Grön och Gul.

Range(”A4″).Select

    With Selection.Interior

        .ColorIndex = 50

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

    End With

Range(”A5″).Select

    With Selection.Interior

        .ColorIndex = 6

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

    End With

‘Formatera typsnitt…

Range(”A1:A23″).Select

    With Selection.Font

        .Name = “Courier New”

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ColorIndex = xlAutomatic

    End With

 

End Sub

Några kommentarer till vba-koden i Excel ovan:

I den första delen sätter vi in en ny kolumn i vårt kalkylark. I den andra delen, som börjar under kommentaren, Hämta data från SVT-Text, kan vi notera att vi instansierar objekten vi skall använda. Principen för programmeringen är fölande:

 1. Först talar vi om vart, vi skall hämta våra data ifrån. Lägg märke till att vi instansierar QueryTables samt att vi talar om från vilken sida data skall hämtas. Data skall hämtas från den URL-adressen som står beskriven till Nordeas SVT-textsidor.

2. I den andra delen måste vi tala om var någonstans på SVT-textsidan vi skall hämta våra data. Vi definierar att våra data med boräntor finns i en tabell på SVT-textsidan, och att denna tabell med data har nummer 3. Detta är i princip den viktigaste koden för att hämta data till kalkylarket.

3. Resterande kod är egentligen valfritt. Koden handlar om olika typer av formateringar som vi vill att sidan skall ha när data väl finns på kalkylarket. Här kan du experimenterar mer med layouten än vad jag har gjort i detta exempel.

 Kommentar: Querytables är ett mycket kraftfullt sätt att hämta ner data från Internet till en arbetsbok i Excel.

Du kan ladde ner exemplet ovan och testa mer på egen hand!

 

Ladda ner:  webquery

Share

Script som snabbt ger en anslutningssträng

Posted by on

Nivå: Medelsvår

Att ansluta sin applikation till en databas är en vanlig företeelse idag. Ibland kan det var knivigt att veta exakt hur själva anslutningen ser ut. 

Här kommer ett tips om hur du snabbt hittar koden för en anslutningssträng till en databas. Databasen ska vara registrerad på din dator.  Vi skapar ett VBS-script som hjälper oss med detta.  Gör så här:

Öppna Anteckningar i Windows (eller någon annan texteditor).  Kopiera in följande kod:

========= Skriptkodstart ==============

Dim oDataLinks, sRetVal
Set oDataLinks = CreateObject( “DataLinks” )
sRetVal = oDataLinks.PromptNew
On Error GoTo 0
If Not IsEmpty(sRetVal) Then
InputBox “Din anslutningssträng är listad nedan.”, _
    “OlEDB Connection String”, sRetVal
End If
Set oDataLinks = Nothing

========= Skripkodslut ==============

Spara nu dokumentet med filextensionen .vbs,  t.ex oledbconnstring.vbs,  eller hitta på något annat passande filnamn.  Testa och kör skriptet genom att dubbelklicka filen.

Share

Låt Word automatiskt skriva dina rapporter med data från ACCESS

Posted by on

Nivå: Avancerad

Kommentar:  Detta tips är för dig som är avancerade användare och behärskar programmering i Visual Basic 6 . Du bör ha kännedom om Data Access Object (DAO) version 3.6 för att hänga med!:)

Inledning

Microsft Word kan inte bara traditionell ordbehandling, utan det är ett mycket kraftfullt verktyg för att använda som en rapportgenerator,  t.ex hämta data från databaser som läggs i ett dokument.  Tänk dig följande scenario:

Din chef vill ha den veckovisa rapporten över lagertillgångar i företaget som ett färdigt Worddokument,  så att han/hon enkelt kan lägga  dokumentet  som en bilaga till nästa möteshandlingar.  Tänk dig om du kunde göra följande: tillverka ett program som automatiskt skriver ett Word-dokument, innehållandes de senaste datauppgifterna från företagets lagerdatabas, utan att du behöver knappa in en endaste rad med text manuellt. Vi antar nu att chefen dessutom är en stressad person, som inte kan lika mycket PC och Word som du, så det måste vara enkelt.  Chefen vill helst inte knappa på tangentbordet, utan endast göra några få musklick, sedan skall rapporten vara  färdig.  Den goda nyheten är att vi kan tillverka ett sådant program! Ett sätt är att programmera Word!  Nedan visas ett exempel på hur man tillverkar ett kraftfullt program i Word. När man finlirat färdigt och fått applikationen att göra det man vill, kan man enkelt konvertera applikationen som en Wordmall (*.dot), som du kommer åt när du ska skapa ett nytt dokument.

Projektet 

Vi startar Word på vanligt sätt och går in Visual Basic Editor (Alt+F11). Vi lägger ut ett formulär från verktygslådan i VBE. Formuläret ska nu innehålla följande kontroller:

  • Commondialog,
  • ListBox,
  • två stycken Kommandoknappar
  • två stycken Label

I detta exempel används DAO (Data Access Object). Därför måste referensen sättas i Visual Basic Editor till Microsoft DAO 3.6 Object, annars kommer det inte att fungera.

Händelseförlopp

Än så länge har vi inte lagt in någon programkod och inget händer om vi skulle starta vår applikation. Vi ser endast vårt formulär. Nu vill vi att följande skall hända i vårt program:

  1.  När användaren startar applikationen skall formuläret synas. Det första användaren skall göra är att välja en databas. Valet görs av att användaren får upp en dialogruta (kontrollen Commondialog).
  2.  När användaren valt databas, skall databasens samtliga frågor visas i Listboxen på formuläret.
  3.  Användaren väljer sedan en Fråga som finns i Listboxen. Applikationen läser alla tillgängliga frågor i Access, som användaren pekar på. Valet görs genom att användaren klickar på frågan som ska köras. Ett kontrollmeddelande skickas till användaren om frågan verkligen skall köras och om han vill fortsätta.
  4. Användaren klickar t.ex Frågan sp_pris. Data hämtas från databasen och data returneras och skrivs direkt till Worddokumentet.
  5. Klart! Vi har en nu databasrapport som är skriven automatiskt och formaterad i ett Worddokument, utan att vi rört tangentbordet och skrivet ett enda tecken från tangentbordet!

Kommentar

Genom att bygga en generell rapportgenerator i Word, som kan anropa Frågor i databaser som t.ex ACCESS, SQL etc. kan man fokusera sig mer på att tillverka frågor som beskriver de data man vill visa för användaren. Användaren kan därefter få data tillgängligt till Word i snygga rapporter som blir “rätt” varje gång de körs.

Resurser

  • Hämta programkoden till artikeln som pdf-dokument. Klicka här  WordVBA
  • Jag har lagt upp ett Youtube klipp där du kan se en presentation hur  applikationen fungerar (för Youtube-klippet, klicka här)
  • Referens DAO Language Reference, DAO 3.6,  från Microsoft. Se Microsofts hemsida.

Fotnot: Applikationen fungerar med Word 2003, 2007 och Access 2003, 2007. Har ej testat med ”högre” programversioner.

Share