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

[domelik21]

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".

[Jan_J]

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.

[domelik21]

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.

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