Menetelmät tietojen siirtämiseksi Exceliin Visual Basicista

Yhteenveto

Tässä artikkelissa käsitellään useita tapoja tietojen siirtämiseksi Microsoft Exceliin Microsoft Visual Basic -sovelluksestasi. Tässä artikkelissa esitellään myös kunkin menetelmän edut ja haitat, jotta voit valita parhaiten toimivan ratkaisun.

Lisätietoja

Tietojen siirtäminen Excel-työkirjaan on yleensä automaatiomenetelmä. Automaation avulla voit määrittää mahdollisimman joustavasti tietojesi sijainnin työkirjassa sekä mahdollisuuden muotoilla työkirjaa ja tehdä erilaisia asetuksia suorituksen aikana. Automaation avulla voit siirtää tietoja usealla eri tavalla:

• Siirrä tietosolu solun mukaan
• Siirrä matriisin tiedot solualueeseen
• Siirrä ADO-tietuejoukon tietoja solualueeseen Käyttämällä CopyFromRecordset-menetelmää
• Sellaisen Excel-laskentataulukon kyselytaulukon luominen, joka sisältää ODBC- tai OLEDB-tietolähteeseen tehdyn kyselyn tuloksen
• Siirrä tietoja leikepöydälle ja liitä leikepöydän sisältö Excel-laskentataulukkoon

On myös olemassa menetelmiä, joilla voit siirtää exceliin tietoja, jotka eivät välttämättä edellytä automaatiota. Jos käytössäsi on sovelluspalvelin, tämä voi olla hyvä tapa ottaa suurin osa tietojen käsittelystä pois asiakkaistasi. Seuraavia menetelmiä voidaan käyttää tietojen siirtämiseen ilman automaatiota:

• Siirrä tiedot sarkaimella tai pilkuin eroteltuun tekstitiedostoon, jonka Excel voi myöhemmin jäsentää laskentataulukon soluiksi
• Tietojen siirtäminen laskentataulukkoon ADO:n avulla
• Tietojen siirtäminen Exceliin DDE:n (Dynamic Data Exchange) avulla

Seuraavissa osioissa on lisätietoja kustakin näistä ratkaisuista.

Huomautus Kun käytät Microsoft Office Excel 2007:ää, voit käyttää uutta Excel 2007 -työkirjan (*.xlsx) tiedostomuotoa, kun tallennat työkirjat. Voit tehdä tämän etsimällä seuraavan koodirivin seuraavista koodiesimerkeistä:

oBook.SaveAs "C:\Book1.xls"

Korvaa tämä koodi seuraavalla koodirivillä:

oBook.SaveAs "C:\Book1.xlsx"

Lisäksi Northwind-tietokanta ei ole oletusarvoisesti mukana Office 2007:ssä. Voit kuitenkin ladata Northwind-tietokannan Microsoft Office Onlinesta.

Automaation avulla voit siirtää tietosoluja soluittain

Automaation avulla voit siirtää tietoja laskentataulukkoon yksi solu kerrallaan:

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

Tietosolun siirtäminen solun mukaan voi olla täysin hyväksyttävä menetelmä, jos tietojen määrä on pieni. Voit sijoittaa tietoja joustavasti mihin tahansa työkirjan kohtaan ja muotoilla soluja ehdollisesti suoritushetkellä. Tätä menetelmää ei kuitenkaan suositella, jos sinulla on paljon tietoja siirrettäväksi Excel-työkirjaan. Jokainen Range-objekti, jonka hankit suorituksen aikana, johtaa liittymäpyyntöön, jotta tietojen siirtäminen tällä tavalla voi olla hidasta. Lisäksi Microsoft Windows 95:llä ja Windows 98:lla on 64K-rajoitus liittymäpyynnöissä. Jos saavutat tai ylität tämän 64k:n raja-arvon liittymäpyynnöille, automaatiopalvelin (Excel) saattaa lakata vastaamasta tai näyttöön saattaa tulla virheitä, jotka ilmaisevat muistin loppumista.

