Metody přenosu dat z jazyka Visual Basic do excelu

Souhrn

Tento článek popisuje různé metody přenosu dat z aplikace Microsoft Visual Basic do aplikace Microsoft Excel. Tento článek také představuje výhody a nevýhody jednotlivých metod, abyste si mohli vybrat řešení, které vám nejlépe vyhovuje.

Další informace

K přenosu dat do excelového sešitu se nejčastěji používá automatizace. Automatizace poskytuje největší flexibilitu pro určení umístění dat v sešitu a také možnost formátování sešitu a provádění různých nastavení za běhu. Se službou Automation můžete k přenosu dat použít několik přístupů:

• Přenos datových buněk po buňce
• Přenos dat v matici do oblasti buněk
• Přenos dat v sadě záznamů ADO do oblasti buněk pomocí metody CopyFromRecordset
• Vytvoření tabulky dotazů na excelovém listu, který obsahuje výsledek dotazu na zdroj dat ODBC nebo OLEDB
• Přenos dat do schránky a následné vložení obsahu schránky do excelového listu

Existují také metody, které můžete použít k přenosu dat do Excelu, které nemusí nutně vyžadovat automatizaci. Pokud používáte aplikační server na straně serveru, může to být dobrý přístup k tomu, aby se velká část zpracování dat od klientů odpojila. K přenosu dat bez služby Automation je možné použít následující metody:

• Přenos dat do textového souboru odděleného tabulátory nebo čárkami, který Excel může později analyzovat do buněk na listu
• Přenos dat do listu pomocí ADO
• Přenos dat do Excelu pomocí dynamické výměny dat (DDE)

Další podrobnosti o každém z těchto řešení najdete v následujících částech.

Poznámka Při použití aplikace Microsoft Office Excel 2007 můžete při ukládání sešitů použít nový formát souboru sešitu aplikace Excel 2007 (*.xlsx). Chcete-li to provést, vyhledejte následující řádek kódu v následujících příkladech kódu:

oBook.SaveAs "C:\Book1.xls"

Tento kód nahraďte následujícím řádkem kódu:

oBook.SaveAs "C:\Book1.xlsx"

Databáze Northwind navíc není ve výchozím nastavení součástí Office 2007. Databázi Northwind si ale můžete stáhnout z webu Microsoft Office Online.

Přenos datových buněk po buňce pomocí služby Automation

Se službou Automation můžete přenášet data do listu po jedné buňce:

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

Přenos datových buněk po buňce může být naprosto přijatelný přístup, pokud je množství dat malé. Data můžete umístit kamkoli do sešitu a buňky můžete podmíněně formátovat za běhu. Tento přístup se ale nedoporučuje, pokud máte velké množství dat k přenosu do excelového sešitu. Každý objekt Range, který získáte za běhu, má za následek požadavek rozhraní, aby přenos dat tímto způsobem mohl být pomalý. Kromě toho mají systémy Microsoft Windows 95 a Windows 98 omezení 64K požadavků rozhraní. Pokud dosáhnete nebo překročíte tento limit 64 000 u požadavků rozhraní, může automatizační server (Excel) přestat reagovat nebo se můžou zobrazit chyby značící nedostatek paměti.

Přenos datových buněk po buňce je opět přijatelný pouze pro malé objemy dat. Pokud potřebujete přenést velké datové sady do Excelu, měli byste zvážit některé z řešení uvedených později.

Další ukázkový kód pro automatizaci Excelu najdete v tématu Jak automatizovat Microsoft Excel z jazyka Visual Basic.

Použití automatizace k přenosu pole dat do oblasti na listu

Pole dat lze přenést do oblasti více buněk najednou:

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

Pokud data přenášíte pomocí pole místo buňky po buňce, můžete dosáhnout obrovského zvýšení výkonu s velkým množstvím dat. Představte si tento řádek z výše uvedeného kódu, který přenáší data do 300 buněk v listu:

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

Tento řádek představuje dva požadavky rozhraní (jeden pro range objekt, který vrátí metoda Range, a druhý pro range objekt, který vrátí metoda Resize). Na druhou stranu přenos datové buňky po buňce by vyžadoval požadavky na 300 rozhraní na objekty Range. Kdykoli je to možné, můžete těžit z hromadného přenosu dat a snížení počtu požadavků rozhraní, které provádíte.

