Start » VBA-exempel för Excel | Effektiva lösningar på konkreta problem » Excel | Interaktion med andra program » Importera Access-tabell i Excel via VBA

Importera Access-tabell i Excel via VBA





about-accessAccess är ju sedan länge en del av Office-sviten och det finns därför ett stort antal Access-databaser runt om på företag. I det här exemplet kommer vi att exportera en Access-tabell till Excel via programkod i Excel VBA.

Detta moment kan i och för sig även utföras via menyer i Access, men vi utgår från att vi vill kunna automatisera den här importen via ett Excelmakro.

Ett tänkbart scenario är att du varje dag exporterar Access-data till Excel. En perfekt lösning är en enkel uppsättning Excelmakron som automatiserar det här arbetet åt dig.

ADO – ActiveX Data Objects

I exemplet skapar vi en koppling från Excel till Access via ADO (ActiveX Data Objects) som är en teknik för att koppla upp sig mot databaser och sedan hämta in datan via så kallade recordsets. Tekniken introducerades 1996 av Microsoft och används i en stor rad sammanhang.

Efter att databasen har kopplats upp mot Excel så exporteras sedan all data från Access till aktivt kalkylblad.

Ladda in ADO-objekten i Excel

För att vi skall kunna nyttja ADO så måste vi i Excels VBA-editor ladda in ADO-objekten.
Detta gör du via Tools – References i VBA-editorn. Se bilden nedan:

access-excel-ado-objects

VBA-kod för att importera Access-tabell i Excel

I kodexemplet så kopplar vi upp oss mot Accessdatabasen db.mdb (eller db.accdb om databasen är sparad i Accessversion 2007 eller nyare) som ligger i samma folder som aktiv Excelfil. Du ser i koden nedan hur du skriver för att peka på en databas som ligger i en annan folder.

strDB = ThisWorkbook.Path & "\" & "db.mdb"
'strDB = "C:\vba\db.mdb" 'om i annan folder

Du måste också ange namnet på den specifika tabell i Access som du vill hämta data från:

strTabell = "pengar_2011"

Nedan ser du den kompletta koden som utför operationen.

Databas: Access 2003 eller tidigare

Sub Importera_Access_I_Excel()
 
'dimensions
Dim datConnection As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim strDB, strSQL As String
Dim strTabell As String
Dim lngTabeller As Long
Dim i As Long
 
'välj en av dessa sökvägar till din Accessdatabas (t ex db.mdb)
strDB = ThisWorkbook.Path & "\" & "db.mdb"
'strDB = "C:\vba\db.mdb" 'om i annan folder

'namn på tabellen i Access
strTabell = "pengar_2011"
 
'skapa ADO-kopplingen
Set datConnection = New ADODB.Connection
Set recSet = New ADODB.Recordset
datConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =" & strDB & ";"
 
'SQL-förfrågan
strSQL = "SELECT * FROM " & strTabell & ""
recSet.Open strSQL, datConnection
 
'kopierar data till kalkylbladet (från och med rad 2 i Excel)
ActiveSheet.Cells(2, 1).CopyFromRecordset recSet
 
'kopierar kolumnrubriker (rad 1 i Excel)
lngCampos = recSet.Fields.Count
For i = 0 To lngCampos - 1
ActiveSheet.Cells(1, i + 1).Value = recSet.Fields(i).Name
Next
 
'kopplar ned
recSet.Close: Set recSet = Nothing
datConnection.Close: Set datConnection = Nothing
 
End Sub

Databas: Access 2007

Sub Importera_Access_I_Excel()
 
'dimensions
Dim datConnection As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim strDB, strSQL As String
Dim strTabell As String
Dim lngTabeller As Long
Dim i As Long
 
'välj en av dessa sökvägar till din Accessdatabas (t ex db.mdb)
strDB = ThisWorkbook.Path & "\" & "db.accdb"
'strDB = "C:\vba\db.mdb" 'om i annan folder

'namn på tabellen i Access
strTabell = "pengar_2011"
 
'skapa ADO-kopplingen
Set datConnection = New ADODB.Connection
Set recSet = New ADODB.Recordset
datConnection.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
"Data Source =" & strDB & ";"
 
'SQL-förfrågan
strSQL = "SELECT * FROM " & strTabell & ""
recSet.Open strSQL, datConnection
 
'kopierar data till kalkylbladet (från och med rad 2 i Excel)
ActiveSheet.Cells(2, 1).CopyFromRecordset recSet
 
'kopierar kolumnrubriker (rad 1 i Excel)
lngCampos = recSet.Fields.Count
For i = 0 To lngCampos - 1
ActiveSheet.Cells(1, i + 1).Value = recSet.Fields(i).Name
Next
 
'kopplar ned
recSet.Close: Set recSet = Nothing
datConnection.Close: Set datConnection = Nothing
 
End Sub




9 kommentarer till “Importera Access-tabell i Excel via VBA”

  1. Björn Engström

    Hej
    Det är nästan exakt den kod jag behöver, men den fungerar inte. Får körfel -2147217865 (80040e37), Microsoft Jet kan inte hitta tabellen eller frågan.

    Har ni några bra förslag hur man fixar detta? Alla sökvägar och stavning är trippelkontrollerade.
    Sen undrar jag om ni kan ge ett bra tips om hur man kan komplitera detta script med att när jag t.ex skriver ett kundnr i excel “A1″ och detta script då hämtar enbart denna kunds information från access. Är det möjligt?
    Har tyvärr inte så stora kunskaper och erfarenhet av VBA än, men vill helt klart lära mig mera.

  2. Excelkungen

    Hej Björn,

    Jag har precis testkört makrot och det fungerade bra här hos mig.

    Databasen i exemplet heter “db.mdb” och är sparad som Access-version “2000-2003″.
    Tabellen i databasen heter “pengar_2011″.
    Fälten kan heta vad som helst.

    Har du laddat in “ADO-objekten” enligt instruktionen ovan?

  3. Björn Engström

    Hej
    Ja det har laddat in Microsoft AtiveX Data Object 2.8. Kör med Access 2010 och excel 2010.
    Kan det vara ngt med dessa versioner som spökar?

  4. Björn Engström

    Hej igen
    Stegar jag fram så kommer jag fram till raden:
    datConnection.open “Provider = Microsoft….
    Sen kommer felmeddelande fram.
    Och både databasen och excelfilen ligger i samma katalog.

  5. Björn Engström

    Hej igen, En rättelse. Accesen är 2007 inte 2010.
    Nu har jag kommit lite längre vid stegning, kommer fram till raden recSet.open strSQL…… , sen kommer felmeddelandet.

  6. Björn Engström

    Hej Igen
    Nu funkar det av någon konstig anledning. Fråga mig inte hur..
    Tack för hjälpen :)

  7. Excelkungen

    Du använder säkerligen databasformatet “.accdb” i och med Access 2007. I artikeln ovan har jag lagt till ett kodexempel för den versionen, i och med att den äldre “JET-uppkopplingen” inte fungerar för Access 2007. Hoppas det fungerar nu.

    /Anders

  8. Mats

    Finns det något sätt att slippa få med den första kolumnen som innehåller nyckelfältet från Access?
    Tack för en enkel och tydlig funktion

  9. Excelkungen

    Mats,

    En enkel lösning är att lägga till följande bit kod (som tar bort kolumn A) i slutet av makrot:

    Columns(“A:A”).Delete Shift:=xlToLeft

    /anders

Lämna en kommentar