INFORMACJE: Metody transferu danych do programu Excel z programu Visual Basic

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 247412 - Zobacz jakich produktów dotyczą zawarte w tym artykule porady.
Rozwiń wszystko | Zwiń wszystko

Streszczenie

W tym artykule opisano wiele metod transferu danych do programu Microsoft Excel z aplikacji programu Microsoft Visual Basic. Przedstawiono także zalety i wady poszczególnych metod, dzięki czemu użytkownik może wybrać rozwiązanie najlepiej dostosowane do własnych potrzeb.

Więcej informacji

Do transferu danych do skoroszytu programu Excel najczęściej służy automatyzacja. Zapewnia ona największą elastyczność podczas określania lokalizacji danych w skoroszycie oraz daje możliwość formatowania skoroszytu i określania różnych ustawień w czasie wykonywania. Korzystając z automatyzacji, można zastosować kilka sposobów transferu danych:
  • Transferowanie danych komórka po komórce
  • Transferowanie danych z tablicy do zakresu komórek
  • Transferowanie danych z zestawu rekordów ADO do zakresu komórek przy użyciu metody CopyFromRecordset
  • Tworzenie w arkuszu programu Excel tabeli QueryTable zawierającej wyniki kwerendy źródła danych ODBC lub OLEDB
  • Transferowanie danych do Schowka, a następnie wklejanie jego zawartości do arkusza programu Excel
Istnieją także metody transferu danych do programu Excel, które nie wymagają użycia automatyzacji. Jeśli aplikacja jest uruchamiana po stronie serwera, może to być dobry sposób przetwarzania dużej ilości danych bez udziału klientów. Do transferu danych bez automatyzacji można użyć następujących metod:
  • Transferowanie danych do pliku tekstowego z danymi rozdzielanymi tabulatorami lub przecinkami, który może być następnie przeanalizowany przez program Excel w celu wklejenia danych do komórek arkusza
  • Transferowanie danych do arkusza przy użyciu obiektów ADO
  • Transferowanie danych do programu Excel przy użyciu mechanizmu dynamicznej wymiany danych (DDE, Dynamic Data Exchange)
W poniższych sekcjach przedstawiono bardziej szczegółowe informacje dotyczące poszczególnych rozwiązań.

Użycie automatyzacji do transferowania danych komórka po komórce

Za pomocą automatyzacji można transferować dane do arkusza po jednej komórce na raz:
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Uruchomienie nowego skoroszytu w programie Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add


   'Dodanie danych do komórek pierwszego arkusza w nowym skoroszycie
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1").Value = "Nazwisko"
   oSheet.Range("B1").Value = "Imię"
   oSheet.Range("A1:B1").Font.Bold = True
   oSheet.Range("A2").Value = "Kowalski"
   oSheet.Range("B2").Value = "Jan"

   'Zapisanie skoroszytu i zakończenie pracy programu Excel
   oBook.SaveAs "C:\Skoroszyt1.xls"
   oExcel.Quit
Transferowanie danych komórka po komórce może być bardzo dobrym rozwiązaniem w przypadku małej ilości danych. Można wtedy umieszczać dane w dowolnym miejscu w skoroszycie i warunkowo formatować komórki w czasie wykonywania. Takie podejście nie jest jednak zalecane, jeśli w skoroszycie programu Excel trzeba umieścić dużą ilość danych. W przypadku każdego obiektu typu Range utworzonego w czasie wykonywania należy wysłać żądanie do interfejsu, co sprawia, że transfer danych przeprowadzany w ten sposób jest powolny. Ponadto w systemach Microsoft Windows 95 i Windows 98 żądania wykonywane przez interfejs są ograniczone do 64 KB. W przypadku osiągnięcia lub przekroczenia limitu 64 KB dotyczącego żądań interfejsu serwer automatyzacji (w tym przypadku program Excel) może przestać odpowiadać lub otrzymać informacje o błędach wskazujących na brak pamięci. To ograniczenie występujące w systemach Windows 95 i Windows 98 zostało omówione w następującym artykule z bazy wiedzy Microsoft Knowledge Base:
216400 PRB: Cross-Process COM Automation Can Hang Client Application on Win95/98
W podsumowaniu można stwierdzić, że transferowanie danych komórka po komórce jest możliwe do zaakceptowania tylko w przypadku małych ilości danych. Jeśli istnieje konieczność przetransferowania do programu Excel dużych zestawów danych, warto rozważyć rozwiązania przestawione w dalszej części artykułu.

Aby uzyskać więcej przykładowych kodów umożliwiających automatyzację pracy z programem Excel, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base:
219151 HOWTO: Automate Microsoft Excel from Visual Basic

Użycie automatyzacji do transferowania tablicy danych do zakresu w arkuszu

