Metoder for overføring av data til Excel fra Visual Basic

  • Artikkel
  • Gjelder for:
    Excel 2010, Office Excel 2007, Office Excel 2003

Sammendrag

Denne artikkelen beskriver en rekke metoder for overføring av data til Microsoft Excel fra Microsoft Visual Basic-programmet. Denne artikkelen presenterer også fordelene og ulempene for hver metode, slik at du kan velge løsningen som fungerer best for deg.

Mer informasjon

Tilnærmingen som vanligvis brukes til å overføre data til en Excel-arbeidsbok, er automatisering. Automatisering gir deg størst fleksibilitet til å angi plasseringen av dataene i arbeidsboken, i tillegg til muligheten til å formatere arbeidsboken og foreta ulike innstillinger under kjøring. Med automatisering kan du bruke flere fremgangsmåter for overføring av data:

  • Overføre datacelle etter celle
  • Overføre data i en matrise til et celleområde
  • Overføre data i et ADO-postsett til et celleområde ved hjelp av Metoden CopyFromRecordset
  • Opprette en spørringstabell i et Excel-regneark som inneholder resultatet av en spørring på en ODBC- eller OLEDB-datakilde
  • Overføre data til utklippstavlen og deretter lime inn innholdet på utklippstavlen i et Excel-regneark

Det finnes også metoder du kan bruke til å overføre data til Excel som ikke nødvendigvis krever automatisering. Hvis du kjører en serverside for et program, kan dette være en god tilnærming for å ta mesteparten av behandlingen av dataene bort fra klientene. Følgende metoder kan brukes til å overføre data uten automatisering:

  • Overføre dataene til en tabulator- eller kommadelt tekstfil som Excel senere kan analysere til celler i et regneark
  • Overføre dataene til et regneark ved hjelp av ADO
  • Overføre data til Excel ved hjelp av dynamisk datautveksling (DDE)

Avsnittene nedenfor gir mer detaljert informasjon om hver av disse løsningene.

Merk Når du bruker Microsoft Office Excel 2007, kan du bruke det nye Excel 2007-arbeidsbokformatet (*.xlsx) når du lagrer arbeidsbøkene. Hvis du vil gjøre dette, finner du følgende kodelinje i følgende kodeeksempler:

oBook.SaveAs "C:\Book1.xls"

Erstatt denne koden med følgende kodelinje:

oBook.SaveAs "C:\Book1.xlsx"

I tillegg er ikke Northwind-databasen inkludert i Office 2007 som standard. Du kan imidlertid laste ned Northwind-databasen fra Microsoft Office Online.

Bruke automatisering til å overføre dataceller etter celle

Med automatisering kan du overføre data til et regneark én celle om gangen:

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

Overføring av data celle for celle kan være en helt akseptabel tilnærming hvis datamengden er liten. Du har fleksibilitet til å plassere data hvor som helst i arbeidsboken og kan formatere cellene betinget ved kjøring. Denne fremgangsmåten anbefales imidlertid ikke hvis du har en stor mengde data som skal overføres til en Excel-arbeidsbok. Hvert områdeobjekt som du henter under kjøring, resulterer i en grensesnittforespørsel, slik at overføring av data på denne måten kan gå tregt. I tillegg har Microsoft Windows 95 og Windows 98 en 64K-begrensning på grensesnittforespørsler. Hvis du når eller overskrider denne grensen på 64 000 grensesnittforespørsler, kan automatiseringsserveren (Excel) slutte å svare, eller du kan få feil som indikerer lite minne.

Overføring av datacelle etter celle er akseptabelt bare for små mengder data. Hvis du må overføre store datasett til Excel, bør du vurdere en av løsningene som presenteres senere.

Hvis du vil ha mer eksempelkode for å automatisere Excel, kan du se Hvordan automatisere Microsoft Excel fra Visual Basic.

Bruke automatisering til å overføre en matrise med data til et område i et regneark

En matrise med data kan overføres til et område med flere celler samtidig:

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

Hvis du overfører dataene ved hjelp av en matrise i stedet for celle for celle, kan du realisere en enorm ytelsesøkning med en stor mengde data. Vurder denne linjen fra koden ovenfor som overfører data til 300 celler i regnearket:

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

Denne linjen representerer to grensesnittforespørsler (én for områdeobjektet som områdemetoden returnerer, og en annen for områdeobjektet som skaleringsmetoden returnerer). På den annen side vil overføring av datacellen etter celle kreve forespørsler om 300 grensesnitt til områdeobjekter. Når det er mulig, kan du dra nytte av å overføre dataene i bulk og redusere antall grensesnittforespørsler du gjør.

Bruke automatisering til å overføre et ADO-postsett til et regnearkområde

