Metody przesyłania danych do programu Excel z programu Visual Basic

Podsumowanie

W tym artykule omówiono wiele metod przesyłania danych do programu Microsoft Excel z aplikacji Microsoft Visual Basic. W tym artykule przedstawiono również zalety i wady każdej metody, dzięki czemu można wybrać rozwiązanie, które działa najlepiej dla Ciebie.

Więcej informacji

Najczęściej używanym podejściem do przesyłania danych do skoroszytu programu Excel jest automatyzacja. Automatyzacja zapewnia największą elastyczność określania lokalizacji danych w skoroszycie, a także możliwość formatowania skoroszytu i tworzenia różnych ustawień w czasie wykonywania. W usłudze Automation można użyć kilku metod przesyłania danych:

• Transferowanie komórki danych według komórki
• Transferowanie danych w tablicy do zakresu komórek
• Transferowanie danych w zestawie rekordów ADO do zakresu komórek przy użyciu metody CopyFromRecordset
• Tworzenie tabeli QueryTable w arkuszu programu Excel zawierającej wynik zapytania w źródle danych ODBC lub OLEDB
• Przesyłanie danych do schowka, a następnie wklej zawartość schowka do arkusza programu Excel

Istnieją również metody, których można użyć do transferu danych do programu Excel, które niekoniecznie wymagają automatyzacji. Jeśli korzystasz z serwera aplikacji, może to być dobre podejście do zbiorczego przetwarzania danych od klientów. Do transferu danych bez usługi Automation można użyć następujących metod:

• Przesyłanie danych do pliku tekstowego rozdzielanego przecinkami lub tabulatora, który program Excel może później przeanalizować w komórkach w arkuszu
• Przesyłanie danych do arkusza przy użyciu usługi ADO
• Przesyłanie danych do programu Excel przy użyciu dynamicznego programu Data Exchange (DDE)

Poniższe sekcje zawierają więcej szczegółów dotyczących każdego z tych rozwiązań.

Uwaga W przypadku korzystania z programu Microsoft Office Excel 2007 można użyć nowego formatu pliku skoroszytu programu Excel 2007 (*.xlsx) podczas zapisywania skoroszytów. W tym celu znajdź następujący wiersz kodu w następujących przykładach kodu:

oBook.SaveAs "C:\Book1.xls"

Zastąp ten kod następującym wierszem kodu:

oBook.SaveAs "C:\Book1.xlsx"

Ponadto baza danych Northwind nie jest domyślnie uwzględniona w pakiecie Office 2007. Możesz jednak pobrać bazę danych Northwind z usługi Microsoft Office Online.

Przesyłanie komórek danych według komórki za pomocą usługi Automation

Za pomocą usługi Automation można przesyłać dane do arkusza po jednej komórce naraz:

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

Transferowanie komórek danych przez komórki może być całkowicie akceptowalnym podejściem, jeśli ilość danych jest niewielka. Możesz elastycznie umieszczać dane w dowolnym miejscu w skoroszycie i warunkowo formatować komórki w czasie wykonywania. Jednak takie podejście nie jest zalecane, jeśli masz dużą ilość danych do przesłania do skoroszytu programu Excel. Każdy obiekt zakresu, który uzyskujesz w czasie wykonywania, powoduje żądanie interfejsu, dzięki czemu przesyłanie danych w ten sposób może być powolne. Ponadto systemy Microsoft Windows 95 i Windows 98 mają ograniczenie 64K dotyczące żądań interfejsu. Jeśli osiągniesz lub przekroczysz ten limit 64 tys. żądań interfejsu, serwer usługi Automation (Excel) może przestać odpowiadać lub mogą wystąpić błędy wskazujące małą ilość pamięci.

Po raz kolejny transferowanie komórki danych przez komórkę jest dopuszczalne tylko w przypadku niewielkich ilości danych. Jeśli musisz przenieść duże zestawy danych do programu Excel, rozważ jedno z rozwiązań przedstawionych później.

Aby uzyskać więcej przykładowego kodu automatyzowania programu Excel, zobacz Jak zautomatyzować program Microsoft Excel z poziomu programu Visual Basic.

Przenoszenie tablicy danych do zakresu w arkuszu przy użyciu automatyzacji

Tablicę danych można przesyłać do zakresu wielu komórek jednocześnie:

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

Jeśli przesyłasz dane przy użyciu tablicy, a nie komórki po komórce, możesz uzyskać ogromny wzrost wydajności dzięki dużej ilości danych. Rozważmy ten wiersz z powyższego kodu, który przesyła dane do 300 komórek w arkuszu:

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

Ten wiersz reprezentuje dwa żądania interfejsu (jedno dla obiektu Zakres zwracanego przez metodę Range, a drugie dla obiektu Range zwracanego przez metodę Resize). Z drugiej strony przeniesienie komórki danych według komórki wymagałoby żądań 300 interfejsów do obiektów zakresu. Jeśli to możliwe, możesz skorzystać z przesyłania danych zbiorczo i zmniejszenia liczby żądań interfejsu.

Przenoszenie zestawu rekordów ADO do zakresu arkuszy przy użyciu automatyzacji

Program Excel 2000 wprowadził metodę CopyFromRecordset, która umożliwia transfer zestawu rekordów ADO (lub DAO) do zakresu w arkuszu. Poniższy kod ilustruje, jak można zautomatyzować program Excel 2000, Excel 2002 lub Office Excel 2003 i przenieść zawartość tabeli Orders w przykładowej bazie danych Northwind przy użyciu 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

Uwaga Jeśli używasz wersji pakietu Office 2007 bazy danych Northwind, musisz zastąpić następujący wiersz kodu w przykładzie kodu:

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

Zastąp ten wiersz kodu następującym wierszem kodu:

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

Program Excel 97 udostępnia również metodę CopyFromRecordset, ale można jej używać tylko z zestawem rekordów DAO. CopyFromRecordset z programem Excel 97 nie obsługuje ADO.

Aby uzyskać więcej informacji na temat korzystania z ADO i metody CopyFromRecordset, zobacz Jak przesyłać dane z zestawu rekordów ADO do programu Excel za pomocą automatyzacji.

Tworzenie tabeli Zapytań w arkuszu przy użyciu automatyzacji

Obiekt QueryTable reprezentuje tabelę utworzoną na podstawie danych zwróconych z zewnętrznego źródła danych. Podczas automatyzowania programu Microsoft Excel można utworzyć tabelę Zapytań, udostępniając po prostu parametry połączenia do źródła danych OLEDB lub ODBC wraz z ciągiem SQL. Program Excel ponosi odpowiedzialność za wygenerowanie zestawu rekordów i wstawienie go do arkusza w określonej lokalizacji. Korzystanie z tabeli QueryTables oferuje kilka korzyści w porównaniu z metodą CopyFromRecordset:

• Program Excel obsługuje tworzenie zestawu rekordów i jego umieszczanie w arkuszu.
• Zapytanie można zapisać przy użyciu tabeli QueryTable, aby można było je odświeżyć w późniejszym czasie w celu uzyskania zaktualizowanego zestawu rekordów.
• Po dodaniu nowej tabeli QueryTable do arkusza można określić, że dane już istniejące w komórkach arkusza zostaną przesunięte w celu uwzględnienia nowych danych (zobacz właściwość RefreshStyle, aby uzyskać szczegółowe informacje).

Poniższy kod przedstawia sposób automatyzowania programu Excel 2000, Excel 2002 lub Office Excel 2003 w celu utworzenia nowej tabeli QueryTable w arkuszu programu Excel przy użyciu danych z przykładowej bazy danych 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

Korzystanie ze schowka

Schowek systemu Windows może być również używany jako mechanizm przesyłania danych do arkusza. Aby wkleić dane do wielu komórek w arkuszu, możesz skopiować ciąg, w którym kolumny są rozdzielane znakami tabulacji, a wiersze są rozdzielane przez zwroty karetki. Poniższy kod ilustruje sposób, w jaki program Visual Basic może używać obiektu Schowka do przesyłania danych do programu 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

Tworzenie rozdzielanego pliku tekstowego, który program Excel może analizować w wierszach i kolumnach

Program Excel może otwierać pliki rozdzielane przecinkami lub tabulatorami i poprawnie analizować dane w komórkach. Możesz skorzystać z tej funkcji, gdy chcesz przenieść dużą ilość danych do arkusza, używając niewielkiej, jeśli w ogóle, automatyzacji. Może to być dobre podejście dla aplikacji klient-serwer, ponieważ plik tekstowy może być generowany po stronie serwera. Następnie możesz otworzyć plik tekstowy na kliencie przy użyciu usługi Automation, jeśli jest to odpowiednie.

Poniższy kod ilustruje sposób tworzenia pliku tekstowego rozdzielanego przecinkami na podstawie zestawu rekordów 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

Uwaga Jeśli używasz wersji pakietu Office 2007 bazy danych Northwind, musisz zastąpić następujący wiersz kodu w przykładzie kodu:

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

Zastąp ten wiersz kodu następującym wierszem kodu:

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

Jeśli plik tekstowy ma rozszerzenie .CSV, program Excel otwiera plik bez wyświetlania Kreatora importu tekstu i automatycznie zakłada, że plik jest rozdzielany przecinkami. Podobnie, jeśli plik ma rozszerzenie .TXT, program Excel automatycznie analizuje plik przy użyciu ograniczników tabulatorów.

W poprzednim przykładzie kodu program Excel został uruchomiony przy użyciu instrukcji Shell, a nazwa pliku była używana jako argument wiersza polecenia. W poprzednim przykładzie nie użyto usługi Automation. Jednak w razie potrzeby można użyć minimalnej ilości automatyzacji, aby otworzyć plik tekstowy i zapisać go w formacie skoroszytu programu Excel:

'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

Przesyłanie danych do arkusza przy użyciu usługi ADO