Tablicę danych można za jednym razem przetransferować do zakresu wielu komórek:
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Uruchomienie nowego skoroszytu w programie Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add

   'Utworzenie tablicy składającej się z 3 kolumn i 100 wierszy
   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

   'Dodanie nagłówków do arkusza w wierszu 1
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:C1").Value = Array("ID zamówienia", "Kwota", "Podatek")

   'Transfer tablicy do arkusza, począwszy od komórki A2
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
   
   'Zapisanie skoroszytu i zakończenie pracy programu Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
Dzięki transferowaniu danych przy użyciu tablicy, zamiast komórka po komórce, można uzyskać bardzo duży wzrost wydajności w przypadku dużej ilości danych. Należy rozważyć ten wiersz powyższego kodu, który transferuje dane do 300 komórek w arkuszu:
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
Ten wiersz reprezentuje dwa żądania interfejsu (jedno w celu uzyskania obiektu Range zwracanego przez metodę Range, a drugie w celu uzyskania obiektu Range, który jest zwracany przez metodę Resize). Transferowanie danych komórka po komórce wymagałoby 300 żądań interfejsów w celu uzyskania obiektów Range. Tam, gdzie to możliwe, warto korzystać z masowego transferowania danych i zmniejszać liczbę żądań interfejsów.

Użycie automatyzacji do transferowania zestawu rekordów ADO do zakresu w arkuszu

W programie Excel 2000 została wprowadzona metoda CopyFromRecordset, która umożliwia transfer zestawu rekordów ADO (lub DAO) do zakresu w arkuszu. Poniższy kod przedstawia, w jaki sposób można zautomatyzować programy Excel 2000, Excel 2002 lub Office Excel 2003 i wykonać transfer zawartości tabeli Zamówienia z przykładowej bazy danych Northwind za pomocą metody CopyFromRecordset:
   'Utworzenie zestawu rekordów ze wszystkich rekordów tabeli Zamówienia
   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("Zamówienia", , adCmdTable)
   
   'Utworzenie nowego skoroszytu w programie 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 danych do programu Excel
   oSheet.Range("A1").CopyFromRecordset rs
   
   'Zapisanie skoroszytu i zakończenie pracy programu Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
   
   'Zamknięcie połączenia
   rs.Close
   conn.Close
Metoda CopyFromRecordset jest także dostępna w programie Excel 97, ale można jej użyć tylko w przypadku zestawu rekordów DAO. Metoda CopyFromRecordset zastosowana w programie Excel 97 nie obsługuje obiektów ADO.

Aby uzyskać więcej informacji dotyczących korzystania z obiektów ADO i metody CopyFromRecordset, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base:
246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation

Użycie automatyzacji do utworzenia tabeli QueryTable w arkuszu

Obiekt QueryTable reprezentuje tabelę skonstruowaną z danych zwróconych z zewnętrznego źródła danych. Podczas automatyzowania programu Microsoft Excel można utworzyć obiekt QueryTable, po prostu przekazując razem z ciągiem SQL ciąg połączenia ze źródłem danych OLEDB lub ODBC. Program Excel jest odpowiedzialny za wygenerowanie zestawu rekordów i wstawienie go do arkusza w określonej lokalizacji. Użycie obiektów QueryTable zapewnia kilka korzyści w porównaniu z metodą CopyFromRecordset:
  • Program Excel obsługuje tworzenie zestawu rekordów i umieszczenie go w arkuszu.
  • Kwerenda może być zapisana razem z obiektem QueryTable, dzięki czemu można ją później odświeżyć w celu uzyskania zaktualizowanego zestawu rekordów.
  • Podczas dodawania nowego obiektu QueryTable do arkusza można określić, aby dane, które już istnieją w arkuszu, zostały przesunięte w celu umieszczenia nowych danych. (Zobacz właściwość RefreshStyle, aby uzyskać szczegółowe informacje.)
Poniższy kod przedstawia, w jaki sposób można zautomatyzować program Excel 2000, Excel 2002 lub Office Excel 2003 w celu utworzenia nowego obiektu QueryTable w arkuszu programu Excel przy użyciu danych z przykładowej bazy danych Northwind:
   'Utworzenie nowego skoroszytu w programie 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)
   
   'Utworzenie obiektu 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
   
   'Zapisanie skoroszytu i zakończenie pracy programu Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Użycie Schowka