Excel 2000 introduserte Metoden CopyFromRecordset som lar deg overføre et ADO(eller DAO)-postsett til et område i et regneark. Følgende kode illustrerer hvordan du kan automatisere Excel 2000, Excel 2002 eller Office Excel 2003 og overføre innholdet i Ordrer-tabellen i eksempeldatabasen Gastronor ved hjelp 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

Merk Hvis du bruker Office 2007-versjonen av Northwind-databasen, må du erstatte følgende kodelinje i kodeeksemplet:

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

Erstatt denne kodelinjen med følgende kodelinje:

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

Excel 97 inneholder også en CopyFromRecordset-metode, men du kan bare bruke den med et DAO-postsett. CopyFromRecordset med Excel 97 støtter ikke ADO.

Hvis du vil ha mer informasjon om hvordan du bruker ADO og CopyFromRecordset-metoden, kan du se Slik overfører du data fra et ADO-postsett til Excel med automatisering.

Bruke automatisering til å opprette en spørringstabell i et regneark

Et QueryTable-objekt representerer en tabell bygget fra data som returneres fra en ekstern datakilde. Når du automatiserer Microsoft Excel, kan du opprette en spørringstabell ved ganske enkelt å gi en tilkoblingsstreng til en OLEDB- eller ODBC-datakilde sammen med en SQL-streng. Excel påtar seg ansvaret for å generere postsettet og sette det inn i regnearket på plasseringen du angir. Bruk av QueryTables gir flere fordeler i forhold til CopyFromRecordset-metoden:

  • Excel håndterer opprettingen av postsettet og plasseringen i regnearket.
  • Spørringen kan lagres med spørringstabellen, slik at den kan oppdateres senere for å få et oppdatert postsett.
  • Når en ny spørringstabell legges til i regnearket, kan du angi at dataene som allerede finnes i cellene i regnearket, flyttes for å få plass til de nye dataene (se RefreshStyle-egenskapen for detaljer).

Følgende kode demonstrerer hvordan du kan automatisere Excel 2000, Excel 2002 eller Office Excel 2003 for å opprette en ny spørringstabell i et Excel-regneark ved hjelp av data fra eksempeldatabasen Gastronor:

'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

Bruke utklippstavlen

Utklippstavlen i Windows kan også brukes som en mekanisme for overføring av data til et regneark. Hvis du vil lime inn data i flere celler i et regneark, kan du kopiere en streng der kolonner skilles med tabulatortegn og rader skilles med returtegn. Følgende kode illustrerer hvordan Visual Basic kan bruke utklippstavleobjektet til å overføre data til 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

Opprette en tekstfil med skilletegn som Excel kan analysere i rader og kolonner

Excel kan åpne tabulator- eller kommadelte filer og analysere dataene riktig i celler. Du kan dra nytte av denne funksjonen når du vil overføre store mengder data til et regneark mens du bruker lite, om noen, automatisering. Dette kan være en god fremgangsmåte for et klientserverprogram fordi tekstfilen kan genereres på serversiden. Deretter kan du åpne tekstfilen på klienten ved hjelp av automatisering der den passer.

Følgende kode illustrerer hvordan du kan opprette en kommadelt tekstfil fra et ADO-postsett:

'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! Hvis du bruker Office 2007-versjonen av Northwind-databasen, må du erstatte følgende kodelinje i kodeeksemplet:

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

Erstatt denne kodelinjen med følgende kodelinje:

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

Hvis tekstfilen har en .CSV filtype, åpnes filen uten å vise tekstimportveiviseren, og det antas automatisk at filen er kommadelt. Hvis filen har en .TXT filtype, analyseres filen automatisk ved hjelp av tabulatorskilletegn.

I det forrige kodeeksempelet ble Excel startet ved hjelp av Shell-setningen, og navnet på filen ble brukt som et kommandolinjeargument. Ingen automatisering ble brukt i det forrige eksemplet. Hvis du ønsker det, kan du imidlertid bruke minimalt med automatisering til å åpne tekstfilen og lagre den i Excel-arbeidsbokformatet:

'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

Overføre data til et regneark ved hjelp av ADO

Ved hjelp av Microsoft Jet OLE DB-leverandøren kan du legge til poster i en tabell i en eksisterende Excel-arbeidsbok. En «tabell» i Excel er bare et område med et definert navn. Den første raden i området må inneholde overskriftene (eller feltnavnene), og alle etterfølgende rader inneholder postene. Følgende trinn illustrerer hvordan du kan opprette en arbeidsbok med en tom tabell kalt MinTabell.