Použití automatizace k přenosu sady záznamů ADO do oblasti listu

Aplikace Excel 2000 zavedla metodu CopyFromRecordset, která umožňuje přenést sadu záznamů ADO (nebo DAO) do oblasti na listu. Následující kód ukazuje, jak můžete automatizovat Excel 2000, Excel 2002 nebo Office Excel 2003 a přenést obsah tabulky Objednávky v ukázkové databázi Northwind pomocí metody 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

Poznámka Pokud používáte verzi databáze Northwind pro Office 2007, musíte v příkladu kódu nahradit následující řádek kódu:

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

Tento řádek kódu nahraďte následujícím řádkem kódu:

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

Excel 97 také poskytuje metodu CopyFromRecordset, ale můžete ji použít pouze se sadou záznamů DAO. CopyFromRecordset s Excelem 97 nepodporuje ADO.

Další informace o používání ADO a metody CopyFromRecordset najdete v tématu Přenos dat ze sady záznamů ADO do Excelu pomocí automatizace.

Použití automatizace k vytvoření tabulky dotazů na listu

Objekt QueryTable představuje tabulku vytvořenou z dat vrácených z externího zdroje dat. Při automatizaci Microsoft Excelu můžete vytvořit tabulku dotazů jednoduchým poskytnutím připojovací řetězec k OLEDB nebo zdroji dat ODBC společně s řetězcem SQL. Aplikace Excel přebírá odpovědnost za vygenerování sady záznamů a jejím vložení do listu v zadaném umístění. Použití QueryTables nabízí několik výhod oproti metodě CopyFromRecordset:

• Excel zpracovává vytvoření sady záznamů a její umístění do listu.
• Dotaz je možné uložit s tabulkou querytable, aby bylo možné ho později aktualizovat a získat aktualizovanou sadu záznamů.
• Když do listu přidáte novou tabulku dotazů, můžete určit, aby se data, která už v buňkách na listu existují, přesunula tak, aby se nová data vešla (podrobnosti najdete ve vlastnosti RefreshStyle).

Následující kód ukazuje, jak můžete automatizovat Excel 2000, Excel 2002 nebo Office Excel 2003 a vytvořit novou tabulku dotazů v excelovém listu pomocí dat z ukázkové databáze Northwind:

'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

Použití schránky

Schránka systému Windows se dá použít také jako mechanismus pro přenos dat do listu. Pokud chcete vložit data do více buněk na listu, můžete zkopírovat řetězec, ve kterém jsou sloupce oddělené znaky tabulátoru a řádky jsou oddělené znaky na řádku. Následující kód ukazuje, jak visual basic může pomocí objektu Clipboard přenést data do Excelu:

'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

Vytvoření textového souboru s oddělovači, který Excel dokáže analyzovat na řádky a sloupce

Excel může otevírat soubory oddělené tabulátory nebo čárkami a správně analyzovat data do buněk. Tuto funkci můžete využít v případě, že chcete přenést velké množství dat do listu a současně používat jen malou automatizaci(pokud vůbec). To může být vhodný přístup pro aplikaci klient-server, protože textový soubor lze vygenerovat na straně serveru. Textový soubor pak můžete otevřít v klientovi pomocí služby Automation tam, kde je to vhodné.

Následující kód ukazuje, jak můžete vytvořit textový soubor oddělený čárkami ze sady záznamů ADO:

'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

Poznámka: Pokud používáte verzi Office 2007 databáze Northwind, musíte nahradit následující řádek kódu v příkladu kódu:

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

Tento řádek kódu nahraďte následujícím řádkem kódu:

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

Pokud má textový soubor příponu .CSV, aplikace Excel soubor otevře bez zobrazení Průvodce importem textu a automaticky předpokládá, že je soubor oddělený čárkami. Podobně platí, že pokud má soubor příponu .TXT, Excel ho automaticky parsuje pomocí oddělovačů tabulátoru.

V předchozí ukázce kódu se Excel spustil pomocí příkazu Shell a název souboru se použil jako argument příkazového řádku. V předchozí ukázce nebyla použita žádná automatizace. Pokud to ale chcete, můžete k otevření textového souboru a jeho uložení ve formátu excelového sešitu použít minimální množství automatizace:

'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

Přenos dat do listu pomocí ADO

