Forum społeczności użytkowników OpenOffice i LibreOffice

: **sob paź 12, 2013 9:06 pm**

Potrzebuje waszej pomocy przy budowie makra.
Opis problemu: Mam kilkadziesiąt plików (*.xls) stworzonych w Libre Calc zawierające rożne dane. Budowa wszystkich plików jest identyczna, a mianowicie arkusz jest podzielony na cześć w której rożne osoby wypełniają własne dane (każda swój plik), natomiast w 2 części arkusza znajdują się kolumny pomocnicze które dokonują rożnych obliczeń statystycznych. Problem polega na tym, iż formuły dokonujące obliczeń (znajdujące się 2 części arkusza) ulegają z biegiem czasu rożnym korektom, aktualizacjom. Aktualizowanie poprawek w kilkudziesięciu plikach jest bardzo czasochłonne. Potrzebuje więc Makra które po każdorazowym otwarciu każdego z plików skopiuje z pliku "źródłowego" (to w nim będę dokonywał aktualizacji) tylko formuły i wstawi je w odpowiednie komórki.
Pytanie 2
Potrzebuje Makra, które skopiuje do jednego pliku ("statystycznego") arkusze w całości ze wszystkich plików w określonym folderze i nazwie je tak jak nazwa pliku źródłowego, bez konieczności otwierania plików źródłowych. Zależy mi by po każdorazowym otwarciu pliku statystycznego dokonywała się aktualizacja danych jeśli w plikach źródłowych coś się zmieniło.

Na dysku Z w osobnym folderze znajduje sie kilkadziesiąt plików *.xls i każdy z nich wypełnia inna osoba.
Każdy plik jest nazwany nazwiskiem osoby wypełniającej. Plik źródłowy jest w tym samym folderze i nosi nazwę mojego nazwiska, gdyż ja tak jak inni wypełniam swój plik.
Tylko Ja nanosze poprawki w zwoim pliku w części z forułami wiec makra powinna zczytywać aktualizację z mojego pliku (źródłowego).
Nie wiem czy to istotne ale każdy plik zawiera kilka arkuszy, a formuły liczące znajdują się od komórki AO11 do GG5000 w arkuszu 1 nazwanym "Rejestr Spraw".

: **ndz paź 13, 2013 11:00 pm**

Do podobnej sytuacji łatwo dojść w każdym projekcie, który opiera się na skoroszytach z
formułami i jednocześnie tworzy dużą liczbę podobnych skoroszytów.
Jasne, że da się podtrzymywać tę konstrukcję aktualizując formuły z szablonu za pomocą makr. Moim zdaniem jedynie na krótką metę.
Czy wystarcza Ci, że pliki przygotowane przez współpracowników zawierają komplet danych wejściowych, czy też owi współpracownicy muszą mieć natychmiast wgląd w wyniki?
Być może rozsądniej byłoby opracować zestaw makr, które -- na bazie formuł albo nawet kodu -- generowałyby plik(i) wynikowe dla dostarczonych danych. Wtedy wystarczyłby jeden egzemplarz "silnika" generującego, zaś wyniki pojawiałyby się w postaci liczb, a nie formuł.
To tylko luźne myśli, bo formuły arkusza nie są najszczęśliwszym narzędziem w przypadku wielu powtarzalnych zestawów danych, przy modyfikowanym w czasie scenariuszu obliczeń.
Inna możliwość to użycie pakietów o zacięciu statystycznym. Spośród otwartoźródłowych najmocniejszym z nich jest obecnie R.
Co do pytania 2, mam wrażenie że właściwe byłoby użycie bazy danych, zamiast mechanicznego kopiowania danych z pliku do pliku. Być może zresztą, gdyby opracowanie statystyczne było proste, dałoby się je w całości (lub prawie w całości) wykonać w środowisku bazodanowym.
Nie twierdzę, że trzeba coś takiego wdrożyć, tylko wskazuję na inne możliwości niż wprowadzenie doraźnej łaty na rozsadzający szwy projekt. Nawet w razie, gdybyś chciał zrealizować swój pierwotny zamysł, służę bardziej technicznymi wskazówkami.

: **pn paź 14, 2013 4:28 pm**

Jan_J pisze:Do podobnej sytuacji łatwo dojść w każdym projekcie, który opiera się na skoroszytach z
formułami i jednocześnie tworzy dużą liczbę podobnych skoroszytów.
Jasne, że da się podtrzymywać tę konstrukcję aktualizując formuły z szablonu za pomocą makr. Moim zdaniem jedynie na krótką metę.
Czy wystarcza Ci, że pliki przygotowane przez współpracowników zawierają komplet danych wejściowych, czy też owi współpracownicy muszą mieć natychmiast wgląd w wyniki? Być może rozsądniej byłoby opracować zestaw makr, które -- na bazie formuł albo nawet kodu -- generowałyby plik(i) wynikowe dla dostarczonych danych. Wtedy wystarczyłby jeden egzemplarz "silnika" generującego, zaś wyniki pojawiałyby się w postaci liczb, a nie formuł.
To tylko luźne myśli, bo formuły arkusza nie są najszczęśliwszym narzędziem w przypadku wielu powtarzalnych zestawów danych, przy modyfikowanym w czasie scenariuszu obliczeń.

Sytuacja wygląda następująco: Rejestr jest wypełniany przez kilkadziesiąt osób (każda swój plik) w celach statystycznych, każda osoba dokonuje również stosownych obliczeń w oparciu o wypełniane dane (statystyka miesięczna z realizacji zadań). Nie potrafię stworzyć jednego "silnika", ale obawiam się iż dostęp dla kilkudziesięciu osób do jednego "silnika" będzie pod wieloma aspektami uciążliwy.

Jan_J pisze:Co do pytania 2, mam wrażenie że właściwe byłoby użycie bazy danych, zamiast mechanicznego kopiowania danych z pliku do pliku. Być może zresztą, gdyby opracowanie statystyczne było proste, dałoby się je w całości (lub prawie w całości) wykonać w środowisku bazodanowym.
Nie twierdzę, że trzeba coś takiego wdrożyć, tylko wskazuję na inne możliwości niż wprowadzenie doraźnej łaty na rozsadzający szwy projekt. Nawet w razie, gdybyś chciał zrealizować swój pierwotny zamysł, służę bardziej technicznymi wskazówkami.

Makra 2 ma celu zbić w całość dane od wszystkich użytkowników i wygenerować analogiczna statystykę dla całej grupy. Nie posiadam wiedzy na temat środowiska bazodanowego więc próbuję z Excelem. Zaznaczę również iż wspomniana statystyka jest sporządzane w oparciu o sztywne ramy i wytyczne, które łatwiej mi transponować w Excelu.
Poniżej projekt makra 1 i 2
Z tym że:
Makra 1
Działa bardzo fajnie na Excel, ale niestety nie potrafię go uruchomić na Libre więc muszę trochę nad nim popracować.
Makra 2
Zależy mi by Makra 2 uruchamiała się tylko w momencie otwarcia pliku "Total" i kopiowała arkusz 1(Rejestr spraw) z każdego pliku w folderze i zapisywała w pliku Total pod nazwą pliku. W projekcie makra 2 uruchamia się (Excel) przy zamykaniu pliku "Użytkownik" i kopiuje do "Total". Kopiowanie w projekcie trwa długo i obawiam się że będą z tym problemy jeśli to zostanie w plikach użytkowników (co jeśli kilku użytkowników uruchomi makra 2 w tym samym czasie). Kolejna sprawa to Makra 2 w momencie pierwszego uruchomienia kopiuje arkusz rejestr do pliku "Total", ale gdy uruchamiamy makra 2 po raz kolejny to makra 2 nie aktualizuje danych tylko tworzy kolejny arkusz i dopisuje do nazwy cyfrę nr Użytkownik 1, 2, 3.

Kod: Zaznacz cały

Const Pan As String = "\Użytkownik.xls"    'plik ze wspoldzielony dostep
Const FileStat As String = "\Total.xls" 'plik ze wspoldzielony dostep("statystyczne")
Const LastR As Long = 5000              'Ostatni wiersz z formul
'''****************************************************
'''makro dla wszystkich uzytkownikow, 
'''****************************************************
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim twb As Workbook, pth$, isSht As Boolean, MyName$
Dim stat As Worksheet
ThisWorkbook.Save   'Zapis pliku uzytkownika
MyName = ThisWorkbook.Name
MyName = Left$(MyName, InStr(MyName, ".xls") - 1)   'Nazwa pliku bez rozszerzenia

pth = ThisWorkbook.Path & FileStat
Set twb = Workbooks.Open(pth)   '"statystyczne"
Application.ScreenUpdating = False
With twb
    For i% = 1 To ThisWorkbook.Sheets.Count
        If i% = 1 Then naz$ = MyName Else naz$ = MyName & CStr(i%)  'Nazwa pierwszego i kolejnych arkuszy
        isSht = False
        For Each sh In .Sheets
            If sh.Name = naz$ Then  'Ten arkusz jest juz
                isSht = True
                sh.Activate
                Exit For
            End If
        Next
        If Not isSht Then   'Jesli nie, nastepnie dodac
            Set stat = .Sheets.Add(, .Sheets(.Sheets.Count))
            stat.Name = naz$
        Else
            Set stat = sh
        End If
        'jest nieodlaczne w kopiowanie
        area = ThisWorkbook.Sheets(i%).UsedRange.Address
        stat.Cells.Clear
        ThisWorkbook.Sheets(i%).UsedRange.Copy
        stat.Range(area).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        Application.Goto [a1]
    Next
    Application.ScreenUpdating = True
    .Save
End With
twb.Close

End Sub
'''**************************************************
'''****makro tylko dla uzytkownika, 
'''**************************************************
Private Sub Workbook_Open()
Dim pth$, twb As Workbook, sh As Worksheet, psh As Worksheet
Dim rowF As Range
pth = ThisWorkbook.Path & Pan   'plik zrodlowy Pana
'tt = Timer
Set twb = Workbooks.Open(pth)
Set psh = twb.Sheets(1)
Set sh = ThisWorkbook.Sheets(1)
Set rowF = psh.Rows(11) 'pierwszy wiersz z formul
'Debug.Print Timer - tt
'tt = Timer
Application.ScreenUpdating = False

'''Formula w pierwszych 13 kolumn i pierwszych 10 wierszy NE skopiowane

'On Error Resume Next
For Each cl In rowF.Cells.SpecialCells(xlCellTypeFormulas)  'dla kazdej komorki z formula w wiersze(11)
    adr& = cl.Column    'numer kolumny
    'Skopiuj formule do ostatniego wiersza
    cl.Copy
    sh.Cells(11, adr&).Resize(LastR - 10, 1).PasteSpecial Paste:=xlFormulas 'xlAll   'xlformulas '
Next
'On Error GoTo 0
Application.CutCopyMode = False
Application.ScreenUpdating = True
'Debug.Print Timer - tt
ThisWorkbook.Save
twb.Close False

End Sub

Forum społeczności użytkowników OpenOffice i LibreOffice

makra - kopiowanie samej formuły oraz całego arkusza

makra - kopiowanie samej formuły oraz całego arkusza

Re: makra - kopiowanie samej formuły oraz całego arkusza

Re: makra - kopiowanie samej formuły oraz całego arkusza