Jälleen kerran tietosolun siirtäminen solun mukaan on hyväksyttävää vain pienissä tietomäärissä. Jos haluat siirtää suuria tietojoukkoja Exceliin, harkitse jotakin myöhemmin esitetyistä ratkaisuista.

Lisätietoja Excelin automatisoinnissa käytettävästä mallikoodista on artikkelissa Microsoft Excelin automatisoiminen Visual Basicista.

Automaation avulla voit siirtää tietomatriisin laskentataulukon alueeseen

Tietomatriisi voidaan siirtää usean solun alueelle kerralla:

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

Jos siirrät tietojasi käyttämällä matriisia solun sijaan, voit ymmärtää valtavan suorituskyvyn tuoton, kun tietoja on paljon. Harkitse seuraavaa riviä yllä olevasta koodista, joka siirtää tietoja 300 soluun laskentataulukossa:

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

Tämä rivi edustaa kahta liittymäpyyntöä (toinen Range-menetelmän palauttamalle Range-objektille ja toinen Range-objektille, jonka Koon muuttaminen -menetelmä palauttaa). Toisaalta tietosolun siirtäminen solun mukaan edellyttäisi pyyntöjä 300 liittymästä Range-objekteihin. Aina kun mahdollista, voit hyötyä tietojen siirtämisestä joukkona ja liittymäpyyntöjen määrän vähentämisestä.

ADO-tietuejoukon siirtäminen laskentataulukkoalueeseen automaation avulla

Excel 2000 esitteli CopyFromRecordset-menetelmän, jonka avulla voit siirtää ADO (tai DAO) -tietuejoukon laskentataulukon alueelle. Seuraava koodi havainnollistaa, miten voit automatisoida Excel 2000:n, Excel 2002:n tai Office Excel 2003:n ja siirtää Northwind-mallitietokannan Orders-taulukon sisällön KopioiTietuejoukosta-menetelmällä.

'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

Huomautus Jos käytät Northwind-tietokannan Office 2007 -versiota, sinun on korvattava seuraava koodirivi koodiesimerkissä:

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

Korvaa tämä koodirivi seuraavalla koodirivillä:

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

Excel 97 sisältää myös CopyFromRecordset-menetelmän, mutta voit käyttää sitä vain DAO-tietuejoukon kanssa. CopyFromRecordset ja Excel 97 eivät tue ADO:a.

Lisätietoja ADO:n ja CopyFromRecordset-menetelmän käyttämisestä on artikkelissa Tietojen siirtäminen ADO-tietuejoukosta Exceliin automaation avulla.

Laskentataulukon kyselytaulukon luominen automaation avulla

QueryTable-objekti edustaa taulukkoa, joka on muodostettu ulkoisesta tietolähteestä palautetuista tiedoista. Kun automatisoit Microsoft Exceliä, voit luoda kyselytaulukon yksinkertaisesti antamalla yhteysmerkkijono OLEDB- tai ODBC-tietolähteelle YHDESSÄ SQL-merkkijonon kanssa. Excel ottaa vastuun tietuejoukon luomisesta ja lisäämisestä laskentataulukkoon määrittämässäsi sijainnissa. Kyselytaulukoiden käyttäminen tarjoaa useita etuja CopyFromRecordset-menetelmään verrattuna:

• Excel käsittelee tietuejoukon luomisen ja sen sijoittamisen laskentataulukkoon.
• Kysely voidaan tallentaa kyselytaulukon kanssa, jotta se voidaan päivittää myöhemmin päivitetyn tietuejoukon saamiseksi.
• Kun uusi kyselytaulukko lisätään laskentataulukkoon, voit määrittää, että laskentataulukon soluissa jo olevat tiedot siirretään uusien tietojen mukaan (katso lisätietoja RefreshStyle-ominaisuudesta).

