Metoder för överföring av data till Excel från Visual Basic

Anteckning

Office 365 ProPlus byter namn till Microsoft 365-appar för företag. Mer information om den här ändringen finns i det här blogginlägget.

Sammanfattning

I den här artikeln beskrivs flera olika metoder för överföring av data Microsoft Excel data från ditt Microsoft Visual Basic program. I den här artikeln presenteras också fördelarna och nackdelarna för varje metod så att du kan välja den lösning som passar dig bäst.

Mer information

Den metod som oftast används för att överföra data till en Excel är automation. Automation ger dig största flexibiliteten att ange platsen för dina data i arbetsboken samt möjligheten att formatera arbetsboken och göra olika inställningar vid körning. Med Automation kan du använda flera olika metoder för att överföra data:

  • Överföra data cell för cell
  • Överföra data i en matris till ett cellområde
  • Överföra data i en ADO-postuppsättning till ett cellområde med metoden CopyFromRecordset
  • Skapa en frågetabell i ett Excel kalkylblad som innehåller resultatet av en fråga i en ODBC- eller OLEDB-datakälla
  • Överföra data till Urklipp och klistra sedan in innehållet i Urklipp i ett Excel kalkylblad

Det finns även metoder som du kan använda för att överföra data till Excel som inte nödvändigtvis kräver automation. Om du kör ett program på serversidan kan det här vara ett bra sätt att ta bort stora mängder data från dina klienter. Följande metoder kan användas för att överföra data utan automation:

  • Överföra data till en tabbavgränsad eller kommaavgränsad textfil som Excel senare parsa in i celler på ett kalkylblad
  • Överföra data till ett kalkylblad med ADO
  • Överföra data till Excel med hjälp av DDE (Dynamic Data Exchange)

I följande avsnitt finns mer information om var och en av de här lösningarna.

Obs! När du använder Microsoft Office Excel 2007 kan du använda det nya filformatet Excel 2007-arbetsbok (*.xlsx) när du sparar arbetsböckerna. Det gör du genom att leta upp följande rad med kod i följande kodexempel:

oBook.SaveAs "C:\Book1.xls"

Ersätt den här koden med följande rad med kod:

oBook.SaveAs "C:\Book1.xlsx"

Northwind-databasen ingår inte heller i Office 2007 som standard. Men du kan ladda ned Northwind-databasen från Microsoft Office Online.

Använda Automation för att överföra data cell för cell

Med Automation kan du överföra data till ett kalkylblad en cell i taget:

Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object 
    
'Start a new workbook in Excel    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add
      
'Add data to cells of the first worksheet in the new workbook    
Set oSheet = oBook.Worksheets(1)    
oSheet.Range("A1").Value = "Last Name"    
oSheet.Range("B1").Value = "First Name"    
oSheet.Range("A1:B1").Font.Bold = True    
oSheet.Range("A2").Value = "Doe"    
oSheet.Range("B2").Value = "John"     

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

Överföring av data cell för cell kan vara en perfekt godtagbar metod om mängden data är liten. Du har flexibiliteten att placera data var som helst i arbetsboken och kan formatera cellerna villkorsstyrd vid körning. Den här metoden rekommenderas dock inte om du har en stor mängd data som ska överföras till en Excel arbetsbok. Varje Område-objekt som du hämtar vid körning resulterar i en gränssnittsbegäran så att det går långsamt att överföra data på det här sättet. Dessutom har Microsoft Windows 95 och Windows 98 en begränsning på 64K på gränssnittsförfrågningar. Om du når eller överskrider den här 64k gränsen på gränssnittsförfrågningar kan Automation-servern (Excel) sluta svara, eller så kan du få fel som anger låg minneskapacitet.

En gång till går det bara att överföra data cell för cell för små mängder data. Om du behöver överföra stora mängder data till ett Excel kan du överväga en av de lösningar som presenteras senare.

Mer exempelkod för automatisering av Excel finns i Så här automatiserar du Microsoft Excel från Visual Basic.

Använda automation för att överföra en datamatris till ett område i ett kalkylblad

En matris med data kan överföras till ett område med flera celler på en gång:

Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object     

'Start a new workbook in Excel    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add     

'Create an array with 3 columns and 100 rows    
Dim DataArray(1 To 100, 1 To 3) As Variant    
Dim r As Integer    
For r = 1 To 100       
   DataArray(r, 1) = "ORD" & Format(r, "0000")       
   DataArray(r, 2) = Rnd() * 1000       
   DataArray(r, 3) = DataArray(r, 2) * 0.7    
Next     

'Add headers to the worksheet on row 1    
Set oSheet = oBook.Worksheets(1)    
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")     

'Transfer the array to the worksheet starting at cell A2    
oSheet.Range("A2").Resize(100, 3).Value = DataArray        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

Om du överför data med hjälp av en matris i stället för cell efter cell kan du inser en enorm prestandavinst med en stor mängd data. Tänk på den här raden från koden ovan som överför data till 300 celler i kalkylbladet:

   oSheet.Range("A2").Resize(100, 3).Value = DataArray

Den här raden representerar två gränssnittsförfrågningar (en för områdesobjektet som områdesmetoden returnerar och en annan för områdesobjektet som metoden Ändra storlek returnerar). Å andra sidan skulle överföring av datacell med cell kräva begäranden för 300 gränssnitt till områdesobjekt. När det är möjligt kan du dra nytta av att överföra data massutifrån och minska antalet användargränssnittsförfrågningar du gör.

Använda automation för att överföra en ADO-postuppsättning till ett kalkylbladsområde

Excel 2000 introducerade metoden CopyFromRecordset som gör att du kan överföra en ADO-postuppsättning (eller DAO) till ett område i ett kalkylblad. Följande kod visar hur du kan automatisera Excel 2000, Excel 2002 eller Office Excel 2003 och överföra innehållet i tabellen Orders i Northwind Sample Database med metoden CopyFromRecordset.

'Create a Recordset from all the records in the Orders table    
Dim sNWind As String    
Dim conn As New ADODB.Connection    
Dim rs As ADODB.Recordset    
sNWind = _       
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       sNWind & ";"    
conn.CursorLocation = adUseClient    
Set rs = conn.Execute("Orders", , adCmdTable)        

'Create a new workbook in Excel    
Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add    
Set oSheet = oBook.Worksheets(1)        

'Transfer the data to Excel    
oSheet.Range("A1").CopyFromRecordset rs        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit        
'Close the connection    
rs.Close    
conn.Close

Obs! Om du använder Office 2007-versionen av Northwind-databasen måste du ersätta följande rad med kod i kodexempel:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"

Ersätt den här kodraden med följande rad med kod:

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"

Excel 97 innehåller även en metod med CopyFromRecordset, men du kan bara använda den med en DAO-postuppsättning. CopyFromRecordset med Excel 97 stöder inte ADO.

Mer information om hur du använder ADO och metoden CopyFromRecordset finns i Så här överför du data från en ADO-postuppsättningtill Excel med automation.

Använda automation för att skapa en frågetabell i ett kalkylblad

Ett QueryTable-objekt representerar en tabell som skapats från data som returnerats från en extern datakälla. När du automatiserar Microsoft Excel kan du skapa en Frågetabell genom att helt enkelt koppla en anslutningssträng till en OLEDB- eller EN ODBC-datakälla tillsammans med en SQL sträng. Excel på ansvaret för att generera postuppsättningen och infoga den i kalkylbladet på den plats du anger. Med hjälp av Frågetabeller har du flera fördelar jämfört med metoden CopyFromRecordset:

  • Excel hanterar skapandet av postuppsättningen och dess placering i kalkylbladet.
  • Frågan kan sparas med frågetabellen så att den kan uppdateras vid ett senare tillfälle för att hämta en uppdaterad postuppsättning.
  • När en ny Frågetabell läggs till i kalkylbladet kan du ange att data som redan finns i cellerna i kalkylbladet flyttas så att de får plats med nya data (mer information finns i egenskapen RefreshStyle).

Följande kod visar hur du kan automatisera Excel 2000, Excel 2002 eller Office Excel 2003 för att skapa en ny frågetabell i ett Excel-kalkylblad med hjälp av data från Northwind-exempeldatabasen:

'Create a new workbook in Excel    
Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add    
Set oSheet = oBook.Worksheets(1)        

'Create the QueryTable    
Dim sNWind As String    
sNWind = _       
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"    
Dim oQryTable As Object    
Set oQryTable = oSheet.QueryTables.Add( _    
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       
sNWind & ";", oSheet.Range("A1"), "Select * from Orders")    oQryTable.RefreshStyle = xlInsertEntireRows    
oQryTable.Refresh False        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

Använda Urklipp

Data Windows urklipp kan också användas som en mekanism för överföring av data till ett kalkylblad. Om du vill klistra in data i flera celler i ett kalkylblad kan du kopiera en sträng där kolumner avgränsas med tabbtecken och rader avgränsas med vagnreturer. Följande kod visar hur du kan Visual Basic använda Urklipp-objektet för att överföra data till Excel:

'Copy a string to the clipboard    
Dim sData As String    
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _            & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _            
    & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"    
Clipboard.Clear     
Clipboard.SetText sData        

'Create a new workbook in Excel    
Dim oExcel As Object    
Dim oBook As Object    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add         

'Paste the data    
oBook.Worksheets(1).Range("A1").Select    
oBook.Worksheets(1).Paste        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

Skapa en avgränsad textfil som Excel kan tolka i rader och kolumner

Excel kan öppna tabbavgränsade eller kommaavgränsade filer och tolka data i celler på rätt sätt. Du kan dra nytta av den här funktionen när du vill överföra en stor mängd data till ett kalkylblad när du använder liten, om sådan finns, automation. Det här kan vara en bra metod för ett klientserverprogram eftersom textfilen kan genereras på serversidan. Du kan sedan öppna textfilen på klienten, med automation där det är lämpligt.

Följande kod visar hur du kan skapa en kommaavgränsad textfil från en ADO-postuppsättning:

'Create a Recordset from all the records in the Orders table    
Dim sNWind As String    
Dim conn As New ADODB.Connection   
Dim rs As ADODB.Recordset    
Dim sData As String    
sNWind = _       
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       sNWind & ";"    
conn.CursorLocation = adUseClient    
Set rs = conn.Execute("Orders", , adCmdTable)        

'Save the recordset as a tab-delimited file    
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)    
Open "C:\Test.txt" For Output As #1    
Print #1, sData    
Close #1         

'Close the connection    
rs.Close    
conn.Close        

'Open the new text file in Excel    
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _       Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus

Obs! Om du använder Office 2007-versionen av Northwind-databasen måste du ersätta följande rad med kod i kodexempel:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       
   sNWind & ";"

Ersätt den här kodraden med följande rad med kod:

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _       
   sNWind & ";"

Om textfilen har filnamnstillägget .CSV, Excel filen öppnas utan att Textimportguiden visas och automatiskt förutsätts att filen är kommaavgränsad. Om filen har filnamnstillägget .TXT så parsar Excel filen automatiskt med tabbavgränsare.

I föregående kodexempel startade Excel startades med kommandot Shell, och namnet på filen användes som ett kommandoradsargument. Ingen automatisering användes i det föregående exemplet. Om så önskas kan du emellertid använda en minimal mängd automatisering för att öppna textfilen och spara den i Excel-arbetsboksformat:

'Create a new instance of Excel    
Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object    
Set oExcel = CreateObject("Excel.Application")            

'Open the text file   
 Set oBook = oExcel.Workbooks.Open("C:\Test.txt")        

'Save as Excel workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal    
oExcel.Quit

Överföra data till ett kalkylblad med hjälp av ADO

Med Microsoft Jet OLE DB Provider kan du lägga till poster i en tabell i en Excel arbetsbok. En "tabell" i Excel är bara ett område med ett definierat namn. Den första raden i området måste innehålla rubrikerna (eller fältnamnen) och alla efterföljande rader innehåller posterna. Följande steg illustrerar hur du kan skapa en arbetsbok med en tom tabell med namnet Mintabell.

Excel 97, Excel 2000 och Excel 2003
  1. Starta en ny arbetsbok i Excel.

  2. Lägg till följande rubriker i cellerna A1:B1 i Blad1:

    A1: Förnamn B1: Efternamn

  3. Formatera cell B1 som högerjusterad.

  4. Välj A1:B1.

  5. Välj Namn på Infoga-menyn och välj sedan Definiera. Ange namnet Mintabell och klicka på OK.

  6. Spara den nya arbetsboken som C:\Book1.xls och avsluta Excel.

Om du vill lägga till poster i MyTable med ADO kan du använda kod som liknar följande:

'Create a new connection object for Book1.xls    
Dim conn As New ADODB.Connection    
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _       
    "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Bill', 'Brown')"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Joe', 'Thomas')"    
conn.Close
Excel 2007
  1. I Excel 2007 startar du en ny arbetsbok.

  2. Lägg till följande rubriker i cellerna A1:B1 i Blad1:

    A1: Förnamn B1: Efternamn

  3. Formatera cell B1 som högerjusterad.

  4. Välj A1:B1.

  5. Klicka på fliken Formler i menyfliksområdet och sedan på Definiera namn. Skriv namnet Mintabell och klicka sedan på OK.

  6. Spara den nya arbetsboken C:\Book1.xlsx och avsluta den Excel.

Om du vill lägga till poster i MyTable-tabellen med hjälp av ADO använder du kod som liknar följande kodexempel.

'Create a new connection object for Book1.xls    
Dim conn As New ADODB.Connection    
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _       
   "Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Scott', 'Brown')"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Jane', 'Dow')"    
conn.Close

När du lägger till poster i tabellen på det här sättet bibehålls formateringen i arbetsboken. I föregående exempel formateras nya fält som lagts till i kolumn B med rätt justering. Varje post som läggs till på en rad lånar formatet från raden ovanför den.

När en post läggs till i en eller flera celler i kalkylbladet skriver den över data som tidigare fanns i de cellerna. Med andra ord:, rader i kalkylbladet "trycks inte ned" när nya poster läggs till. Tänk på det här när du utformar layouten för data i dina kalkylblad.

Anteckning

Metoden för att uppdatera data i ett Excel-kalkylblad med hjälp av ADO eller med DAO fungerar inte i Visual Basic för programmiljö i Access när du har installerat Office 2003 Service Pack 2 (SP2) eller efter att du har installerat uppdateringen för Access 2002 som ingår i Microsoft Knowledge Base-artikeln 904018. Metoden fungerar bra i Visual Basic programmiljö från andra Office program, till exempel Word, Excel och Outlook.

Mer information finns i följande artikel:

Du kan inte ändra, lägga till eller ta bort data i tabeller som är länkade till en Excel-arbetsbok i Office Access 2003 eller Access 2002

Mer information om hur du använder ADO för att komma åt en Excel-arbetsbok finns i Köra frågor och uppdatera Excel med ADO från ASP.

Använd DDE för att överföra data till Excel

DDE är ett alternativ till automatisering som ett sätt att kommunicera med Excel och överföra data, men med tack vare nya Automation och COM är DDE inte längre den rekommenderade metoden för att kommunicera med andra program och bör bara användas när det inte finns någon annan lösning tillgänglig för dig.

Om du vill överföra data till Excel med DDE kan du använda metoden LinkPoke för att flytta data till ett visst cellområde, eller så använder du metoden LinkExecute för att skicka kommandon som Excel kommer att köra.

I följande kodexempel visas hur du kan upprätta en DDE-konversation med Excel så att du kan skicka data till celler i ett kalkylblad och köra kommandon. Om du använder det här exemplet måste en arbetsbok med namnet MyBook.xls redan vara öppen i en instans av Excel för att en DDE-konversation ska kunna upprättas till LinkTopic Excel|MyBook.xls.

Anteckning

När du använder Excel 2007 kan du spara arbetsböckerna .xlsx det nya filformatet. Kontrollera att du uppdaterar filnamnet i följande kodexempel. I det här exemplet representerar Text1 en textr box-kontroll i ett Visual Basic formulär:

'Initiate a DDE communication with Excel    
Text1.LinkMode = 0    
Text1.LinkTopic = "Excel|MyBook.xls"    
Text1.LinkItem = "R1C1:R2C3"    
Text1.LinkMode = 1        

'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls    
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _                             
             "four" & vbTab & "five" & vbTab & "six"    
Text1.LinkPoke        

'Execute commands to select cell A1 (same as R1C1) and change the font  format 
Text1.LinkExecute "[SELECT(""R1C1"")]"    
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"        

'Terminate the DDE communication    
Text1.LinkMode = 0

När du använder LinkPoke Excel radkolumnnotationen (R1C1) för LinkItem. Om du för över data till flera celler kan du använda en sträng där kolumnerna avgränsas med tabbar och rader avgränsas med vagnreturer.

När du använder LinkExecute för att be Excel att utföra ett kommando måste du ge Excel kommandot i syntaxen för Excel Makrospråk (XLM). XLM-dokumentationen ingår inte i version Excel 97 och senare.
DDE är inte en rekommenderad lösning för att kommunicera med Excel. Automation ger största möjliga flexibilitet och ger dig större tillgång till de nya funktioner Excel har att erbjuda.