Methoden voor het overbrengen van gegevens naar Excel vanuit Visual Basic
Samenvatting
In dit artikel worden tal van methoden besproken voor het overdragen van gegevens naar Microsoft Excel vanuit uw Microsoft Visual Basic-toepassing. Dit artikel bevat ook de voor- en nadelen voor elke methode, zodat u de oplossing kunt kiezen die het beste bij u past.
Meer informatie
De methode die het meest wordt gebruikt om gegevens over te dragen naar een Excel-werkmap, is Automation. Automatisering biedt u de grootste flexibiliteit voor het opgeven van de locatie van uw gegevens in de werkmap, evenals de mogelijkheid om de werkmap op te maken en verschillende instellingen te maken tijdens uitvoering. Met Automation kunt u verschillende methoden gebruiken voor het overdragen van uw gegevens:
- Gegevens cel per cel overdragen
- Gegevens in een matrix overbrengen naar een celbereik
- Gegevens in een ADO-recordset overdragen naar een celbereik met behulp van de methode CopyFromRecordset
- Een querytabel maken op een Excel-werkblad met het resultaat van een query op een ODBC- of OLEDB-gegevensbron
- Gegevens overbrengen naar het Klembord en de inhoud van het klembord in een Excel-werkblad plakken
Er zijn ook methoden die u kunt gebruiken om gegevens over te dragen naar Excel waarvoor automatisering niet noodzakelijk is vereist. Als u een toepassingsserver gebruikt, kan dit een goede aanpak zijn om het grootste deel van de verwerking van de gegevens weg te nemen van uw clients. De volgende methoden kunnen worden gebruikt om uw gegevens over te dragen zonder Automation:
- Uw gegevens overbrengen naar een tekstbestand met tabs of komma's dat later kan worden geparseerd in cellen op een werkblad
- Uw gegevens overbrengen naar een werkblad met behulp van ADO
- Gegevens overdragen naar Excel met behulp van Dynamic Data Exchange (DDE)
De volgende secties bevatten meer informatie over elk van deze oplossingen.
Opmerking Wanneer u Microsoft Office Excel 2007 gebruikt, kunt u de nieuwe bestandsindeling excel 2007-werkmap (*.xlsx) gebruiken wanneer u de werkmappen opslaat. Zoek hiervoor de volgende regel code in de volgende codevoorbeelden:
oBook.SaveAs "C:\Book1.xls"
Vervang deze code door de volgende coderegel:
oBook.SaveAs "C:\Book1.xlsx"
Bovendien is de Northwind-database niet standaard opgenomen in Office 2007. U kunt de Northwind-database echter downloaden van Microsoft Office Online.
Automation gebruiken om gegevens cel per cel over te dragen
Met Automation kunt u gegevens cel voor cel overdragen naar een werkblad:
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
Het overdragen van gegevens per cel kan een perfect acceptabele benadering zijn als de hoeveelheid gegevens klein is. U hebt de flexibiliteit om gegevens overal in de werkmap te plaatsen en kunt de cellen voorwaardelijk opmaken tijdens runtime. Deze aanpak wordt echter niet aanbevolen als u een grote hoeveelheid gegevens hebt om over te dragen naar een Excel-werkmap. Elk bereikobject dat u verkrijgt tijdens de uitvoering resulteert in een interfaceaanvraag, zodat de overdracht van gegevens op deze manier traag kan zijn. Bovendien hebben Microsoft Windows 95 en Windows 98 een beperking van 64.000 voor interfaceaanvragen. Als u deze limiet van 64.000 voor interfaceaanvragen bereikt of overschrijdt, reageert de Automation-server (Excel) mogelijk niet meer of ontvangt u mogelijk fouten die aangeven dat er weinig geheugen is.
Nogmaals, het overdragen van gegevens per cel is alleen acceptabel voor kleine hoeveelheden gegevens. Als u grote gegevenssets wilt overdragen naar Excel, moet u een van de oplossingen overwegen die later worden gepresenteerd.
Zie Microsoft Excel automatiseren vanuit Visual Basic voor meer voorbeeldcode voor Het automatiseren van Excel.
Automatisering gebruiken om een matrix met gegevens over te dragen naar een bereik op een werkblad
Een matrix met gegevens kan worden overgedragen naar een bereik van meerdere cellen tegelijk:
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
Als u uw gegevens overbrengt met behulp van een matrix in plaats van cel voor cel, kunt u een enorme prestatieverbetering realiseren met een grote hoeveelheid gegevens. Houd rekening met deze regel uit de bovenstaande code waarmee gegevens worden overgedragen naar 300 cellen in het werkblad:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Deze regel vertegenwoordigt twee interfaceaanvragen (één voor het bereikobject dat door de methode Bereik wordt geretourneerd en een andere voor het bereikobject dat door de methode Formaat wijzigen wordt geretourneerd). Aan de andere kant zijn voor het overdragen van de gegevenscel per cel aanvragen voor 300 interfaces voor Bereikobjecten vereist. Waar mogelijk kunt u profiteren van het bulksgewijs overdragen van uw gegevens en het verminderen van het aantal interfaceaanvragen dat u doet.
Automatisering gebruiken om een ADO-recordset over te dragen naar een werkbladbereik
In Excel 2000 is de methode CopyFromRecordset geïntroduceerd waarmee u een ADO- of DAO-recordset kunt overdragen naar een bereik op een werkblad. De volgende code illustreert hoe u Excel 2000, Excel 2002 of Office Excel 2003 kunt automatiseren en de inhoud van de tabel Orders in de Northwind-voorbeelddatabase kunt overdragen met behulp van de methode 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
Opmerking Als u de Office 2007-versie van de Northwind-database gebruikt, moet u de volgende regel code in het codevoorbeeld vervangen:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Vervang deze regel code door de volgende coderegel:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 biedt ook een methode CopyFromRecordset, maar u kunt deze alleen gebruiken met een DAO-recordset. CopyFromRecordset met Excel 97 biedt geen ondersteuning voor ADO.
Zie Gegevens overdragen van een ADO-recordset naar Excel met automatisering voor meer informatie over het gebruik van ADO en de methode CopyFromRecordset.
Automatisering gebruiken om een querytabel op een werkblad te maken
Een QueryTable-object vertegenwoordigt een tabel die is gebouwd op basis van gegevens die zijn geretourneerd uit een externe gegevensbron. Tijdens het automatiseren van Microsoft Excel kunt u een querytabel maken door een verbindingsreeks op te geven voor een OLEDB- of ODBC-gegevensbron, samen met een SQL-tekenreeks. Excel neemt de verantwoordelijkheid voor het genereren van de recordset en het invoegen ervan in het werkblad op de locatie die u opgeeft. Het gebruik van QueryTables biedt verschillende voordelen ten opzichte van de methode CopyFromRecordset:
- Excel zorgt voor het maken van de recordset en de plaatsing ervan in het werkblad.
- De query kan worden opgeslagen met de querytabel, zodat deze op een later tijdstip kan worden vernieuwd om een bijgewerkte recordset te verkrijgen.
- Wanneer er een nieuwe querytabel wordt toegevoegd aan uw werkblad, kunt u opgeven dat gegevens die al aanwezig zijn in cellen op het werkblad, moeten worden verplaatst naar de nieuwe gegevens (zie de eigenschap RefreshStyle voor meer informatie).
De volgende code laat zien hoe u Excel 2000, Excel 2002 of Office Excel 2003 kunt automatiseren om een nieuwe querytabel te maken in een Excel-werkblad met gegevens uit de Northwind-voorbeelddatabase:
'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
Het klembord gebruiken
Het Windows Klembord kan ook worden gebruikt als mechanisme voor het overbrengen van gegevens naar een werkblad. Als u gegevens in meerdere cellen in een werkblad wilt plakken, kunt u een tekenreeks kopiëren waarbij kolommen worden gescheiden door tabtekens en rijen worden gescheiden door rijseinden. De volgende code illustreert hoe Visual Basic het Klembord-object kan gebruiken om gegevens over te dragen naar 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
Een tekstbestand met scheidingstekens maken dat kan worden geparseerd in rijen en kolommen
Excel kan bestanden met door tabs of komma's gescheiden bestanden openen en de gegevens correct in cellen parseren. U kunt van deze functie profiteren wanneer u een grote hoeveelheid gegevens naar een werkblad wilt overbrengen terwijl u weinig of geen Automatisering gebruikt. Dit kan een goede aanpak zijn voor een client-servertoepassing, omdat het tekstbestand aan de serverzijde kan worden gegenereerd. U kunt het tekstbestand vervolgens openen op de client, waar nodig met behulp van Automation.
De volgende code illustreert hoe u een tekstbestand met komma's kunt maken op basis van een ADO-recordset:
'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
Opmerking Als u de Office 2007-versie van de Northwind-database gebruikt, moet u de volgende regel code in het codevoorbeeld vervangen:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
Vervang deze regel code door de volgende coderegel:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
Als uw tekstbestand een .CSV-extensie heeft, wordt het bestand geopend zonder de wizard Tekst importeren weer te geven en wordt er automatisch van uitgegaan dat het bestand door komma's is gescheiden. Als uw bestand een .TXT-extensie heeft, parseert Excel het bestand automatisch met behulp van tabscheidingstekens.
In het vorige codevoorbeeld is Excel gestart met behulp van de Shell-instructie en is de naam van het bestand gebruikt als een opdrachtregelargument. In het vorige voorbeeld is geen Automation gebruikt. Indien gewenst kunt u echter een minimale hoeveelheid Automation gebruiken om het tekstbestand te openen en op te slaan in de Excel-werkmapindeling:
'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
Gegevens overdragen naar een werkblad met behulp van ADO
Met de Microsoft Jet OLE DB-provider kunt u records toevoegen aan een tabel in een bestaande Excel-werkmap. Een 'tabel' in Excel is slechts een bereik met een gedefinieerde naam. De eerste rij van het bereik moet de kopteksten (of veldnamen) bevatten en alle volgende rijen bevatten de records. In de volgende stappen ziet u hoe u een werkmap kunt maken met een lege tabel met de naam MyTable.
Excel 97, Excel 2000 en Excel 2003
Een nieuwe werkmap starten in Excel.
Voeg de volgende kopteksten toe aan cellen A1:B1 van Blad1:
A1: Voornaam B1: Achternaam
Cel B1 opmaken als rechts uitgelijnd.
Selecteer A1:B1.
Kies in het menu Invoegen de optie Namen en selecteer vervolgens Definiëren. Voer de naam MyTable in en klik op OK.
Sla de nieuwe werkmap op als C:\Book1.xls en sluit Excel af.
Als u records wilt toevoegen aan MyTable met behulp van ADO, kunt u code gebruiken die vergelijkbaar is met de volgende:
'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
Start een nieuwe werkmap in Excel 2007.
Voeg de volgende kopteksten toe aan cellen A1:B1 van Blad1:
A1: Voornaam B1: Achternaam
Cel B1 opmaken als rechts uitgelijnd.
Selecteer A1:B1.
Klik op het lint op het tabblad Formules en klik vervolgens op Naam definiëren. Typ de naam MyTable en klik op OK.
Sla de nieuwe werkmap op als C:\Book1.xlsx en sluit Excel af.
Als u records wilt toevoegen aan de tabel MyTable met behulp van ADO, gebruikt u code die lijkt op het volgende codevoorbeeld.
'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
Wanneer u op deze manier records aan de tabel toevoegt, blijft de opmaak in de werkmap behouden. In het vorige voorbeeld worden nieuwe velden die zijn toegevoegd aan kolom B, opgemaakt met de juiste uitlijning. Elke record die aan een rij wordt toegevoegd, ontleent de indeling van de rij erboven.
Houd er rekening mee dat wanneer een record wordt toegevoegd aan een cel of cellen in het werkblad, alle gegevens die eerder in die cellen zijn opgenomen, worden overschreven. Met andere woorden, rijen in het werkblad worden niet 'omlaag geduwd' wanneer nieuwe records worden toegevoegd. Houd hier rekening mee bij het ontwerpen van de indeling van gegevens in uw werkbladen.
Opmerking
De methode voor het bijwerken van gegevens in een Excel-werkblad met behulp van ADO of dao werkt niet in de Visual Basic for Application-omgeving in Access nadat u Office 2003 Service Pack 2 (SP2) hebt geïnstalleerd of nadat u de update voor Access 2002 hebt geïnstalleerd die is opgenomen in het Microsoft Knowledge Base-artikel 904018. De methode werkt goed in de Visual Basic for Application-omgeving van andere Office-toepassingen, zoals Word, Excel en Outlook.
Zie het volgende artikel voor meer informatie:
Zie Excel-gegevens opvragen en bijwerken met behulp van ADO van ASP voor meer informatie over het gebruik van ADO voor toegang tot een Excel-werkmap.
DDE gebruiken om gegevens over te dragen naar Excel
DDE is een alternatief voor Automation als middel voor communicatie met Excel en het overdragen van gegevens; Met de komst van Automation en COM is DDE echter niet langer de voorkeursmethode voor communicatie met andere toepassingen en mag deze alleen worden gebruikt wanneer er geen andere oplossing voor u beschikbaar is.
Als u gegevens wilt overdragen naar Excel met behulp van DDE, kunt u de methode LinkPoke gebruiken om gegevens naar een specifiek celbereik te verplaatsen, of u gebruikt de methode LinkExecute om opdrachten te verzenden die in Excel worden uitgevoerd.
In het volgende codevoorbeeld ziet u hoe u een DDE-gesprek tot stand brengt met Excel, zodat u gegevens in cellen op een werkblad kunt plaatsen en opdrachten kunt uitvoeren. Gebruik dit voorbeeld om een DDE-gesprek tot stand te laten komen in LinkTopic Excel|MyBook.xls moet er al een werkmap met de naam MyBook.xls zijn geopend in een actief exemplaar van Excel.
Opmerking
Wanneer u Excel 2007 gebruikt, kunt u de nieuwe .xlsx-bestandsindeling gebruiken om de werkmappen op te slaan. Zorg ervoor dat u de bestandsnaam bijwerkt in het volgende codevoorbeeld. In dit voorbeeld vertegenwoordigt Text1 een besturingselement Tekstvak in een Visual Basic-formulier:
'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
Wanneer u LinkPoke met Excel gebruikt, geeft u het bereik op in rijkolomnotatie (R1C1) voor de LinkItem. Als u gegevens naar meerdere cellen prikt, kunt u een tekenreeks gebruiken waarbij de kolommen worden gescheiden door tabbladen en rijen worden gescheiden door regelterugloop.
Wanneer u LinkExecute gebruikt om Excel te vragen een opdracht uit te voeren, moet u Excel de opdracht geven in de syntaxis van excel macrotaal (XLM). De XLM-documentatie is niet opgenomen in Excel-versies 97 en hoger.
DDE is geen aanbevolen oplossing voor communicatie met Excel. Automation biedt de grootste flexibiliteit en geeft u meer toegang tot de nieuwe functies die Excel te bieden heeft.
Feedback
Binnenkort beschikbaar: In de loop van 2024 zullen we GitHub-problemen geleidelijk uitfaseren als het feedbackmechanisme voor inhoud en deze vervangen door een nieuw feedbacksysteem. Zie voor meer informatie: https://aka.ms/ContentUserFeedback.
Feedback verzenden en weergeven voor