Jak zaimportować kilka arkuszy kalkulacyjnych do bazy danych programu Access?

Skrypciarze odpowiadają na Wasze pytania

Jak zaimportować kilka arkuszy kalkulacyjnych do bazy danych programu Access?

Cześć Skrypciarze! Próbuję zaimportować plik programu Excel do bazy danych Access. Problem w tym, że nie zawiera on arkuszy. Mogę bez problemu zaimportować pierwszy arkusz, ale nie wiem, jak zaimportować pozostałe.

-- EA

Cześć, EA. Chciałbym dziś dać wyraz temu, jaki jestem szczęśliwy. Nie wiem tylko od czego zacząć, mam z tym pewne trudności, w końcu nie często zdarzają się takie momenty w moim życiu. W każdym razie nie ja sam jestem sprawcą tego, że tak się czuję, ale jest tak dzięki temu, że udało mi się obstawić wygraną meczu drużyny koszykówki Uniwersytetu Washington. I bardzo cieszę się z tego, że wygrali właśnie oni, a nie drużyna przeciwna. Wbrew temu, co mówi wiele osób (z których z kolei wiele pracuje w firmie Microsoft), sport nie jest taki ciekawy, jeżeli „każdy jest wygranym”. Sport ma sens tylko wtedy, jeżeli jest zwycięzca. A jeżeli jest zwycięzca, to musi być też przegrany. Każda akcja ma swoją reakcję. Yin i Yang. Zwycięzcy i przegrani. Psy i koty. Skrypciarze i ludzie, którzy wiedzą, co robią.

Tak czy inaczej czuję się dziś wyśmienicie i chyba najlepszym wyrazem mojego samopoczucia będzie napisanie skryptu, który może zaimportować wiele arkuszy programu Microsoft Excel do bazy danych Microsoft Access:

Const acImport = 0

Const acSpreadsheetTypeExcel9 = 8



Set objAccess = CreateObject("Access.Application")

objAccess.OpenCurrentDatabase "C:\Scripts\Personnel.mdb"



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



strFileName = "C:\Scripts\ImportData.xls"



Set objWorkbook = objExcel.Workbooks.Open(strFileName)

Set colWorksheets = objWorkbook.Worksheets



For Each objWorksheet in colWorksheets 

    Set objRange = objWorksheet.UsedRange 

    strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False) 

    objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _

        "Employees", strFileName, True, strWorksheetName

Next

Jak ten skrypt działa? Cóż, na początek definiujemy parę stałych: acImport oraz acSpreadsheetTypeExcel9:

Const acImport = 0

Const acSpreadsheetTypeExcel9 = 8

Stała acImport informuje skrypt, że chcemy zaimportować dane do bazy (w przeciwieństwie do wyeksportowania danych z bazy); a stała acSpreadsheetTypeExcel9 informuje skrypt o tym, że dane, które importujemy, pochodzą z arkusza kalkulacyjnego programu Excel 2003.

Uwaga. Ten skrypt jest przeznaczony do współpracy z programem Microsoft Office 2003; a to dlatego, że na tej wersji pakietu Office pracuje EA. Jednak nasz skrypt sprawdza się równie dobrze z pakietem Office 2007; żeby móc używać go z pakietem Office 2007, wystarczy tylko zmienić rozszerzenia plików (np. baza danych Access 2007 używa rozszerzenia pliku .accdb zamiast .mdb.) Poza tym wszystko działa bez zarzutu, nawet używanie stałej acSpreadsheetTypeExcel9.

Po zdefiniowaniu stałych wykonujemy następujące dwa wiersze kodu w celu utworzenia wystąpienia obiektu Access.Application oraz otwarcia bazy danych C:\Scripts\Personnel.mdb:

Set objAccess = CreateObject("Access.Application")

objAccess.OpenCurrentDatabase "C:\Scripts\Personnel.mdb"

Jak się okazuje, baza danych Access zawiera metodę TransferSpreadsheet, umożliwiającą importowanie danych z programu Excel. Cokolwiek by się nie działo, metoda TransferSpreadsheet może importować dane wyłącznie z jednego arkusza na raz (domyślnie jest to pierwszy arkusz programu Excel). Na szczęście możliwe jest także zaimportowanie pozostałych arkuszy pod warunkiem, że znamy ich nazwy. Jak zatem określić nazwy wszystkich arkuszy w arkuszu kalkulacyjnym programu Excel? O ile wiem, jest na to tylko jeden sposób: skrypt musi otworzyć plik arkusza kalkulacyjnego i rzucić okiem na nazwy arkuszy.

To wyjaśnia, dlaczego za pomocą następnych dwóch wierszy tworzymy wystąpienie obiektu Excel.Application i ustawiamy wartość właściwości Visible na True; dzięki temu otrzymujemy działające wystąpienie programu Excel widoczne na ekranie. Po przypisaniu nazwy pliku arkusza kalkulacyjnego (C:\Scripts\ImportData.xls) do zmiennej o nazwie strFileName wykonujemy poniższe dwa wiersze kodu w celu otwarcia pliku i pobrania kolekcji wszystkich arkuszy znajdujących się w tym pliku:

Set objWorkbook = objExcel.Workbooks.Open(strFileName)

Set colWorksheets = objWorkbook.Worksheets

Kiedy już mamy tę kolekcję, uruchamiamy pętle For Each, która przejdzie przez wszystkie arkusze w pliku ImportData.xls:

For Each objWorksheet in colWorksheets

Co się stanie po wykonaniu tej czynności? No cóż, teraz możemy zabrać się za poważną robotę. W celu zaimportowania danych z wielu arkuszy musimy zebrać razem różne wartości ciągu, które tworzą nazwy arkuszy (np. Sheet1) razem z zakresem danych, które mają zostać zaimportowane (przykładowo komórki od A1 do B50). Te wartości ciągu wyglądają mniej więcej tak:

Sheet1!A1:B50

Sheet2!A1:B34

Sheet3!A1:B87

Jak tworzymy te wartości ciągu? Cóż, na początek określamy zakres danych do zaimportowania za pomocą następującego wiersza kodu:

Set objRange = objWorksheet.UsedRange

Tworzymy teraz wystąpienie obiektu Range programu Excel oraz konfigurujemy obiekt Range tak, aby obejmował cały arkusz (lub przynajmniej tę jego część, która zawiera dane; ta część jest reprezentowana przez właściwość UsedRange.) Po skonfigurowaniu obiektu Range możemy zastosować poniższy wiersz kodu w celu utworzenia pierwszej wartości ciągu:

strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False)

strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False)

Jak widzimy, łączymy tutaj tylko trzy elementy, a następnie przypisujemy otrzymaną wartość do zmiennej o nazwie strWorksheetName. Jakie to dokładnie elementy? Właśnie te:

• objWorksheet.Name. Jest to po prostu nazwa arkusza (np. Sheet1).
• "!" Wykrzyknik. Nawiasem mówiąc, pewnie się zastanawiacie, po co nam wykrzyknik; ten skrypt nie jest aż tak ekscytujący. Chociaż po bliższym przyjrzeniu się… Jak się jednak okazuje, ten wykrzyknik nie oznacza ekscytacji; jest natomiast używany w celu oddzielenia danych arkusza od zakresu danych.
• Address(False, False). Właściwość Address przekazuje nam punkty początkowe i końcowe dla naszego zakresu (np. A1, B50). Ustawiając parametry RowAbsolute oraz ColumnAbsolute na False (stąd się wzięła cześć (False, False)), otrzymujemy punkty początkowe i końcowe w formacie A1:B50. Jest to dokładnie taki format, jakiego potrzebujemy, żeby zaimportować dane z arkusza kalkulacyjnego.

Otrzymamy dzięki temu wartość ciągu wyglądającą mniej więcej tak: Sheet1!A1:B50. Przekazując tę wartość do metody TransferSpreadsheet informujemy TransferSpreadsheet, że musi spojrzeć na arkusz Sheet1 i zaimportować wartości znajdujące się w komórkach od A1 do B50.

A skoro już o tym mowa, czas wywołać metodę TransferSpreadsheet:

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _

    "Employees", strFileName, True, strWorksheetName

Jeśli przyjrzymy się odniesieniu do obiektu (objAccess), zobaczymy, że przełączyliśmy się z powrotem do bazy danych Access. Jest tak dlatego, że metoda TransferSpreadsheet należy do obiektu DoCmd bazy danych Access, który umożliwia wykonywanie poleceń bazy danych Access za pomocą skryptu. Jak widzimy, przekazujemy metodzie TransferSpreadsheet sześć parametrów:

• acImport. Ta stała informuje skrypt, że chcemy importować dane, a nie eksportować je.

• acSpreadsheetTypeExcel9. Stała informująca skrypt o tym, jakiego typu dane importujemy.

• “Employees”. Nazwa tabeli bazy danych do której chcemy zaimportować dane.

• strFileName. Ścieżka pliku arkusza kalkulacyjnego (C:\Scripts\ImportData.xls).

• True. Wartość logiczna informująca skrypt, że pierwszy wiersz w arkuszu zawiera nazwy pól. Co się dzieje, jeżeli pierwszy wiersz w arkuszu nie zawiera nazw pól; czyli jeżeli w pierwszym wierszu znajdują się dane? Ustawiamy tę wartość na False.

  Oczywiście z powyższych względów zalecamy, aby w pierwszym wierszu każdego arkusza kalkulacyjnego umieścić nazwy pól. To pomaga w śledzeniu danych, a także gwarantuje, że dane zostaną poprawnie zaimportowane. Jeżeli używamy nazw pól (np.: Nazwa, Stopień, Numer seryjny) w pierwszym wierszu, kolumny w arkuszu nie muszą znajdować się w tej samej kolejności co pola w bazie danych (Nazwa, Stopień, Numer seryjny). Jeżeli nie używamy nazw pól w pierwszym wierszu, kolumny arkusza kalkulacyjnego i pola bazy danych muszą mieć dokładnie tę samą kolejność.

• strWorksheetName. To jest wartość ciągu, którą właśnie utworzyliśmy. (Sheet1!A1:B50.) Ona z kolei informuje dokładnie skrypt o tym, które dane, z którego arkusza powinny zostać zaimportowane.

I to wszystko. Po zaimportowaniu pierwszego arkusza wracamy na początek pętli i powtarzamy cały proces dla następnego arkusza z kolekcji. Po zakończeniu dane z każdego jednego arkusza w pliku ImportData.xls zostaną dodane do tabeli Employees w bazie danych.

I to załatwia całą sprawę, EA. A tak w ogóle to jak Wasze przygotowania do zimowej Olimpiady Skrypciarskiej 2008? 2008 Winter Scripting Games

Do początku strony