[TIP]Data ultimei actualizari

Doua add-onuri gratuite oferite de catre Microsoft ce completeaza functionalitatea Excel atat de bine incat incepend cu Excel 2013 Power Pivot vine preinstalat (doar trebuie activat) iar in Excel 2016 Power Query este inclus direct in interfata Excel (nu mai este add-on)
Închis
TudyBTH
Moderator
Moderator
Mesaje: 993
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

[TIP]Data ultimei actualizari

Mesaj de TudyBTH » Mar Iun 21, 2016 4:27 pm

Unul dintre aspectele de care trebuie tinut cont atunci cand folositi Query sau Pivot Table este ca acestea nu sunt volatile, modificarea datelor sursa nu declanseaza actualizarea rezultatelor. De aceea, este important sa aveti afisata vizibil data si ora ultimei actualizari.
Prima metoda
si cea mai simpla este includerea unei Query in fsier care sa afiseze data si ora la care a fost executata.
Pentru aceasta alegeti BlankQuery din TABul Power QueryGet External DataFrom Other Sources...
Se va deschide Query Editor unde in partea dreapta sub Query SettingsPROPERTIESName , puteti atribui un nume, de exemplu "Ultima_Actualizare". Acest nume va defini si campul de celule in care va fi scris rezultatul si va putea fi utilizat pentru adresare.
NumeQuery.PNG
Prin HomeQuery Advanced Editor
AdvancedEditor.PNG
se va deschide fereastra de editare a Query. Limbajul folosit este "Power Query Formula Language" (numit si Codul "M"). La deschidere codul ar trebui sa arate asa
CodInitial.PNG
sub expresia let, dupa "Source =" introduceti urmatorul cod

Cod: Selectaţi tot

 #table(type table[DataActualizare=datetime], {{DateTime.LocalNow()}})
si veti obtine urmatoarea secventa
CodFinal.PNG
Apasati Done iar in meniul Query Editor apasati pe Close&Load pentru a srie rezultatul intr-un sheet nou sau din submeniul butonului Close&Load alegeti Close&Load To ... si veti avea posibilitatea alegerii unui range pentru scrierea rezultatului.
Veti obtine astfel un tabel cu o sigura coloana "DataActualizare", o singura linie ce va contine data si ora la care s-a executat Query, valoare la care se poate face referire cu numele "Ultima_actualizare".

Solutia nu va functiona insa in fisierele cu foi protejate. Pentru aceasta situatie se poate folosi o

A doua Metoda
prin inserarea unui buton in foaia principala a fisierului, la actionarea caruia se executa RefreshAll printr-un macro care: inlatura protectia, actualizeza data si ora, executa RefreshAll dupa care protejeaza din nou foile care erau protejate inainte de executie.
Pentru aceasta, in foaia cu rezultate unde doriti sa aveti afisata data ultimei actualizari:
  • - asigurati-va ca nu este protejata,
    - inserati un buton din meniul DeveloperControls InsertActiveX Controls,
    - asigurati-va ca sunteti in modul Design Mode (Developer Controls Design Mode buton este activat),
    - dublu-click pe butonul creat va conduce in VisualBasicEditor ►Modulul foii in care ati creat butonul si initiaza o subrutina care se va executa la actionarea butonului'.
In interiorul acestei subrutine inserati urmatorul cod:

Cod: Selectaţi tot

    Dim i As Integer
    Dim ws() As Integer
    ReDim ws(1 To Sheets.Count)
    Application.ScreenUpdating = False
    'Memoreaza sheeturile protejate intr-un array si inlatura protectia
    For i = LBound(ws) To UBound(ws)
        If Sheets(i).ProtectContents Then
            ws(i) = 1
            Sheets(i).Unprotect
        Else
            ws(i) = 0
        End If
    Next i
    'Inscrie data si ora in titlul butonului si executa comanda RefreshAll
    Me.CommandButton1.Caption = "Ultima Actualizare la " & Now
    ThisWorkbook.RefreshAll
    'Protejeaza din nou foile marcate
    For i = LBound(ws) To UBound(ws)
        If ws(i) = 1 Then Sheets(i).Protect
    Next i
    Application.ScreenUpdating = True
Subrutina va trebui sa arate asa
CodButon.PNG
Salvati si inchideti VBE. Daca sunteti intr-un fisier .xlsx va trebui sa modificati tipul fisierului in .xlsm.
Cat timp sunteti in modul Design Mode, prin click dreapta pe buton puteti alege Format control... si Properties pentru a modifica proprietatile butonului.
Odata cu dezactivarea modului Design Mode puteti proteja din nou foaia (daca e cazul) si verifica functionarea butonului.

In atasament aveti doua fisiere care contin solutiile prezentete mai sus. Atat prezentarea cat si fisierele au fost realizate cu versiunea 2010, de aceea referirile la meniul Excel sunt valabile pentru aceasta versiune,

Un alt aspect legat de data actualizarii este posibilitatea unei actualizari programate care sa se execute automat la diferite intervale. Voi incerca sa revin cat mai curand cu o postare pe acest subiect.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

Închis

Înapoi la “Power Query & Power Pivot”