Start » VBA-exempel för Excel | Effektiva lösningar på konkreta problem » Excel | Interaktion med andra program » Importera data från en stängd Excelbok (ADO)

Importera data från en stängd Excelbok (ADO)





Med hjälp av ADO (ActiveX Data Objects) så möjliggörs att från ett kalkylark importera data från en annan Excelbok, och detta utan att denna externa bok överhuvudtaget behöver öppnas.

Importen är i normalfallet snabb, och kan vara ett bra sätt att effektivisera vissa periodvisa rutiner på.

Steg 1: En indatafil i Excel

Indatafilen, dvs den Excelfil som vi kommer att importera data från, kan vara vilken Excelbok som helst. I det här exemplet ser vår indatafil ut så här:

Innehållet i den Excelfil vars data vi ska importera via ADO.

Steg 2: En Excelfil som innehåller makrot för importen

Även denna bok kan vara en helt vanlig Excelbok, med den lilla skillnaden att alla Excelböcker som innehåller makron måste sparas i formatet “.xlsm” (fr o m Excel 2007).

När vi har vår “makrobok” på plats så är det dags att skriva in makrokoden i VBA. Tryck Alt + F11 för att öppna VBA-hanteraren. Skapa därefter en ny modul enligt  bilden nedan:

Lägg till en ny modul i VBA-hanteraren.

3. Lägg in följande makrokod i den nya modulen

 

Sub ImporteraExcelTillExcel_ADO()
 
'--------------------------------------------------------------
'importerar data från en extern Excelbok utan att öppna den
'--------------------------------------------------------------

'variabeldeklareringar
Dim datConnection As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim recRubrik As ADODB.Field
Dim strDB, strSQL As String
Dim strDriver As String
Dim i As Long
 
'sökväg till den externa Excelfilen
strDB = ThisWorkbook.Path & "\" & "MinExcelFil.xlsx" 'filen i samma folder
'strDB = "C:\ExcelVBA\MinExcelFil.xlsx" 'filen i annan folder

'uppkoppling
Set datConnection = New ADODB.Connection
Set recSet = New ADODB.Recordset
strDriver = "DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
datConnection.Open "DBQ=" & strDB & ";" & strDriver & ";UID=admin;"
 
'SQL-förfrågan
strSQL = "SELECT * FROM [Sheet1$A1:Z9999]" 'cellområde på visst ark
'strSQL = "SELECT * FROM [Cellområdesnamn]" 'för namngivna cellområden

'öppnar ett "recordset"
recSet.Open strSQL, datConnection, adOpenStatic
 
'kopierar in ny data
ActiveSheet.Range("A2").CopyFromRecordset recSet
 
'kopierar kolumnrubriker
i = 1
For Each recRubrik In recSet.Fields
 ActiveSheet.Cells(1, i) = _
  recRubrik.Name: i = i + 1
Next recRubrik
 
'kopplar ned (viktigt!)
recSet.Close
datConnection.Close
 
'stänger ned objekten (viktigt!)
Set recSet = Nothing
Set datConnection = Nothing
 
End Sub

4. Justeringar av makrokoden

I exempelkoden så finns det två delar som du bör justera, nämligen

  • sökväg till den externa Excelfilen (ange sökväg till önskad fil)
  • SQL-förfrågan (ange vilket cellområde som ska importeras, alternativt ett namngivet område)

5. Ladda in ADO-objekten i VBA

Detta kanske låter mycket svårare än det är. Vad det handlar om är att vi måste “slå på” VBA:s funktion för att kunna hantera ADO. Detta gör du “en gång för alla” för just den här Excelboken. Nedanstående bild visar exakt hur du går tillväga:

Aktivering av ADO-bibliotek i Excel VBA.

 

6. Testkör importen

Gå därefter tillbaks ut till kalkylbladet och testkör makrot. Importen av den testfil som introducerades i början av den här artikeln ger följande slutresultat:

Resultatet av ADO-importen från Excel till Excel.

Som man kan se så har all data importerats, inklusive kolumnrubrikerna. Det enda som inte importeras är cellformat (kolumnrubrikerna var skrivna i fetstil i orginalfilen).

7. Du glömmer väl inte…

att först av allt göra säkerhetskopia på alla filer som du håller på och trixar med? Detta gäller förstås alltid, och inte minst om man tankar in och ut via ADO, eftersom innehåll skrivs över “i det tysta”.





23 kommentarer till “Importera data från en stängd Excelbok (ADO)”

  1. Dahlgrenen

    När jag försöker använda koden får jag ett fel på raden
    datConnection.Open “DBQ=” & strDB & amp, “,” & strDriver & amp, “,UID=admin,”

    felet ger texten
    ‘runtime error -2147467259(8004005}
    [microsoft][ODBC driver Maneger]datasource not found and no default driver specified’

    Vad kan det vara för fel? Tack för hjälp!

  2. Excelkungen

    Hej,

    Tyvärr så hade alla “&”-tecken förvandlats till “&amp” (detta händer på webben ibland).

    Jag har rättat till detta i koden ovan, så nu kan du testa igen!

    /Anders

  3. Dahlgrenen

    Tack så mycket för hjälpen! Toppen bra hemsida med.

    Men nu får jag istället ett fel på raden,
    recSet.Open strSQL, datConnection, adOpenStatic
    några förslag?

  4. Janne

    När jag försöker använda koden får jag ett fel på raden

    datConnection.Open “DBQ=” & strDB & “;” & strDriver & “;UID=admin;”

    med meddelande” Microsovt ODBC Excel Driver Failuere creating file

  5. Excelkungen

    Janne,

    Det kan vara sökvägen som strular. Kontrollera sökvägen alt. testa också att lägga filen på ett annat ställe (gärna annat ställe än C:).

  6. Excelkungen

    Dahlgrenen,

    Kanske måste du justera SQL-frågan.

    strSQL = “SELECT * FROM [Sheet1$A1:Z9999]

    hänvisar till ett cellområde på ett arbetsblad som heter “Sheet1″.

  7. Dahlgrenen

    Okej tack! jag vet inte vad jag ändrade men på något sätt fick jag det plötsligt att funka, toppen!

  8. Dahlgrenen

    Men en fråga till, eller två, om du vet,

    Går det att välja flera separata områden att kopiera? Som A2 och G10

    Och går det att att kopiera till andra celler än dem valda, tillexempel att cellen B5 i en fil ska hamna i cellen C6 i den andra?

  9. Excelkungen

    Ett sätt att ta in separata områden blir ju att skapa två Recordset:

    Set recSet = New ADODB.Recordset
    Set recSetB = New ADODB.Recordset

    och sedan köra de båda:

    recSet.Open strSQL, datConnection, adOpenStatic
    recSetB.Open strSQL, datConnection, adOpenStatic

    för att sedan skriva in båda (här ser du också var de kommer att hamna):

    ActiveSheet.Range(“A2″).CopyFromRecordset recSet
    ActiveSheet.Range(“E2″).CopyFromRecordset recSetB

    Kolumnrubrikerna finns här:

    i = 1
    For Each recRubrik In recSet.Fields
    ActiveSheet.Cells(1, i) = _
    recRubrik.Name: i = i + 1
    Next recRubrik

    där Cells(1,i) betyder cell A1 (1=RAD, i=1=KOLUMN). Du kan köra denna rutin två gånger inkl. recSetB.

  10. Dahlgrenen

    Tack så mycket.
    Då förstår jag hur man kopierar in till olika områden.Men jag missade nog hur man kopierar från olika områden?

  11. Dahlgrenen

    Nu förstår jag, det funkade inte med en gång bara.

  12. Emma

    När jag använder denna kod får jag ett problem.
    Jag transporterar 4.7 men får fram talet 47 Vad kan man göra åt detta?

    Tack på förhand

  13. K

    “Metoden stöds inte” trots att man öppnar med Excel 2010. Vad beror det på?

    Har ju fungerat tidigare på annan dator..

  14. Excelkungen

    Hej K,

    Jag har precis testat på Excel 2010 och det fungerade bra. Har du laddat in ADO-objekten enligt punkt 5 ovan?

    /anders

  15. Roland Andersson

    När jag kör får jag felet USER DEFINED TYPE NOT DEFINED OCH EN HÄNVISNING TILL Koden Dim datConnection As ADOB Connection

  16. Excelkungen

    Roland,

    Har du verkligen laddat in ADO-objekten enligt punkt 5 ovan? I VBA-hanteraren, under “Tools – References” markerar du “Microsoft ActiveX Data Objects 2.8 Library”.

    /Anders
    ExcelKungen.com

  17. Roland Andersson

    Ja jag har laddat in referensen och alla jag har konsulterat får samma resultat Jag har prövat många andra programsnuttar också och väldigt många använder sig inte av ADO överhuvudtaget Men inget fungerar än så länge Jag har också använt VB 6 med samma resultat så det verkar vara en Microsoftbug eller hur.

  18. Roland Andersson

    Ja nu har jag äntligen själv kommit på var problemet ligger
    Man måste också referera till Microsoft recordset biblioteket det räcker inte med Active x Data Object

  19. Ola

    Hej!
    Jag får samma fel som Roland i kom.15.
    Jag har laddat ner ADO 2.8. Vad är det han menar med referens till Ms recordset bibliotek.

  20. Robban

    Hej, jag vill importera från en tabell som har underrubriker. Går det inte på något sätt att bara importera hela området som det ser ut? Måste man importera rubrikerna för sig? Har också problem med underrubrikerna eftersom de är text och datan siffror. Det medför att inte underrubrikerna skrivs ut.

    MVH
    Robban

  21. Carolina

    Hejsan,

    Importerar utan problem, nästan, från ett antal olika böcker. Dock så hoppas två kolumer över och värden i de kolumnerna ignoreras. Vad kan vara anledningen till detta?

    Jag hämtar data från namngivna områden, och kolumnerna är inkluderade i det namngivna området.

  22. Patrik

    Hej!
    Jag fick precis som Dahlgrenen, problem med:
    recSet.Open strSQL, datConnection, adOpenStatic
    Jag lade till en bevakning i editorn, och kontrollerade vart det gick fel. Sedan fick jag hjälp med att testa selectsatsen. Summa Summarum, det gick inte att hänvisa till ett blad som “Sheet” eller “Blad”, men när jag istället använde “populärnamnet”, dvs. det namn jag döpt fliken till, då fungerade det.

    Alltså, jag ersatte:
    strSQL = “SELECT * FROM [Sheet1$A1:Z9999]”
    Med
    strSQL = “SELECT * FROM ["mitt bladnamn"$A1:Z9999]”

    Fråga mig inte varför…;)

  23. Ola

    Hej, fungerar detta på URLer – alltså en cell vars text är en länk i källan. Kommer länken över till målet?

Lämna en kommentar