[SOLVED] Makro, wytnij i wklej dane do pustej komórki, wiersza

[woxalu]

Potrzebuje pomocy przy makro które wytnie i wklei dane do pustej komórki, wiersza niżej jeżeli jest juz zajęty. Dane wyglądają mniej wiecej tak:

Kod: Zaznacz cały

Birthdate:
09/23/91
Birthplace:
Los Angeles California USA
Height:
6' 1"
Weight:
180 lbs
Zip Code:
90210

Dane wejściowe są zawsze w tej samej kolumnie i komórkach G2:G11
G2 Birthdate:
G3 09/23/91
G4 Birthplace:
G5 Los Angeles California USA
G6 Height:
G7 6' 1"
G8 Weight:
G9 180 lbs
G10 Zip Code:
G11 90210

Teraz potrzebuję wyciąć G3 i wkleic do B2, G5 do C2, G7 do D2 itd... lub do wiersza niżej np: 3, 4, itd jeżeli dany wiersz jest już zajęty, a nastepnie po poprawnej operacji usunie wszytko w G1:G10
Nagrałem proste makro które działa, ale wkleja na sztywno do wpisanych komórek i chce nadpisywać dane.

Kod: Zaznacz cały

REM  *****  BASIC  *****

Sub Main

End Sub


sub Replace
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$G$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$B$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$G$5"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$C$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$G$7"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "$D$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$G$9"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args13())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "ToPoint"
args15(0).Value = "$E$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args15())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args17(0) as new com.sun.star.beans.PropertyValue
args17(0).Name = "ToPoint"
args17(0).Value = "$G$10"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args17())

rem ----------------------------------------------------------------------
dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name = "ToPoint"
args18(0).Value = "$G$11"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args18())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args20(0) as new com.sun.star.beans.PropertyValue
args20(0).Name = "ToPoint"
args20(0).Value = "$F$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args20())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args22(0) as new com.sun.star.beans.PropertyValue
args22(0).Name = "ToPoint"
args22(0).Value = "$G$1:$G$10"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args22())

rem ----------------------------------------------------------------------
dim args23(0) as new com.sun.star.beans.PropertyValue
args23(0).Name = "Flags"
args23(0).Value = "U"

dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args23())


end sub

[Jan_J]

Bardzo zgrubny szkic:

1. Ustal numer wiersza docelowego.
Jeśli masz porządek w danych, to będzie wartość o 1 większa, niż numer ostatniego wypełnionego wiersza.
Czyli numer pierwszego wiersza, w którym komórka B będzie pusta.
Jest tak dlatego, że w Basicu wiersze Calca są numerowane od 0. Kolumny też.

Jeśli nie chce ci się pisać tej części w makrze, wpisz w nieużywanej komórce fomułę =COUNTA(B:B).
Powinna dać tę samą wartość. Nazwijmy ją n.

2. Załóż pętlę, która przebiegnie komórki od G2 do G11, wstawiając odczytane wartości do kolejnych komórek wiersza n, przy okazji opróżniając komórki z G

Coś w rodzaju — piszę na żywca: z pamięci, bez sprawdzania i bez opakowania w funkcję.

Kod: Zaznacz cały

sheet = thisComponent.currentController.getActiveSheet()
target_row = n
target_column = 1
source_row = 1
source_column = 6
nfields = 10
for i = 0 to nfields-1
    v = sheet.getCellByPosition(source_column, source_row+i).getString()
    sheet.getCellByPosition(target_column+i, target_row).setString(v)
    sheet.getCellByPosition(source_column, source_row+i).setString("")
next i

Co może wymagać uwagi, to wymuszenie typu przenoszonych danych (teksty / wartości liczbowe) oraz ich formatów (daty). Metody getString i setString pobierają i wstawiają wartości tekstowe.

Niezastąpionym źródłem umiejętności programowego operowania Basicem w Calcu są materiały Andrew Pitonyaka:
AndrewMacro oraz OpenOffice Macros Explained — patrz https://pitonyak.org/book/.

[Jermor]

Aby twoje nagrane makro działało wg twego zamysłu, musi być uzupełnione o dodatkowy fragment nagrania i fragment ten musi zostać wpisany (wywołany) przed twoim zarejestrowanym kodem. Oto on:

Kod: Zaznacz cały

sub NewRow
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")


rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$B$2:$F$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"
args4(0).Value = "V"

dispatcher.executeDispatch(document, ".uno:InsertCell", "", 0, args4())

end sub

Co on robi?
On przesuwa w dół zapisany juz wiersz z danymi, w związku z tym adresy B2, C2, D2 itd są dostępne i puste.
Przy tym rozwiązaniu nowe dane sa dopisywane zawsze na początku zestawienia.

[woxalu]

1. Ustal numer wiersza docelowego.
Jeśli masz porządek w danych, to będzie wartość o 1 większa, niż numer ostatniego wypełnionego wiersza.
Czyli numer pierwszego wiersza, w którym komórka B będzie pusta.
Jest tak dlatego, że w Basicu wiersze Calca są numerowane od 0. Kolumny też.

Ostatni w tej chwili wypełniony wiersz to piąty, w pierwszym są nagłówki (Birthdate:, Birthplace:, itd), więc wolny to będzie szósty - B6

Po wpisaniu formuły "=COUNTA(B:B)" - otrzymałem wynik "Dec 31, 1899" i niewiele z tego rozumiem.

Zmieniłem nawet sposób odwoływania się do komórek na (C2, D2, E2, itd) bo gdzieś czytałem że taki zapis ma wprowzdzać dane do wiersza niżej jeśli wskazany zakres jest juz zajęty, ale nie pomogło.

Niestety nie wiem w którym miejscu twój kod mam wstawić w moim kodzie.

Dziękuję.

[woxalu]

Aby twoje nagrane makro działało wg twego zamysłu, musi być uzupełnione o dodatkowy fragment nagrania i fragment ten musi zostać wpisany (wywołany) przed twoim zarejestrowanym kodem. Oto on:

Co on robi?
On przesuwa w dół zapisany juz wiersz z danymi, w związku z tym adresy B2, C2, D2 itd są dostępne i puste.
Przy tym rozwiązaniu nowe dane sa dopisywane zawsze na początku zestawienia.

Niestety to dla mnie nie zadziała, sprawdziłem przed chwilą. Dlaczego? w kolumnie A mam nagłówek (Name:) więc nie mogą tam być wpisywane przypadkowe dane, po zmianie zakresu komórek na "$A$2:$F$2" działa, ale prawie to samo robi "wstaw wiersz".

Wolałbym żeby dane były dodawane w kolejnych pustych wierszach w dół jeśli to możliwe.

Edit: chyba że jest możliwość wypełnienia w ten sposób np: 2000 wierszy, a potem odwrócic kolejność - wiersz 2000 będzie na górze i vice versa?

Dziękuję.

[Jan_J]

Po wpisaniu formuły "=COUNTA(B:B)" - otrzymałem wynik "Dec 31, 1899" i niewiele z tego rozumiem.

Formuła jest poprawna. Wynik jest poprawny. Nieodpowiedni i mylący jest format przedstawiania zawartości komórki. Widocznie przechowywana w niej była data i tak zostało.
Aktywuj tę komórkę i wybierz
Format→Format liczb→[x] Ogólne
albo
Format→Komórki→Liczba
i okaże się, że wartość ta wynosi 2, zapewne zgodnie z prawdą.

Niestety nie wiem w którym miejscu twój kod mam wstawić w moim kodzie.

Prymitywne i uproszczone; zwłaszcza dostrojenia wymaga rozróżnienie między get/setValue a get/setString,
ale działa.
Arkusz z funkcją w załączeniu. Przy otwieraniu włącz makra

Kod: Zaznacz cały

Sub Dopisz()
    sheet = thisComponent.currentController.getActiveSheet()
    target_column = 6 ' kolumna G
    source_row = 1
    source_column = 2 ' kolumna B
    nfields = 10

    test_contents = sheet.getCellByPosition(source_column, source_row).getString() <> ""
    if test_contents then
        target_row = sheet.getCellRangeByName("E2").getValue() + 1
        for i = 0 to nfields-1
              v = sheet.getCellByPosition(source_column, source_row+i).getValue()
              sheet.getCellByPosition(target_column+i, target_row).setValue(v)
              sheet.getCellByPosition(source_column, source_row+i).setString("")
        next i
    end if

End Sub

woxalu.ods

(9.44 KiB) Pobrany 23 razy

[Jermor]

Skupiłem się na przygotowaniu makra poprzez zarejestrowanie go, mam bowiem wrażenie, że nie jesteś gotowy na pisanie w Basicu. W opisie twojego problemu w ogóle nie pojawił się problem kolumny A, i zaproponowany przeze mnie sposób niczego w tej kolumnie nie robi.
Zrozumiałem ten problem tak:
Wpisujesz jakieś dane do komórek kolumny G (od G2 do G11), a następnie uruchamiasz makro, które kopiuje zawartość komórek G3, G5, G7, G9 i G11 do pierwszego wolnego wiersza odpowiednio do komórek w kolumnach B, C, D, E i F. Pierwszy zapis znajduje się w wierszu drugim. Po tej operacji kasujesz całą zawartość obszaru w kolumnie G. Zaproponowany przeze mnie sposób robi to, tyle że kopiuje te dane zawsze do komórek B2, C2, D2, E2 i G2 po przesunięciu już zapisanych danych o jeden wiersz w dół.
Generowanie makra przez nagranie własnych działań opisałem w opracowaniu https://yestok.pl/lbo/y60.php
Aby móc zarejestrować makro spełniające twoje warunki, musisz dotrzeć do ostatniego wiersza i w nim wykonać działanie. Wymaga to jednak aby dwa pierwsze wiersze były wypełnione danym. Np. komórki B1, C1, D1, E1 i F1 zawierały spacje, czyli robiły wrażenie komórek pustych, komórki od B2 do F2 zawierały wpisy, np. Birthdate, Birthplace, Height, Weight i Zip Code. Wówczas następująca sekwencja po zarejestrowaniu wyznaczała pierwszy wolny wiersz:

• Przejdź do komórki B1 -> ustawia aktywną komórkę
• Wykonaj CTRL+strzałka w dół -> Przesuwa aktywną komórkę w dół do ostatniej komórki zajętej, po której występuje komórka pusta
• Wykonaj strzałka w dół -> przesuwa aktywną komórkę w dół do następnej komórki

W tym momencie aktywną komórką będzie pierwsza napotkana wolna komórka w tej kolumnie.
Taką czynność należy zarejestrować dla każdej z kopiowanych komórek.
Taki przykład ci załączam. Wykorzystałem w nim obszar roboczy. Jest to 5 komórek, które zawierają wymagane do przeniesienia dane. Zarejestrowane makro (trochę je ręcznie poprawiłem) kopiuje obszar roboczy, odnajduje pierwszą wolną komórkę w kolumnie B i wykonuje operację "wklej specjalnie" (tylko wartości), a następnie czyści komórki G3, G5, G7, G9, G11 pozostawiając opis tych komórek.

[woxalu]

Wykonałem

Aktywuj tę komórkę i wybierz
Format→Format liczb→[x] Ogólne
albo
Format→Komórki→Liczba
i okaże się, że wartość ta wynosi 2, zapewne zgodnie z prawdą.

i wynik to 1.

Sprawidziłem jeszcze raz formułę "=COUNTA(B:B)" w innej pustej komórce i otrzymałem "=COUNTA(B:B)" - szukałem dlaczego i znalazłem że pierwsze 100 komórek od B2 mam sformatowane jako data.

Otworzyłem twój arkusz i uruchomiłem makro, ale nic się nie dzieje, po ponownym wywołaniu makra w moim arkuszu z danymi zadziałało w ten sposób:
zostały wyczyszczone komórki C2:C5 (dalej nie było danych), a w G2:P2 pojawiły się same zera.

[woxalu]

Skupiłem się na przygotowaniu makra poprzez zarejestrowanie go, mam bowiem wrażenie, że nie jesteś gotowy na pisanie w Basicu. W opisie twojego problemu w ogóle nie pojawił się problem kolumny A, i zaproponowany przeze mnie sposób niczego w tej kolumnie nie robi.

Przepraszam, myślałem że to nie ma znaczenia.

Zrozumiałem ten problem tak:
Wpisujesz jakieś dane do komórek kolumny G (od G2 do G11), a następnie uruchamiasz makro, które kopiuje zawartość komórek G3, G5, G7, G9 i G11 do pierwszego wolnego wiersza odpowiednio do komórek w kolumnach B, C, D, E i F. Pierwszy zapis znajduje się w wierszu drugim. Po tej operacji kasujesz całą zawartość obszaru w kolumnie G. Zaproponowany przeze mnie sposób robi to, tyle że kopiuje te dane zawsze do komórek B2, C2, D2, E2 i G2 po przesunięciu już zapisanych danych o jeden wiersz w dół.

Dobrze zrozumiałeś, mój błąd.

Twoje makro w zasadzie działa tak chciałem, tylko potrzebuje jeszcze kilka informacji.
1. Jak mogę poszerzyć (przesunąć) zakres działania makra (obszar roboczy) np: jesli będę potrzebował coś dodać do G12, G13, itd.. i żeby te dane pojawiły się w pierwszym wolnym wierszu, czyli w kolumnach A, B, C, D, E i F

