Jesteś obecnie w trybie offline. Czekamy na ponowne połączenie z Internetem.

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

Zakończono świadczenie pomocy technicznej dla pakietu Office 2003

Firma Microsoft zakończyła świadczenie pomocy technicznej dla pakietu Office 2003 8 kwietnia 2014. Ta zmiana wpłynęła na Twoje aktualizacje oprogramowania i opcje zabezpieczeń. Dowiedz się, co to oznacza dla Ciebie i jak zapewnić sobie kontynuację ochrony.

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:

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
Windows 98 excel automatyzacja ograniczenia vb
Właściwości

Identyfikator artykułu: 247412 — ostatni przegląd: 11/25/2003 15:23:21 — zmiana: 4.0

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

  • kbautomation kbdde kbinfo KB247412
Opinia
mTracker.init();