Seuraava koodi näyttää, miten voit automatisoida Excel 2000:n, Excel 2002:n tai Office Excel 2003:n uuden kyselytaulukon luomiseksi Excel-laskentataulukkoon Northwind-mallitietokannan tietojen avulla:

'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

Leikepöydän käyttäminen

Windowsin Leikepöytää voidaan käyttää myös mekanismina tietojen siirtämiseen laskentataulukkoon. Jos haluat liittää tietoja useisiin laskentataulukon soluihin, voit kopioida merkkijonon, jossa sarakkeet erotetaan sarkainmerkkien mukaan ja rivit rivinvaihdoilla. Seuraava koodi havainnollistaa, miten Visual Basic voi käyttää Leikepöytä-objektiaan tietojen siirtämiseen Exceliin:

'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

Luo eroteltu tekstitiedosto, jonka Excel voi jäsentää riveiksi ja sarakkeiksi

Excel voi avata sarkaimella tai pilkuin eroteltuja tiedostoja ja jäsentää tiedot oikein soluiksi. Voit hyödyntää tätä ominaisuutta, kun haluat siirtää suuren määrän tietoja laskentataulukkoon ja käyttää vain vähän, jos lainkaan, automaatiota. Tämä voi olla hyvä tapa asiakaspalvelinsovellukselle, koska tekstitiedosto voidaan luoda palvelinpuolelle. Voit sitten avata tekstitiedoston asiakasohjelmassa käyttämällä automaatiota tarvittaessa.

Seuraava koodi havainnollistaa, miten voit luoda pilkuin erotellun tekstitiedoston ADO-tietuejoukosta:

'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

Huomautus Jos käytät Northwind-tietokannan Office 2007 -versiota, sinun on korvattava seuraava koodirivi koodiesimerkissä:

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

Korvaa tämä koodirivi seuraavalla koodirivillä:

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

Jos tekstitiedoston tunniste on .CSV, Excel avaa tiedoston näyttämättä ohjattua tekstin tuontitoimintoa ja olettaa automaattisesti, että tiedosto on pilkuilla eroteltu. Vastaavasti jos tiedostolla on .TXT tunniste, Excel jäsentää tiedoston automaattisesti sarkainerotinten avulla.

Edellisessä koodiesimerkissä Excel käynnistettiin käyttämällä Shell-lausetta, ja tiedoston nimeä käytettiin komentoriviargumenttina. Edellisessä mallissa ei käytetty automaatiota. Halutessasi voit kuitenkin käyttää minimaalista määrää automaatiota tekstitiedoston avaamiseen ja tallentamiseen Excel-työkirjamuodossa:

'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

Tietojen siirtäminen laskentataulukkoon ADO:n avulla

Käyttämällä Microsoft Jet OLE DB -palvelua voit lisätä tietueita aiemmin luodun Excel-työkirjan taulukkoon. Excelin taulukko on vain alue, jolla on määritetty nimi. Alueen ensimmäisen rivin on sisällettävä otsikot (tai kenttien nimet), ja kaikki seuraavat rivit sisältävät tietueet. Seuraavissa vaiheissa kuvataan, miten voit luoda työkirjan, jossa on tyhjä taulukko nimeltä MyTable.

Excel 97, Excel 2000 ja Excel 2003

1. Aloita uusi työkirja Excelissä.

2. Lisää seuraavat otsikot taul1 soluihin A1:B1:

   A1: Etunimi B1: Sukunimi

3. Muotoile solu B1 oikealle tasatuksi.

4. Valitse A1:B1.

5. Valitse Lisää-valikosta Nimet ja valitse sitten Määritä. Anna nimi Oma Taulukko ja valitse OK.

6. Tallenna uusi työkirja C:\Book1.xls ja sulje Excel.

Jos haluat lisätä tietueita MyTable-taulukkoon ADO:n avulla, voit käyttää seuraavanlaista koodia:

'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. Käynnistä uusi työkirja Excel 2007:ssä.

2. Lisää seuraavat otsikot taul1 soluihin A1:B1:

   A1: Etunimi B1: Sukunimi

3. Muotoile solu B1 oikealle tasatuksi.

4. Valitse A1:B1.

5. Napsauta valintanauhan Kaavat-välilehteä ja valitse sitten Määritä nimi. Kirjoita nimi MyTable ja valitse sitten OK.

6. Tallenna uusi työkirja C:\Book1.xlsx ja sulje Excel.

Jos haluat lisätä tietueita MyTable-taulukkoon ADO:n avulla, käytä koodia, joka muistuttaa seuraavaa koodiesimerkkiä.

'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

Kun lisäät tietueita taulukkoon tällä tavalla, työkirjan muotoilu säilyy. Edellisessä esimerkissä sarakkeeseen B lisätyt uudet kentät muotoillaan oikealla tasausmuodolla. Jokainen riville lisätty tietue lainaa muodon sen yläpuolella olevalta riviltä.

Huomaa, että kun tietue lisätään laskentataulukon soluun tai soluihin, se korvaa kaikki kyseisissä soluissa aiemmin olevat tiedot. toisin sanoen laskentataulukon rivejä ei "työnnetä alas", kun uusia tietueita lisätään. Pidä tämä mielessä, kun suunnittelet laskentataulukoiden tietojen asettelua.

Lisätietoja on seuraavassa artikkelissa:

Et voi muuttaa, lisätä tai poistaa tietoja taulukoissa, jotka on linkitetty Excel-työkirjaan Office Access 2003:ssa tai Access 2002:ssa

Lisätietoja Excel-työkirjan käyttämisestä ADO:n avulla on artikkelissa Excel-tietojen kyseleminen ja päivittäminen ASP:n ADO:n avulla.

Tietojen siirtäminen Exceliin DDE:n avulla

DDE on automaation vaihtoehto, joka on keino kommunikoida Excelin kanssa ja siirtää tietoja. Automaation ja COM:n tulon myötä DDE ei kuitenkaan ole enää suositeltava tapa kommunikoida muiden sovellusten kanssa, ja sitä tulee käyttää vain, kun käytettävissäsi ei ole muuta ratkaisua.

Jos haluat siirtää tietoja Exceliin DDE:n avulla, voit käyttää LinkPoke-menetelmää tietojen hakemiseen tietylle solualueelle tai linkexecute-menetelmää excelin suorittamien komentojen lähettämiseen.

Seuraava koodiesimerkki havainnollistaa, miten voit muodostaa DDE-keskustelun Excelin kanssa, jotta voit lisätä tietoja laskentataulukon soluihin ja suorittaa komentoja. Tämän mallin avulla DDE-keskustelu voidaan muodostaa Onnistuneesti LinkTopic Exceliin|MyBook.xls, MyBook.xls niminen työkirja on jo avattava käynnissä olevassa Excel-esiintymässä.

'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

Kun käytät LinkPoke-toimintoa Excelin kanssa, määrität alueen rivisarakemerkinnässä (R1C1) LinkItem-kohteelle. Jos tökit tietoja useisiin soluihin, voit käyttää merkkijonoa, jossa sarakkeet erotetaan sarkaimella ja rivit rivien erottimena rivinvaihdoilla.

Kun käytät LinkExecute-funktiota ja pyydät Exceliä suorittamaan komennon, sinun on annettava Excelille komento Excel-makrokielen (XLM) syntaksissa. XLM-dokumentaatio ei sisälly Excel-versioihin 97 tai uudempiin.
DDE ei ole suositeltu ratkaisu tiedonsiirtoon Excelin kanssa. Automaatio tarjoaa parhaan joustavuuden ja tarjoaa enemmän käyttöoikeuksia Excelin tarjoamista uusista ominaisuuksista.