Metoder för att överföra data till Excel från Visual Basic
Sammanfattning
Den här artikeln beskriver flera metoder för att överföra data till Microsoft Excel från ditt Microsoft Visual Basic-program. Den här artikeln beskriver 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-arbetsbok är Automation. Automation ger dig största flexibilitet för 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 metoder för att överföra dina data:
- Överföra datacell efter 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 QueryTable i ett Excel-kalkylblad som innehåller resultatet av en fråga på 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 också 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 detta vara en bra metod för att ta bort huvuddelen av bearbetningen av data från dina klienter. Följande metoder kan användas för att överföra dina data utan Automation:
- Överföra dina data till en tabb- eller kommaavgränsad textfil som Excel senare kan parsa i celler i ett kalkylblad
- Överföra dina data till ett kalkylblad med hjälp av ADO
- Överföra data till Excel med hjälp av DDE (Dynamic Data Exchange)
Följande avsnitt innehåller mer information om var och en av dessa lösningar.
Observera När du använder Microsoft Office Excel 2007 kan du använda det nya Excel 2007-filformatet (*.xlsx) när du sparar arbetsböckerna. Det gör du genom att leta upp följande kodrad i följande kodexempel:
oBook.SaveAs "C:\Book1.xls"
Ersätt den här koden med med följande kodrad:
oBook.SaveAs "C:\Book1.xlsx"
Dessutom ingår inte Northwind-databasen i Office 2007 som standard. Du kan dock ladda ned Northwind-databasen från Microsoft Office Online.
Använda Automation för att överföra datacell efter 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 datacell efter cell kan vara en helt acceptabel metod om mängden data är liten. Du har flexibiliteten att placera data var som helst i arbetsboken och kan formatera cellerna villkorligt vid körning. Den här metoden rekommenderas dock inte om du har en stor mängd data att överföra till en Excel-arbetsbok. Varje Områdesobjekt som du hämtar vid körning resulterar i en gränssnittsbegäran så att dataöverföringen på det här sättet kan vara långsam. Dessutom har Microsoft Windows 95 och Windows 98 en 64K-begränsning för gränssnittsbegäranden. Om du når eller överskrider den här gränsen på 64 000 för gränssnittsbegäranden kan Automation-servern (Excel) sluta svara eller så kan du få fel som anger att det finns ont om minne.
En gång till är överföring av datacell per cell endast acceptabel för små mängder data. Om du behöver överföra stora datamängder till Excel bör du överväga någon av lösningarna som visas senare.
Mer exempelkod för att automatisera Excel finns i Så här automatiserar du Microsoft Excel från Visual Basic.
Använda automatisering för att överföra en matris med data till ett intervall i ett kalkylblad
En matris med data kan överföras till ett område med flera celler samtidigt:
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 dina data med hjälp av en matris i stället för cell för cell kan du uppnå en enorm prestandavinst med en stor mängd data. Överväg 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änssnittsbegäranden (en för range-objektet som range-metoden returnerar och en annan för range-objektet som metoden Resize returnerar). Å andra sidan skulle överföring av datacellen med cell kräva begäranden om 300 gränssnitt till Range-objekt. När det är möjligt kan du dra nytta av att överföra dina data i grupp och minska antalet gränssnittsbegäranden som du gör.
Använda automatisering för att överföra en ADO-postuppsättning till ett kalkylbladsintervall
Excel 2000 introducerade metoden CopyFromRecordset som gör att du kan överföra en ADO-postuppsättning (eller DAO) till ett intervall i ett kalkylblad. Följande kod illustrerar hur du kan automatisera Excel 2000, Excel 2002 eller Office Excel 2003 och överföra innehållet i tabellen Beställningar i Northwind-exempeldatabasen med hjälp av 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
Observera Om du använder Office 2007-versionen av Northwind-databasen måste du ersätta följande kodrad i kodexemplet:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Ersätt den här kodraden med följande kodrad:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 tillhandahåller också en CopyFromRecordset-metod, 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ättning till Excel med automatisering.
Använda automatisering för att skapa en QueryTable i ett kalkylblad
Ett QueryTable-objekt representerar en tabell som skapats från data som returneras från en extern datakälla. När du automatiserar Microsoft Excel kan du skapa en QueryTable genom att helt enkelt tillhandahålla en anslutningssträng till en OLEDB eller en ODBC-datakälla tillsammans med en SQL-sträng. Excel ansvarar för att generera postuppsättningen och infoga den i kalkylbladet på den plats som du anger. Att använda QueryTables ger 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 QueryTable så att den kan uppdateras vid ett senare tillfälle för att hämta en uppdaterad postuppsättning.
- När en ny QueryTable läggs till i kalkylbladet kan du ange att data som redan finns i celler i kalkylbladet ska flyttas så att de passar de 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 QueryTable i ett Excel-kalkylblad med 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
Urklipp i Windows kan också användas som en mekanism för att överföra 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 illustrerar hur Visual Basic kan använda urklippsobjektet 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 parsa i rader och kolumner
Excel kan öppna tab- eller kommaavgränsade filer och parsa data korrekt i celler. 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 lite, om ens någon, Automation. Detta kan vara en bra metod för ett klient-serverprogram eftersom textfilen kan genereras på serversidan. Du kan sedan öppna textfilen på klienten med hjälp av Automation där det är lämpligt.
Följande kod illustrerar 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 kodrad i kodexemplet:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
Ersätt den här kodraden med följande kodrad:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
Om textfilen har ett .CSV filnamnstillägg öppnar Excel filen utan att visa guiden Importera text och förutsätter automatiskt att filen är kommaavgränsad. Om filen har ett .TXT filnamnstillägg parsas filen automatiskt med tabbavgränsare.
I det föregående kodexemplet startades Excel med shell-instruktionen och namnet på filen användes som kommandoradsargument. Ingen Automation användes i föregående exempel. Om så önskas kan du dock använda en minimal mängd Automation för att öppna textfilen och spara den i Excel-arbetsboksformatet:
'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-providern kan du lägga till poster i en tabell i en befintlig Excel-arbetsbok. En "tabell" i Excel är bara ett intervall med ett definierat namn. Den första raden i intervallet 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 MyTable.
Excel 97, Excel 2000 och Excel 2003
Starta en ny arbetsbok i Excel.
Lägg till följande rubriker i cellerna A1:B1 i Blad1:
A1: FirstName B1: LastName
Formatera cell B1 som högerjusterad.
Välj A1:B1.
På Menyn Infoga väljer du Namn och sedan Definiera. Ange namnet MyTable och klicka på OK.
Spara den nya arbetsboken som C:\Book1.xls och avsluta Excel.
Om du vill lägga till poster i MyTable med hjälp av 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
Starta en ny arbetsbok i Excel 2007.
Lägg till följande rubriker i cellerna A1:B1 i Blad1:
A1: FirstName B1: LastName
Formatera cell B1 som högerjusterad.
Välj A1:B1.
Klicka på fliken Formler i menyfliksområdet och klicka sedan på Definiera namn. Skriv namnet MyTable och klicka sedan på OK.
Spara den nya arbetsboken som C:\Book1.xlsx och avsluta sedan Excel.
Om du vill lägga till poster i Tabellen MyTable 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 behålls formateringen i arbetsboken. I föregående exempel formateras nya fält som läggs till i kolumn B med högerjustering. Varje post som läggs till i en rad lånar formatet från raden ovanför den.
Observera att när en post läggs till i en cell eller celler i kalkylbladet skriver den över alla data som tidigare fanns i dessa celler. Med andra ord "pushas inte rader i kalkylbladet" ned när nya poster läggs till. Du bör ha detta i åtanke när du utformar layouten för data i kalkylbladen.
Obs!
Metoden för att uppdatera data i ett Excel-kalkylblad med hjälp av ADO eller med hjälp av 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 för programmiljö från andra Office-program, till exempel Word, Excel och Outlook.
Mer information finns i följande artiklar:
Mer information om hur du använder ADO för att komma åt en Excel-arbetsbok finns i Köra frågor mot och uppdatera Excel-data med hjälp av ADO från ASP.
Använda DDE för att överföra data till Excel
DDE är ett alternativ till Automation som ett sätt att kommunicera med Excel och överföra data. Men med tillkomsten av Automation och COM är DDE inte längre den bästa metoden för att kommunicera med andra program och bör endast 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 hjälp av DDE kan du använda metoden LinkPoke för att skicka data till ett visst cellområde, eller så använder du metoden LinkExecute för att skicka kommandon som Excel ska köra.
I följande kodexempel visas hur du upprättar en DDE-konversation med Excel så att du kan peta data i celler i ett kalkylblad och köra kommandon. Med det här exemplet kan en DDE-konversation upprättas till LinkTopic Excel|MyBook.xls måste en arbetsbok med namnet MyBook.xls redan öppnas i en instans av Excel som körs.
Obs!
När du använder Excel 2007 kan du använda det nya .xlsx filformatet för att spara arbetsböckerna. Se till att du uppdaterar filnamnet i följande kodexempel. I det här exemplet representerar Text1 en Text 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 med Excel anger du intervallet i radkolumnsnotationen (R1C1) för LinkItem. Om du petar data till flera celler kan du använda en sträng där kolumnerna avgränsas av flikar 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 Macro Language (XLM). XLM-dokumentationen ingår inte i Excel-versionerna 97 och senare.
DDE är inte en rekommenderad lösning för kommunikation med Excel. Automation ger den största flexibiliteten och ger dig mer åtkomst till de nya funktioner som Excel har att erbjuda.
Feedback
Kommer snart: Under hela 2024 kommer vi att fasa ut GitHub-problem som feedbackmekanism för innehåll och ersätta det med ett nytt feedbacksystem. Mer information finns i: https://aka.ms/ContentUserFeedback.
Skicka och visa feedback för