Kontroller andre programmer fra Excel
Objektbrowseren
Automatisering i praksis
Automatiseríng af Word
Automatisering af Outlook
Fra et program kan man ved hjælp af programmering udnytte ressourcer i andre programmer, hvis disse stiller deres objektbiblioteker til rådighed for VBA. Det gælder fx alle programmer i office-pakken, men det gælder også en række andre programmer både fra Microsoft og fra andre leverandører.
Dette kaldes Automation eller tidligere OLE Automation og er en disciplin for sig selv. Idéen med automation er egentlig at man kan udnytte de ting, som de enkelte programmer er bedst til, sådan som det bør være med alt værktøj. Ligesom det er nemmere at fælde et egetræ med en motorsav end med en teske (selv om begge dele er mulige), så er det også nemmere at lave et brev i Word end i Excel. Men når nu data findes i Excel, og det er der vi arbejder mest, hvorfor så ikke bare automatisere brevskrivningen?
Selve integrationen mellem programmerne kan give sig udslag på mange måder. Måske skal der åbnes en PowerPoint præsentation ved klik på et knap i et regneark, måske skal der sendes en mail via Outlook ved en klik på en knap i en formular eller måske skal der overføres data til Word, som findes i et regneark ved dobbeltklik på en celle i regnearket.
Fælles for dem alle er imidlertid, at for at de skal virke skal Excel kunne tilgå de pågældende programmers objektbiblioteker. Det betyder:
1) Det pågældende program skal være installeret på den pc, hvor koden skal bruges
2) Det skal oprettes en reference fra Excel (eller hvilket program, som end er udgangspunkt) til det pågældende programs objektbibliotek
Referencen skal oprettes på programmeringstidspunktet (såkaldt early binding).
Early
binding udføres ved at vælge Tools – References fra VBA editorens
menulinje. I ruden, der så vises sættes flueben ud for de objektbiblioteker, som
man har brug for at få adgang til.
Næste gang man åbner dialogboksen vil man kunne se, at de biblioteker, der er markeret er flyttet til toppen af listen.
Fordelen ved Early binding er udover at det er nemt - at objekterne fra det andet program er til rådighed allerede mens man programmerer, og man kan derfor referere det pågældende programs nøgleord.
Ulempen er, at alle der skal bruge den pågældende kode skal ind i deres VBA editor og sætte en reference.
Ved late binding sættes referencen først på eksekveringstidspunktet, men det kan man ikke i VBA, selv om man kan i andre sprog, fx VB. Derfor ikke mere om det.
Objektbrowseren
Når
først referencen er sat, kan man gennemse det pågældende programs
objektbibliotek inde fra Excel VBA editoren.
Vælg View - Object Browser eller tryk F2 mens VBA editoren er aktiv. Så vises objektbrowseren. I den øverste rude vælges det program, hvis objektbibliotek, man gerne vil se.
I ruden til
venstre kan man nu se de objektklasser, som findes og i ruden til højre kan man
se de metoder (Sub
) og Egenskaber (Properties
) som er knyttet
til den pågældende objektklasse. I eksemplet er vist Paragraphs (Afsnit) og det
fremgår at dette har ret mange medlemmer.
Det er nok ikke mange, der vil sætte sig ned og gennemgå samtlige programmers objektbiblioteker og det er da heldigvis heller ikke nødvendigt. Dels bør man kende programmerne nogenlunde inden man indlader sig på automation, og så har man sikkert allerede et meget godt greb om de forskellige objekter, som de enkelte programmer har, og hvad de kan bruges til. Desuden kan man altid finde objektbrowseren frem, hvis det bliver nødvendigt.
Endelig er der en anden metode, man også kan have glæde af. Man kan fx optage en makro i det pågældende program, der gør noget i retning af, hvad man gerne vil gøre gennem automation, og se på de objekter, metoder og egenskaber, som programmet selv bruger. Alt hvad programmet selv gør, kan man også gøre gennem automation. Som eksempler har jeg optaget tre makroer i Word.
Den første makro indsætter et billede i et dokument. Af makroen lærer vi, at der findes et Selection objekt som igen indeholder objektet InlineShapes. Vi kan se, at dette objekt har en metode, som heddert AddPicture som indsætter et billede i objektet. Metoden har et argument som hedder FileName, som skal være billedets sti og filnavn. Desuden to andre argumenter "LinkToFile (skal der oprettes en kæde til billedfilen, her Nej) og et argument som hedder SaveWithDocument (skal billedet gemmes sammen med dokumentet og det skal det i dette tilfælde).
Sub Makro2()
'
' Makro2 Makro
' Makro indspillet 16-02-2010 af Jan Kronsell
'
Selection.InlineShapes.AddPicture FileName:= _
"F:\Billeder\1-10-Januar\DSC_0003.JPG", LinkToFile:=False, _
SaveWithDocument:=True
End Sub
Havde
jeg fundet den samme information via object browseren kunne jeg have set
følgende:
Altså præcis det samme, men jeg kunne også have set, at der var endnu et argument, nemlig Range. Man kunne i øvrigt også have set, at InlineShapes havde flere andre metoder og egenskaber.
Den næste makro flytter et afsnit rundt i dokumentet:
Sub Makro3()
'
' Makro3 Makro
' Makro indspillet 16-02-2010 Jan Kronsell
'
Selection.MoveUp Unit:=wdLine, Count:=6, Extend:=wdExtend
Selection.Cut
Selection.MoveUp Unit:=wdLine, Count:=38
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.Paste
End Sub
Objektet her er Selection. Det har i hvert fald fire metoder: MoveUp og MoveDown, som begge har argumenterne Unit og Count (unit kan i hvert fald måles i linjer og Count i heltal). MoveUp har også argumentet Extend og objektbrowseren viser at det samme argument findes for MoveDown. Desuden har objektet metoderne Cut og Paste som tilsyneladende ikke har argumenter. Objektbrowseren viser, at det er korrekt for så vidt angår Cut (og i øvrigt også for de øvrige metoder).
Den sidste makro jeg indspillede ændrede på et afsnits format.
Sub Makro4()
'
' Makro4 Makro
' Makro indspillet 16-02-2010 af Jan Kronsell
'
With Selection.ParagraphFormat
.LeftIndent = CentimetersToPoints(1)
.RightIndent = CentimetersToPoints(1)
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 6
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpace1pt5
.Alignment = wdAlignParagraphLeft[1]
.WidowControl = True
.KeepWithNext = False
.KeepTogether = False
.PageBreakBefore = False
.NoLineNumber = False
.Hyphenation = True
.FirstLineIndent = CentimetersToPoints(0)
.OutlineLevel = wdOutlineLevelBodyText
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
End With
End Sub
Det er stadig Selection objektet, der arbejdes med. Denne gang med det indeholdte objekt ParagraphFormat. Allerede koden viser, at dette objekt har mange egenskaber. Læg mærke til, at når så mange egenskaber skal styres på en gang, bruger Word også With…End With syntaksen, som blev omtalt i artiklen om Exces objekter.
Objektbrowseren viser at objektet har endnu flere egenskaber og metoder, som kan anvendes.
Automatisering i praksis
Jeg vil nu prøve at gennemgå to forskellige eksempler på Automation. Det første, som gennemgås detaljeret, automatiserer Word. Det næste, som gennemgås knap så detaljeret automatiserer Outlook. De to eksempler er også vist under Makroer, henholdsvis i artiklerne Opret enkelt dokument i Word med data fra Excel og Send mail fra Excel via Outlook. Uanset hvilket program, der skal automatiseres med, skal der altså sættes en reference til det pågældende program under Tools - References. Hvis makroen ikke skal bruges på den pc, hvor det udvikles, skal det pågældende program være installeret på alle de pc'er, hvor makroen faktisk skal bruges og på den alle skal den relevante reference sættes.
Men det er ikke nok til at makroen kan anvende det pågældende programs ressourcer. Først skal der oprettes en såkaldt "instans" (en forekomst) af den pågældende applikation. Dette kaldes en automatiseringsserver. I modsætning til selve referencen oprettes instansen af automatiseringsserveren altid ved hjælp af kode. Først når instansen er oprettet kan makroen anvende det pågældende programs objekter, metoder og egenskaber.
En automatiseringsserver oprettes med denne sætningen CreateObject – ellers hvis programmet allerede er startet på den pågældende pc med GetObject-sætningen. De to sætninger tildeler et objekt (i dette tilfælde et applikationsobjekt) til en variabel, og som man sikkert husker fra afsnittet om Objektvariabler i artiklen om Excels objekter (ellers læs den igen :-)), gøres dette med kommandoen Set. Inden har vi selvfølgelig erklæret den pågældende variabel. Dette kan gøres som en generel objektvariabel eller som en specifik objektvariabel.
Koden kunne derfor se således ud
Dim wdApp as Object
Set wdaPP = CreateObject("Word.Application")
eller
Dim wdApp as Word.Application
Set wdaPP = CreateObject("Word.Application")
Begge dele vil fungere, men det sidste er det mest præcise. Da der sjældent er grund til at oprette en ny instans af et program, hvis der allerede findes én, kan man bruge denne kode:
Dim wdApp as Word.Application
On Error Resume Next
Set Wdapp = GetObject(, "Word.application")
If Err.Number <> 0 Then
Set Wdapp = CreateObject("Word.Application")
End if
Koden forsøger først at tildele en allerede eksisterende instans til objektvariablen. Hvis der opstår en fejl fortsættes bare til næste linje. Her testes om der er et fejlnummer, der er forskelligt fra 0. Hvis det ikke er tilfældet, var der allerede en instans, og den er nu tildelt til variablen. Koden forsætter så efter End If. Er der en fejlkode, som er forskellig fra 0, eksisterede der ingen instans, som kunne tildeles og derfor oprettes der en ny instans som tildeles variablen, og derefter fortsætter koden efter End If.
Automatisering af Word
I dette tilfælde skal man forestille sig, at jeg har en liste med navne og adresser i et Excel ark. Dette kunne fx se således ud:
Nu vil jeg gerne kunne sende breve til disse mennesker fra Excel. Det kan ske i forskellige grader af automation, hvor jeg fx har skrevet brevene i Word i forvejen (eller overfører hele teksten til brevet fra Excel) – i så fald behøver brugeren slet ikke at se Word. I dette tilfælde vil jeg imidlertid bare have startet Word i et tomt dokument, hvor Stilling, Navn og Adresse samt Postnummer og By er indsat på forhånd. Jeg vil gerne have at funktionen skal kunne startes ved et dobbeltklik på navnet, altså A-kolonnen i dette tilfælde. Det betyder at der er tale om hændelsesprogrammering. Hændelsen er knyttet til det pågældende arks klassemodul, så det er i dette, at koden skal skrives. Jeg åbner altså i dette tilfælde Ark1's klassemodul. Her finder jeg Worksheet i ruden til højre og hændelsen BeforeDoubleClick i ruden til højre.
Så indsættes de to linjer, der altid indsættes i forbindelse med hændelsesprogrammering, i dette tilfælde
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub
Og jeg er klar til at skrive min kode. Jeg starter som sædvanligt med at erklære nogle variable.
Dim wdapp As Word.Application
Dim Stilling As String
Dim Navn As String
Dim Adr As String
Dim Postnr As String
Dim By As String
wdApp skal senere bruges til at indlæse Word Applikationen i. De øvrige variabler som alle erklæres som String skal bruges til at indeholde de data, der skal overføres fra Excel til Word. Som det fremgår skal kolonnen Gevinst ikke bruges i denne sammenhæng, men det skal den måske i en helt anden sammenhæng. Man behøver altså ikke at bruge alle data fra regnearket, og man behøver heller ikke at bruge dem i den rækkefølge de står i arket. Det vil også fremgå af det videre kodearbejde.
Det næste jeg skal gøre er at undersøge om der overhovedet skal foretages mere. Det var jo kun ved dobbeltklik i A-kolonnen at der skulle dannes breve. Dette kan undersøges ved hjælp af hændelsens Target objekt. Target er den celle, der dobbeltklikkes i. Vi er interesserede i at vide om denne celle befinder sig i A-kolonnen. Dette kan gøres med
If Target.Column = 1 Then
…
End if
Idet Column 1 svarer til A-kolonnen (I stedet for at se på hele kolonnen, kunne der være sammenlignet med et Range objekt, fx cellerne A1:A100 eller lignende, men her bruger jeg altså alle cellerne i A-kolonnen.) Er der dobbeltklikket alle andre steder, skal koden ikke gøre mere, og det betyder i praksis at hele resten af koden skal stå mellem If og End If sætningerne. Hvis der er dobbeltklikket i A-kolonnen, vil jeg gerne lige se, om den celle, der er klikket i er tom. I så fald skal der heller ikke ske mere i denne omgang. Det håndterer jeg med denne If blok:
If IsEmpty(Target.Value) Then
Exit Sub
End If
Den undersøger simpelthen om den celle, der dobbeltklikkes i er tom (IsEmpty). Er den det, forlades proceduren. Læg mærke til at jeg anvender Target objektets Value egenskab her. I modsat fald forsættes. Det havde faktisk været nok at skrive If IsEmpty(Target) Then, men det kan gøre forståelsen at koden nemmere, hvis man kan se præcis, hvad der undersøges.
Nu kan man så starte Word, men det kan være en god idé at få tildelt værdier til de variable, så de er klar når Word startes om lidt.
Stilling = Target.Offset(0, 1).Value
Navn = Target.Value
Adr = Target.Offset(0, 2).Value
Postnr = Target.Offset(0, 3).Value
By = Target.Offset(0, 4).Value
Navn står i den celle, der dobbeltklikkes i (Target). De øvrige står i kolonnerne til højre for. Derfor bruger jeg Offset til at finde deres indhold. Det første tal i parentesen står for forskydning i rækker og det andet for forskydning i kolonner. Da alle informationer står i samme række skal der ikke ske nogen forskydning i dette plan. Til gengæld hentes de øvrige informationer henholdsvis 1, 2, 3 og 4 kolonner til højre for kolonnen med navnet, idet positive tal forskyder mod højre mens negative tal forskyder mod venstre. Er der tale om rækkeforskydning er negativ op mens positiv er ned.
Så er det på tide at få startet Word op, hvis det ikke allerede er tilfældet. Det gøres med koden
On Error Resume Next
Set wdApp = GetObject(, "Word.application")
If Err.Number <> 0 Then
Set Wdapp = CreateObject("Word.Application")
End If
som allerede er gennemgået ovenfor, så det skal ikke gentages her. Så er Word applikationen startet og dermed klar til at koden kan skrive, men først skal jeg lige have et tomt dokument. Det danner jeg med Word applikationens Documents objekt, som blandt andet har metoden Add.
wdApp.Documents.Add
Læg mærke til, at det nu er muligt at kalde Words objekter, metoder og egenskaber, ved at referere til den objektvariabel, der indeholder Word applikationen.
Når dokumentet er åbent kan man begynde at fylde indhold i. Dette gøres med de variabler, hvor man tidligere har gemt datane fra Excel.
wdApp.Selection.TypeText Text:=Stilling
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:=Navn
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:=Adr
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:=Postnr & " "
wdApp.Selection.TypeText Text:=By
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:="Kære " & Left(Navn, InStr(1, Navn, " "))
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
De enkelte liner skal læses som følger.
wdApp.Selection.TypeText Text:=Stilling skriver en tekst på markørens plads. Det er i et tomt dokument altid i toppen. Teksten, der skrives er her indholdet af variablen Stilling, men det kunne lige så godt være en fast tekst, som det er tilfældet i linjen med "Kære".
wdApp.Selection.TypeParagraph laver et linjeskift. Læg mærke til, at der er linjeskift mellem alle linjerne, bortset fra linjen med Postnr og linjen med By. Det skyldes at jeg gerne vil have disse til at stå på samme linje. Til gengæld vil jeg helst ikke have dem til at stå lige ved siden af hinanden. Derfor konstruktionen & " " efter Postnr som indsætter et mellemrum mellem denne variabel og den næste. Teoretisk set burde man i denne linje kunne have skrevet
wdApp.Selection.TypeText Text:=Postnr & " " & wdApp.Selection.TypeText Text:=By
men wdApp.Selection.TypeText insisterer på, at det vil stå først på en linje. Efter byen komme tre linjeskift. Det kunne have være flere eller færre, men her tager jeg altså tre. Derefter går jeg i gang med selve brevet. Jeg vil gerne indlede dette med Kære og så modtagerens fornavn. Det løses med
wdApp.Selection.TypeText Text:="Kære " & Left(Navn, InStr(1, Navn, " "))
idet Left(Navn, InStr(1, Navn, " ")) finder den del af navnet, der står før det første mellemrum. Instr(1, Navn, " ") finder positionen den første forekomst af strengen " " (mellemrum) i strengen navn, efter 1 bogstav i navnet. Left(Navn, ...) tager så den venstre del af strengen fra første bogstav til den fundne position.
NB! Denne løsning giver nogle sære resultater, når man kun har et forbogstav. Fx kunne en henvendelse starte med "Kære B." og det er knap så hensigtsmæssigt. Man kan sagtens kode os ud af det, så der bliver testet på om ovenstående var netop to langt og det andet tegn var et punktum (tester man kun på to tegn og glemmer testen for punktum på anden plads, kan vi ikke være på fornavn, med Ea, Bo og Ib), så skulle den tage hele navnet, ellers kun fornavnet, men det vil føre for vidt i denne sammenhæng, og det er da også helt almindelig betinget logik. Men nu videre med koden:
I Excel skal jeg have gjort noget ved cellemarkøren og her vælger jeg at sætte den tilbage i A1.
Range("a1").Activate
Desuden vil jeg gerne se Word dokumentet så jeg kan skrive selve teksten i dokumentet og ikke bare vil jeg se Word; jeg vil også gerne have at det skal være det aktive program:
wdApp.Visible = True
wdApp.Activate
Til sidst skal jeg bare tømme objektvariablen så den ikke ligger og tager plads op i arbejdshukommelsen.
Set wdApp = Nothing
Og lad mig slutte med at se hele koden i sammenhæng.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim wdApp As Object
Dim Stilling As String
Dim Navn As String
Dim Adr As String
Dim Postnr As String
Dim By As String
If Target.Column = 1 Then
If IsEmpty(Target) Then
Exit Sub
End If
Stilling = Target.Offset(0, 1).Value
Navn = Target.Value
Adr = Target.Offset(0, 2).Value
Postnr = Target.Offset(0, 3).Value
By = Target.Offset(0, 4).Value
On Error Resume Next
Set wdApp = GetObject(, "Word.application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
wdApp.Documents.Add
wdApp.Selection.TypeText Text:=Stilling
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:=Navn
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:=Adr
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:=Postnr & " "
wdApp.Selection.TypeText Text:=By
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeText Text:="Kære " & Left(Navn, InStr(1, Navn, " "))
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
Range("a1").Activate
wdApp.Visible = True
wdApp.Activate
Set wdApp = Nothing
End If
End Sub
Et udsnit af Word-dokumentet kunne se ud som vist her:
Den lodrette streg under "Kære" angiver markørens placering, når dokumentet åbnes.
Automatisering af Outlook
Her følger et eksempel på automatisering af Outlook til at sende mail direkte fra et regneark. Husk at lave en reference til Outlooks objektbibliotek inden kodningen.
Koden forudsætter at e-mail adresser på modtagerne står i A-kolonnen. I B-kolonnen (i det konkrete eksempel) er varenavn. I C-kolonnen et bestilt antal. I princippet kunne der selvfølgelig stå hvad som helst, der kan indsættes som variabler i en fast tekst. Eksemplet forudsætter 10 kunder i arket fra A1 til A10 men det kan nemt ændres.
I koden erklæres først en række variable. Først to objektvariable til henholdsvis Outlook applikationen og de enkelte mails. Dernæst fire variable til at rumme de informationer, der hentes fra regnearket. Endelig en variabel til teksten i mailen.
Så hentes Outlook hvis programmet ikke allerede er startet. Nu gennemløbes en løkke så 10 gange. Dette kan selvfølgelig rettes, så det bliver et variabelt antal. For hvert gennemløb hentes information fra en række i regnearket, startende med række 1. Skal man starte i en en anden række, kan For I = 1 To 10 ændres. For hver række oprettes en ny mail med værdien i kolonne A som modtager, teksten fra kolonne B som emne, og Ordlyden fra variablen MsgTxt som tekst i mailen, i dette tilfælde fx "Deres bestilte 8 kålpølser er hjemkommet, og kan afhentes.". Mailen gemmes og sendes.
Koden starter som sædvanligt med erklæringerne. Læg mærke til at der her skal erklæres en objektvariabel til både selve Outlook og til den enkelte mail. Der testes her ikke for hvilken kolonne, der er klikket i, da der ikke er tale om en hændelsesprocedure men en ganske almindelig sub-rutine.
Outlook hentes eller startes. Loopet gennemløbes 10 gange. For hvert gennemløb genereres en mail med teksten "Deres bestilte ????? stk. xxxxxxx er hjemkommet, og kan afhentes." hvor ????? er antallet og xxxxx er varenavnet. Disse informationer hentes fra regnearkets B og C kolonne og mailen gemmes og sendes derefter til adressen i A-kolonnen.
Det er koden her, der generer de enkelte mail:
With olNewMail
.Recipients.Add Recep
.Body = MsgTxt
.Subject = Varenavn
.ReadReceiptRequested = False
.OriginatorDeliveryReportRequested = False
.Save
.Send
End With
Læg mærke til at NewMail objektet har en del egenskaber og metoder. Recipients er i sig selv et objekt og der skal derfor tilføjes et for hver mail (Add). Havde der været flere modtagere af samme mail, skulle de alle have være tilføjet. Body, Subject, ReadReceiptRequested og OriginatorDeleveryReportRequested er egenskaber. Jeg ønsker ikke at belaste mailserveren mere end højest nødvendigt, så jeg frabeder mig kvitteringer. Save og Send er metoder til objektet.
Og her er så den samlede kode.
Sub SendViaOutlook()
Dim olApp As New Outlook.Application
Dim olNewMail As Object
Dim Recep As String
Dim MsgTxt As String
Dim Varnavn As String
Dim varantal As Long
On Error Resume Nex
Set olApp = GetObject("Outlook.Application")
If Err.Number <> 0 Then
Set olApp = CreateObject("Outlook.Application")
End If
For i = 1 To 10
Recep = Range("A" & i).Value
Varenavn = Range("B" & i).Value
Vareantal = Range("C" & i).Value
MsgTxt = "Deres bestilte " & Vareantal & " stk. " & Varenavn _
& " er hjemkommet, og kan afhentes."
Set olNewMail = CreateItem(olMailItem)
With olNewMail
.Recipients.Add Recep
.Body = MsgTxt
.Subject = Varenavn
.ReadReceiptRequested = False
.OriginatorDeliveryReportRequested = False
.Save
.Send
End With
Next i
End Sub