Pomocí zprostředkovatele Microsoft Jet OLE DB můžete přidat záznamy do tabulky v existujícím excelovém sešitu. "Tabulka" v Excelu je pouze oblast s definovaným názvem. První řádek oblasti musí obsahovat záhlaví (nebo názvy polí) a všechny následující řádky obsahují záznamy. Následující kroky ukazují, jak můžete vytvořit sešit s prázdnou tabulkou s názvem MyTable.

Excel 97, Excel 2000 a Excel 2003

1. Vytvořte nový sešit v Excelu.

2. Do buněk A1:B1 listu List1 přidejte následující záhlaví:

   A1: Jméno B1: Příjmení

3. Naformátujte buňku B1 jako zarovnanou doprava.

4. Vyberte A1:B1.

5. V nabídce Insert (Vložit) zvolte Names (Názvy) a pak vyberte Define (Definovat). Zadejte název MyTable a klikněte na OK.

6. Uložte nový sešit jako C:\Book1.xls a ukončete Excel.

Pokud chcete přidat záznamy do tabulky MyTable pomocí ADO, můžete použít kód podobný tomuto:

'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. V aplikaci Excel 2007 spusťte nový sešit.

2. Do buněk A1:B1 listu List1 přidejte následující záhlaví:

   A1: Jméno B1: Příjmení

3. Naformátujte buňku B1 jako zarovnanou doprava.

4. Vyberte A1:B1.

5. Na pásu karet klikněte na kartu Vzorce a potom klikněte na Definovat název. Zadejte název MyTable a klikněte na OK.

6. Uložte nový sešit jako C:\Book1.xlsx a ukončete Excel.

Pokud chcete přidat záznamy do tabulky MyTable pomocí ADO, použijte kód podobný následujícímu příkladu kódu.

'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

Když do tabulky přidáte záznamy tímto způsobem, formátování sešitu se zachová. V předchozím příkladu jsou nová pole přidaná do sloupce B naformátovaná se správným zarovnáním. Každý záznam přidaný do řádku si přepůjčuje formát z řádku nad ním.

Všimněte si, že když je záznam přidán do buňky nebo buněk v listu, přepíše všechna data v těchto buňkách dříve; jinými slovy, řádky v listu nejsou při přidání nových záznamů "posunuty dolů". To byste měli mít na paměti při návrhu rozložení dat na listech.

Další informace najdete v následujícím článku:

Nelze změnit, přidat nebo odstranit data v tabulkách, které jsou propojeny s sešitem aplikace Excel v aplikaci Office Access 2003 nebo Access 2002

Další informace o použití ADO pro přístup k excelovým sešitům najdete v tématu Dotazování a aktualizace excelových dat pomocí ADO z ASP.

Přenos dat do Excelu pomocí DDE

DDE je alternativou ke službě Automation jako prostředek pro komunikaci s Excelem a přenos dat. S nástupem automatizace a modelu COM však DDE již není upřednostňovanou metodou pro komunikaci s jinými aplikacemi a mělo by se používat pouze v případě, že pro vás není k dispozici žádné jiné řešení.

Chcete-li přenést data do aplikace Excel pomocí DDE, můžete použít LinkPoke metodu pro zobrazení dat do určité oblasti buněk nebo pomocí linkexecute metody odeslat příkazy, které aplikace Excel provede.

Následující příklad kódu ukazuje, jak vytvořit konverzaci DDE s Excelem, abyste mohli do buněk na listu vložit data a spouštět příkazy. Pokud chcete úspěšně navázat konverzaci DDE do Aplikace LinkTopic Excel, použijte tuto ukázku|MyBook.xls musí být sešit s názvem MyBook.xls již otevřen ve spuštěné instanci aplikace Excel.

'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

Pokud používáte LinkPoke s Excelem, zadáte oblast v zápisu row-column (R1C1) pro LinkItem. Pokud šťoucháte data do více buněk, můžete použít řetězec, ve kterém jsou sloupce oddělené tabulátory a řádky jsou odděleny vrácené znaky řádku.

Když použijete LinkExecute a požádáte Excel o provedení příkazu, musíte excelu dát příkaz v syntaxi excelového jazyka maker (XLM). Dokumentace k xlm není součástí Excelu verze 97 a novějších.
DDE není doporučené řešení pro komunikaci s Excelem. Automatizace poskytuje největší flexibilitu a poskytuje větší přístup k novým funkcím, které Excel nabízí.