Excel 97, Excel 2000 og Excel 2003

  1. Start en ny arbeidsbok i Excel.

  2. Legg til følgende overskrifter i celle A1:B1 i Ark1:

    A1: Fornavn B1: Etternavn

  3. Formater celle B1 som høyrejustert.

  4. Velg A1:B1.

  5. Velg Navn på Sett inn-menyen, og velg deretter Definer. Skriv inn navnet MyTable, og klikk OK.

  6. Lagre den nye arbeidsboken som C:\Book1.xls, og avslutt Excel.

Hvis du vil legge til poster i MyTable ved hjelp av ADO, kan du bruke kode som ligner på følgende:

'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. Start en ny arbeidsbok i Excel 2007.

  2. Legg til følgende overskrifter i celle A1:B1 i Ark1:

    A1: Fornavn B1: Etternavn

  3. Formater celle B1 som høyrejustert.

  4. Velg A1:B1.

  5. Klikk formler-fanen på båndet, og klikk deretter Definer navn. Skriv inn navnet MyTable, og klikk deretter OK.

  6. Lagre den nye arbeidsboken som C:\Book1.xlsx, og avslutt deretter Excel.

Hvis du vil legge til poster i MyTable-tabellen ved hjelp av ADO, kan du bruke kode som ligner på følgende kodeeksempel.

'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 legger til poster i tabellen på denne måten, beholdes formateringen i arbeidsboken. I det forrige eksemplet formateres nye felt som er lagt til i kolonne B, med riktig justering. Hver post som legges til i en rad, låner formatet fra raden over den.

Du bør være oppmerksom på at når en post legges til i en celle eller celler i regnearket, overskriver den alle data som tidligere var i disse cellene. rader i regnearket blir med andre ord ikke «presset ned» når nye poster legges til. Du bør huske på dette når du utformer oppsettet med data i regnearkene.

Obs!

Metoden for å oppdatere data i et Excel-regneark ved hjelp av ADO eller ved hjelp av DAO fungerer ikke i Visual Basic for Application-miljøet i Access etter at du har installert Office 2003 Service Pack 2 (SP2) eller etter at du har installert oppdateringen for Access 2002 som er inkludert i Microsoft Knowledge Base-artikkelen 904018. Metoden fungerer bra i Visual Basic for Program-miljøet fra andre Office-programmer, for eksempel Word, Excel og Outlook.

Hvis du vil ha mer informasjon, kan du se følgende artikkel:

Du kan ikke endre, legge til eller slette data i tabeller som er koblet til en Excel-arbeidsbok i Office Access 2003 eller i Access 2002

Hvis du vil ha mer informasjon om hvordan du bruker ADO til å få tilgang til en Excel-arbeidsbok, kan du se Slik spør og oppdaterer du Excel-data ved hjelp av ADO fra ASP.

Bruke DDE til å overføre data til Excel

DDE er et alternativ til automatisering som et middel for å kommunisere med Excel og overføre data. Men med bruk av automatisering og COM er DDE ikke lenger den foretrukne metoden for å kommunisere med andre programmer, og bør bare brukes når det ikke finnes noen annen løsning som er tilgjengelig for deg.

Hvis du vil overføre data til Excel ved hjelp av DDE, kan du bruke LinkPoke-metoden til å rote data til et bestemt celleområde, eller du kan bruke LinkExecute-metoden til å sende kommandoer som Excel skal kjøre.

Følgende kodeeksempel illustrerer hvordan du oppretter en DDE-samtale med Excel, slik at du kan rote data til celler i et regneark og utføre kommandoer. Hvis du bruker dette eksemplet til en DDE-samtale som skal opprettes i LinkTopic Excel|MyBook.xls må en arbeidsbok med navnet MyBook.xls allerede være åpnet i en kjørende forekomst av Excel.

Obs!

Når du bruker Excel 2007, kan du bruke det nye .xlsx filformatet til å lagre arbeidsbøkene. Kontroller at du oppdaterer filnavnet i følgende kodeeksempel. I dette eksemplet representerer Tekst1 en tekstbokskontroll i et Visual Basic-skjema:

'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 bruker LinkPoke med Excel, angir du området i radkolonne-notasjonen (R1C1) for LinkItem. Hvis du poking data til flere celler, kan du bruke en streng der kolonnene er skilletegnet med faner og rader er skilletegnet med vognretur.

Når du bruker LinkExecute til å be Excel om å utføre en kommando, må du gi Excel kommandoen i syntaksen for Excel Macro Language (XLM). XLM-dokumentasjonen er ikke inkludert i Excel-versjon 97 og nyere.
DDE er ikke en anbefalt løsning for kommunikasjon med Excel. Automatisering gir størst fleksibilitet og gir deg mer tilgang til de nye funksjonene som Excel har å tilby.