Za pomocą dostawcy usługi Microsoft Jet OLE DB możesz dodać rekordy do tabeli w istniejącym skoroszycie programu Excel. "Tabela" w programie Excel to tylko zakres o zdefiniowanej nazwie. Pierwszy wiersz zakresu musi zawierać nagłówki (lub nazwy pól), a wszystkie kolejne wiersze zawierają rekordy. Poniższe kroki ilustrują sposób tworzenia skoroszytu z pustą tabelą o nazwie MyTable.

Excel 97, Excel 2000 i Excel 2003

1. Uruchom nowy skoroszyt w programie Excel.

2. Dodaj następujące nagłówki do komórek A1:B1 arkusza Sheet1:

   A1: FirstName B1: LastName

3. Sformatuj komórkę B1 jako wyrównaną do prawej.

4. Wybierz pozycję A1:B1.

5. W menu Wstawianie wybierz pozycję Nazwy, a następnie wybierz pozycję Zdefiniuj. Wprowadź nazwę MyTable i kliknij przycisk OK.

6. Zapisz nowy skoroszyt jako C:\Book1.xls i zamknij program Excel.

Aby dodać rekordy do tabeli MyTable przy użyciu biblioteki ADO, możesz użyć kodu podobnego do następującego:

'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. W programie Excel 2007 uruchom nowy skoroszyt.

2. Dodaj następujące nagłówki do komórek A1:B1 arkusza Sheet1:

   A1: FirstName B1: LastName

3. Sformatuj komórkę B1 jako wyrównaną do prawej.

4. Wybierz pozycję A1:B1.

5. Na wstążce kliknij kartę Formuły , a następnie kliknij pozycję Zdefiniuj nazwę. Wpisz nazwę MyTable, a następnie kliknij przycisk OK.

6. Zapisz nowy skoroszyt jako C:\Book1.xlsx, a następnie zamknij program Excel.

Aby dodać rekordy do tabeli MyTable przy użyciu biblioteki ADO, użyj kodu podobnego do poniższego przykładu kodu.

'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

Po dodaniu rekordów do tabeli w ten sposób formatowanie w skoroszycie jest zachowywane. W poprzednim przykładzie nowe pola dodane do kolumny B są sformatowane z wyrównaniem do prawej. Każdy rekord dodany do wiersza wypożycza format z wiersza nad nim.

Należy pamiętać, że po dodaniu rekordu do komórki lub komórek w arkuszu zastąpi on wszystkie dane wcześniej w tych komórkach; Innymi słowy, wiersze w arkuszu nie są "wypychane" po dodaniu nowych rekordów. Należy o tym pamiętać podczas projektowania układu danych w arkuszach.

Aby uzyskać więcej informacji, zobacz następujący artykuł:

Nie można zmieniać, dodawać ani usuwać danych w tabelach połączonych ze skoroszytem programu Excel w programie Office Access 2003 lub w programie Access 2002

Aby uzyskać więcej informacji na temat uzyskiwania dostępu do skoroszytu programu Excel przy użyciu usługi ADO, zobacz Jak wykonywać zapytania i aktualizować dane programu Excel przy użyciu usługi ADO z poziomu programu ASP.

Przesyłanie danych do programu Excel przy użyciu środowiska DDE

DDE jest alternatywą dla usługi Automation jako sposobu komunikowania się z programem Excel i przesyłania danych; Jednak wraz z pojawieniem się usług Automation i COM, DDE nie jest już preferowaną metodą komunikacji z innymi aplikacjami i powinna być używana tylko wtedy, gdy nie ma innego rozwiązania dostępnego dla Ciebie.

Aby przesłać dane do programu Excel przy użyciu funkcji DDE, możesz użyć metody LinkPoke, aby szturchać dane do określonego zakresu komórek lub użyć metody LinkExecute do wysyłania poleceń wykonywanych przez program Excel.

Poniższy przykład kodu ilustruje sposób nawiązywania konwersacji DDE z programem Excel, aby można było szturchać dane do komórek w arkuszu i wykonywać polecenia. Korzystając z tego przykładu, aby konwersacja DDE została pomyślnie ustanowiona w programie LinkTopic Excel|MyBook.xls skoroszyt o nazwie MyBook.xls musi być już otwarty w uruchomionym wystąpieniu programu 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

W przypadku korzystania z narzędzia LinkPoke w programie Excel należy określić zakres w notacji wiersz-kolumna (R1C1) dla elementu LinkItem. Jeśli szturchasz dane do wielu komórek, możesz użyć ciągu, w którym kolumny są rozdzielane kartami, a wiersze są rozdzielane przez powroty karetki.

Jeśli używasz funkcji LinkExecute, aby poprosić program Excel o wykonanie polecenia, musisz podać programowi Excel polecenie w składni języka makr programu Excel (XLM). Dokumentacja xlm nie jest dołączona do programu Excel w wersji 97 lub nowszej.
DDE nie jest zalecanym rozwiązaniem do komunikacji z programem Excel. Automatyzacja zapewnia największą elastyczność i zapewnia większy dostęp do nowych funkcji oferowanych przez program Excel.