Jeśli zmienię

Kod: Zaznacz cały

args3(0).Name = "ToPoint"
args3(0).Value = "$B$1"

na

Kod: Zaznacz cały

args3(0).Name = "ToPoint"
args3(0).Value = "$A$1"

to dane i tak są kopiowane do 5 kolumn A:E, a nie (6) A:F - nawet jeśli rozszerzę "support area" na 6 komórek.

Czy przycisk "Insert Data" w arkuszu coś robi, czy to tylko informacja? - po kliknięciu nic się nie dzieje.

[Jermor]

Najpierw o przycisku. Kliknięcie go uruchamia makro. Dlaczego nic nie robi? Związane to jest z ustawionymi opcjami bezpieczeństwa makr. Dotyczy to makr zapisanych łącznie z plikiem. Nie wiem jaki poziom bezpieczeństwa masz ustawiony. Poczytać o tym możesz w tym opracowaniu: https://yestok.pl/ooo/y58.php. Jeśli uruchamianie makr jest zablokowane, to przycisk nie może makra uruchomić. Natomiast można je uruchomić poleceniem "Narzędzia -> Makra -> Wykonaj makro...".

Jak mogę poszerzyć (przesunąć) zakres działania makra

Przesunięcie zakresu działania wymaga dopisania nowego i zmodyfikowania już istniejącego kodu albo ponownego zarejestrowania czynności.
Cały czas trzeba mieć na uwadze, że rejestrowane makra polega na zarejestrowaniu tylko tych czynności, które wykona użytkownik.
W związku z tym należy zaprojektować właściwie sam arkusz. W twoim przypadku ważnym jest znalezienie pierwszego wolnego miejsca do przeniesienia danych. Nie można tego rejestrować kliknięciem myszki, bo makro taki fakt rejestruje jak wskazanie stałego adresu. Dlatego jednym ze sposobów jest wykorzystanie właściwości przenoszenia aktywnej komórki za pomocą klawiatury. Klawisze ze strzałkami (w lewo, w prawo, w górę, w dół) przenoszą aktywną komórkę zgodnie z wybraną strzałką o jedną pozycję. Inaczej działają, gdy podczas ich naciskania przytrzymywany jest klawisz CTRL i te cechy zostaną wykorzystane podczas rejestracji makra.

• Aktywna komórka i przynajmniej jedna następna nie jest pusta: CTRL+strzałka -> przejście do ostatniej komórki w tym ciągu.
• Aktywna komórka nie jest pusta a następna i ewentualnie dalsze są puste: CTRL+strzałka -> przejście do najbliższej niepustej albo do końca, gdy nie ma komórek niepustych.
• Aktywna komórka jest pusta a następna i ewentualnie następne nie są puste: CTRL+strzałka -> przejście do pierwszej niepustej.

To dlatego, jak napisałem, dwa pierwsze wiersze zestawienia nie mogą być puste. Można bowiem w tej sytuacji zarejestrować następującą sekwencję:

1. Przejść do komórki G3 (to można zrobić myszką, bo adres tej komórki nie będzie się zmieniał).
2. Skopiować jej zawartość CTRL+C (zawartość komórki jest w schowku).
3. Usunąć zawartość komórki BACKSPACE (wyczyszczenie komórki).
4. Przejść do komórki A1 (to także można zrobić myszką, bo ten adres nie będzie się zmieniał).
5. Wykonać CTRL+STRZAŁKA W DÓŁ (nastąpi przeniesienie aktywności do ostatniej wypełnionej komórki).
6. Wykonać STRZAŁKA W DÓŁ (w tym momencie aktywna będzie pusta komórka poniżej).
7. Wstawić zawartość schowka CTRL+V

Ta sekwencja wstawiła zawartość komórki G3. Teraz można te czynności powtórzyć w kroku pierwszym wskazując komórkę G5 a w kroku 4 komórkę B1 i analogicznie dla wszystkich dalszych.
Przedstawiony tu sposób odbiega od propozycji przedstawionej w poprzednim poście. Tutaj dane są przenoszone po kolei, jedne za drugimi.

[woxalu]

Przepraszam że tak długo, ale nie miałem czasu sie tym zająć. Rzeczywiście miałem ustawione zabezpieczenie makr na wysokie, teraz przycisk działa.

Jeśli chodzi o sam temat, to ponowne zarejestrowanie makra wg: twojej instrukcji prawdopodobnie rozwiązuje problem, muszę jeszcze kilka razy przetestować i dam znać.

Dzięki za wasze wskazówki i pomoc.