Do transferu danych do arkusza można także wykorzystać Schowek systemu Windows. Aby wkleić dane do wielu komórek w arkuszu, można skopiować ciąg, w którym kolumny są rozdzielane tabulatorami, a wiersze – znakami powrotu karetki. Poniższy kod ilustruje sposób, w jaki program Visual Basic może użyć obiektu Clipboard w celu przetransferowania danych do programu Excel:
   'Kopiowanie ciągu do Schowka
   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
   
   'Utworzenie nowego skoroszytu w programie Excel
   Dim oExcel As Object
   Dim oBook As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   

   'Wklejenie danych
   oBook.Worksheets(1).Range("A1").Select
   oBook.Worksheets(1).Paste
   
   'Zapisanie skoroszytu i zakończenie pracy programu Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Utworzenie pliku tekstowego ze znakami podziału, który program Excel może przeanalizować w celu wklejenia do wierszy i kolumn

Program Excel może otwierać pliki rozdzielane tabulatorami lub przecinkami i poprawnie wklejać dane do komórek. W razie potrzeby można wykorzystać tę funkcję do przetransferowania dużej ilości danych do arkusza, stosując automatyzację w niewielkim stopniu lub wcale. Może to być dobre rozwiązanie w przypadku aplikacji typu klient-serwer, ponieważ plik tekstowy może zostać wygenerowany po stronie serwera. Następnie można otworzyć ten plik po stornie klienta przy użyciu funkcji automatyzacji, jeśli jest to potrzebne.

Poniższy kod przedstawia sposób utworzenia pliku tekstowego rozdzielanego tabulatorami na podstawie zestawu rekordów ADO:
   'Utworzenie zestawu rekordów ze wszystkich rekordów w tabeli Zamówienia
   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("Zamówienia", , adCmdTable)
   
   'Zapisanie zestawu rekordów w postaci pliku rozdzielanego tabulatorami
   sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
   Open "C:\Test.txt" For Output As #1
   Print #1, sData
   Close #1
    
   'Zamknięcie połączenia
   rs.Close
   conn.Close
   
   'Otwarcie nowego pliku tekstowego w programie Excel
   Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
      Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Jeśli plik tekstowy ma rozszerzenie CSV, program Excel otworzy go bez wyświetlania Kreatora importu tekstu i automatycznie założy, że jest to plik rozdzielany przecinkami. Jeśli plik ma rozszerzenie TXT, program Excel automatycznie go przeanalizuje, przyjmując, że do rozdzielenia danych zostały użyte tabulatory.

W poprzednim przykładzie kodu program Excel został uruchomiony za pomocą instrukcji Shell, a nazwa pliku została użyta jako argument wiersza polecenia. W poprzednim przykładzie nie wykorzystano automatyzacji. Jeśli jednak jest to konieczne, można w niewielkim zakresie użyć automatyzacji do otwarcia pliku tekstowego i zapisania go w formacie skoroszytu programu Excel:
   'Utworzenie nowego wystąpienia programu Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
       
   'Otwarcie pliku tekstowego
   Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
   
   'Zapisanie pliku w postaci skoroszytu programu Excel i zakończenie pracy programu Excel
   oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
   oExcel.Quit
Aby uzyskać więcej informacji dotyczących wykorzystania funkcji We/Wy pliku w aplikacji programu Visual Basic, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base:
172267 RECEDIT.VBP Demonstrates File I/O in Visual Basic
W następującym artykule przedstawiono dyskusję i przykładowy kod służący do sterowania operacjami We/Wy pliku w aplikacjach programu Visual Basic:
File Access with Visual Basic for Applications

Transferowanie danych do arkusza przy użyciu obiektów ADO

Za pomocą dostawcy Microsoft Jet OLE DB można dodawać rekordy do tabeli w istniejącym skoroszycie programu Excel. „Tabela” w programie Excel to zwykły zakres o zdefiniowanej nazwie. Pierwszy wiersz zakresu musi zawierać nagłówki (lub nazwy pól), a wszystkie następne wiersze muszą zawierać rekordy. Poniższe kroki przedstawiają sposób tworzenia skoroszytu z pustą tabelą o nazwie MojaTabela:
  1. Uruchom nowy skoroszyt w programie Excel.
  2. Dodaj następujące nagłówki w komórkach A1:B1 arkusza Arkusz1:

    A1: Imię B1: Nazwisko
  3. Sformatuj komórkę B1 za pomocą opcji wyrównywania do prawej.
  4. Zaznacz komórki A1:B1.
  5. W menu Wstaw wybierz polecenie Nazwa, a następnie wybierz polecenie Definiuj. Wprowadź nazwę MojaTabela i kliknij przycisk OK.
  6. Zapisz nowy skoroszyt jako C:\Book1.xls i zakończ pracę programu Excel.
Aby dodać rekordy do tabeli MojaTabela za pomocą obiektów ADO, można użyć kodu podobnego do następującego:
   'Utworzenie nowego obiektu połączenia dla skoroszytu 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 (Imię, Nazwisko)" & _
      " values ('Bill', 'Brown')"
   conn.Execute "Insert into MyTable (Imię, Nazwisko)" & _
      " values ('Joe', 'Thomas')"
   conn.Close
Jeśli rekordy są dodawane do tabeli w ten sposób, zachowywane jest formatowanie skoroszytu. W poprzednim przykładzie nowe pola dodawane do kolumny B będą sformatowane przy użyciu opcji wyrównywania do prawej. Każdy rekord dodany do wiersza jest formatowany tak, jak wiersz nad nim.

Należy zauważyć, że rekord dodawany do komórki lub komórek w arkuszu zastępuje wszelkie dane znajdujące się wcześniej w tych komórkach. Innymi słowy, wiersze w arkuszu nie są „spychane w dół” w wyniku dodawania nowych rekordów. Warto o tym pamiętać podczas projektowania układu danych w arkuszach.

Aby uzyskać dodatkowe informacje dotyczące korzystania z obiektów ADO w celu uzyskania dostępu do skoroszytu programu Excel, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base:
195951 HOWTO: Query and Update Excel Data Using ADO From ASP

Użycie mechanizmu DDE do transferowania danych do programu Excel

Mechanizm DDE jako sposób komunikowania się z programem Excel i transferowania danych stanowi alternatywę dla automatyzacji. Wprowadzenie automatyzacji i modelu COM spowodowało jednak, że mechanizm DDE nie jest już preferowaną metodą komunikowania się z innymi aplikacjami i powinien być używany tylko wtedy, gdy nie są dostępne inne rozwiązania.

Aby przetransferować dane do programu Excel przy użyciu mechanizmu DDE, można:
  • Użyć metody LinkPoke w celu wstawienia danych do określonego zakresu komórek.

    lub
  • Użyć metody LinkExecute w celu wysłania poleceń, które wykona program Excel.
Na poniższym przykładowym kodzie pokazano, w jaki sposób można ustanowić konwersację DDE z programem Excel, aby można było wstawiać dane do komórek arkusza i wykonywać polecenia. Aby za pomocą tego przykładu pomyślnie ustanowić konwersację DDE ze skoroszytem LinkTopic Excel|MyBook.xls, skoroszyt o nazwie MyBook.xls musi być otwarty w uruchomionym wystąpieniu programu Excel.

UWAGA: W tym przykładzie ciąg Text1 oznacza formant Pole tekstowe w formularzu programu Visual Basic:
   'Zainicjowanie komunikacji DDE z programem Excel
   Text1.LinkMode = 0
   Text1.LinkTopic = "Excel|MyBook.xls"
   Text1.LinkItem = "R1C1:R2C3"
   Text1.LinkMode = 1
   
   'Wstawienie tekstu z formantu Text1 do zakresu R1C1:R2C3 w arkuszu MyBook.xls
   Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
                "four" & vbTab & "five" & vbTab & "six"
   Text1.LinkPoke
   
   'Wykonanie poleceń polegających na zaznaczeniu komórki A1 (lub R1C1) 
   'i zmianie formatu czcionki
   Text1.LinkExecute "[SELECT(""R1C1"")]"
   Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
   
   'Zakończenie komunikacji DDE
   Text1.LinkMode = 0
W przypadku użycia metody LinkPoke z programem Excel zakres należy określić za pomocą zapisu wiersz-kolumna (R1C1) w odniesieniu do obiektu LinkItem. Podczas wstawiania danych do wielu komórek można użyć ciągu, w którym kolumny są rozdzielane tabulatorami, a wiersze znakami powrotu karetki.

W przypadku użycia metody LinkExecute w celu nakazania programowi Excel wykonania polecenia należy przekazać to polecenie w składni języka makr programu Excel (XLM). Dokumentacja dotycząca języka XLM nie jest dołączana do programu Excel w wersjach 97 i nowszych. Aby uzyskać więcej informacji dotyczących sposobu uzyskania dokumentacji języka XLM, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base:
143466 XL97: Plik Macro97.exe dostępny w Usługach online
Mechanizm DDE nie jest rozwiązaniem zalecanym do użycia w celu komunikowania się z programem Excel. Automatyzacja zapewnia największą elastyczność i daje lepszy dostęp do nowych funkcji oferowanych przez program Excel.

Materiały referencyjne

Aby uzyskać dodatkowe informacje, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
306022 HOW TO: Transfer Data to an Excel Workbook by Using Visual Basic .NET

Właściwości

Numer ID artykułu: 247412 - Ostatnia weryfikacja: 25 listopada 2003 - Weryfikacja: 4.0
Informacje zawarte w tym artykule dotyczą:
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic for Applications 6.0
Słowa kluczowe: 
kbautomation kbdde kbinfo KB247412

Przekaż opinię

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com