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
Starta en ny arbetsbok i Excel.
Lägg till följande rubriker i cellerna A1:B1 i Blad1:
A1: Förnamn B1: Efternamn
Formatera cell B1 som högerjusterad.
Välj A1:B1.
Välj Namn på Infoga-menyn och välj sedan Definiera. Ange namnet Mintabell 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 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
I Excel 2007 startar du en ny arbetsbok.
Lägg till följande rubriker i cellerna A1:B1 i Blad1:
A1: Förnamn B1: Efternamn
Formatera cell B1 som högerjusterad.
Välj A1:B1.
Klicka på fliken Formler i menyfliksområdet och sedan på Definiera namn. Skriv namnet Mintabell och klicka sedan på OK.
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:
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.