Wyodrębnianie danych z zamkniętego pliku w innym skoroszycie jest częstym żądaniem większości użytkowników programu Excel. Chcieliby pobrać lub skonsolidować dane z zamkniętych plików; jednak nie jest to możliwe. Istnieje obejście, które pomaga rozwiązać ten problem. Rozwiązaniem jest użycie kodu Macro.
Pytanie: Na co dzień spędzam czas na kopiowaniu danych z jednego pliku do drugiego. Istnieją dwa pliki „Open.xls” i „Closed.xls” i chcę skopiować dane z „Closed.xls” do „Open.xls” za pomocą kodu VBA.
Najważniejsze rzeczy, które należy wiedzieć:
- Po pierwsze, powinniśmy znać „Usedrange” zamkniętego skoroszytu, tj. „Closed.xls” w ramach otwartego skoroszytu, np. „Otwórz.xls”
- Możemy użyć funkcji JEŻELI w Usedrange skoroszytu „Closed.xls” w skoroszycie „Open.xls” i wyodrębni ona dane ze skoroszytu „Closed.xls”
- Jeśli komórka odniesienia jest pusta, wstawiany jest #N/A. Korzystając z metody specialcells, możesz usunąć wszystkie błędy #N/A i zmienić formułę na wartości
Na początek zapiszemy dwa pliki Excela 1) Open.xls 2) Closed.xls w ścieżce „D:\Test Folder”
Poniżej znajduje się migawka skoroszytu „Closed.xls”:
Aby automatycznie zapisać dane z pliku „Closed.xls” do pliku „Open.xls”, należy wykonać poniższe kroki, aby uruchomić edytor VB
- Kliknij kartę Deweloper
- Z grupy Kod wybierz Visual Basic
- Skopiuj poniższy kod w ThisWorkbook (Closed.xls)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) 'Wstaw adres UsedRange arkusza Sheet1 Closed.xls (ten skoroszyt)' --- Komentarz Sheet2.Cells(1, 1) = Sheet1.UsedRange.Address End Sub
- Aby pobrać dane z „Open.xls”, skopiuj następujący kod do modułu standardowego
Sub Importdata() Dim AreaAddress As String Sheet1.UsedRange.Clear Sheet1.Cells(1, 1) = "= 'D:\Test Folder\" & "[Closed.xls]Sheet2'!RC" AreaAddress = Sheet1.Cells( 1, 1) With Sheet1.Range(AreaAddress) .FormulaR1C1 = "=IF('D:\Test Folder\" & "[Closed.xls]Sheet1'!RC="""",NA(),'D: \Test Folder\" & _ "[Closed.xls]Sheet1'!RC)" w przypadku błędu Wznów dalej .SpecialCells(xlCellTypeFormulas, xlErrors).Wyczyść w przypadku błędu GoTo 0 .Value = .Value Koniec z końcem
- Skopiuj następujący kod w ThisWorkbook (Open.xls)
Private Sub Workbook_Open() Uruchom "Importdata" End Sub
Teraz wszystkie kody VBA są ustawione; wystarczy otworzyć plik o nazwie „Open.xls”. Poniżej znajduje się migawka pliku „Open.xls”:
Kod nie ogranicza się do kopii komórki z A1.usedrange; kod wybierze początkowy zakres i będzie działał idealnie. Pliki są dostępne do pobrania i zalecamy, aby spróbować.
Wniosek: Dzięki kodowi VBA możemy zautomatyzować zadanie ekstrakcji danych bez otwierania skoroszytu docelowego i oszczędzamy się przed ręcznym kopiowaniem i wklejaniem.
Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku.
Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail