Kā ar VBA komandām un makro pogām uzlabot Excel spraudņa atskaišu funkcionalitāti?

[#1]

Excel spraudnis nodrošina COM objektu, ar kuru no VBA var vadīt atskaišu pārlasīšanu. Tā vietā, lai katru atskaiti pārlasītu manuāli, iespējams, izmantojot Excel Developer iespējas, izveidot makro komandas un piesaistot tās pogai, izveidot lietotājam ērti pārlasāmu atskaiti.

Priekšnosacījumi

• Excel spraudnis (Horizon Excel AddIn) ir uzstādīts un aktīvs.
• Darbgrāmata ir saglabāta kā makro iespējota darbgrāmata (.xlsm).
• Excel ir aktivizēta cilne „Developer" (Izstrādātājs). To ieslēdz sadaļā File → Options → Customize Ribbon, atzīmējot „Developer".

VBA komandu piemēri

Atskaišu pārlasīšana

Atskaišu pārlasīšanu vada ar Excel spraudņa COM objektu:

Application.COMAddIns.Item("Horizon Excel AddIn").Object

Šim objektam pieejamas šādas komandas:

RefreshAll – pārlasa visas darbgrāmatas Excel spraudņa atskaites.

Piemērs Nr.1.:

Sub Parlasit_visu()

Call Application.COMAddIns.Item("Horizon Excel AddIn").Object.RefreshAll

End Sub

RefreshList("<atskaites nosaukums>") – pārlasa vienu konkrētu atskaiti (Excel darblapu). Nosaukumu norāda kā tekstu (piemēram, "Pirmdokumenti". Piemērs Nr.2) vai atsaucoties uz lapu ar ActiveWorkbook.Sheets("PL saraksts". Piemērs Nr.3.).Name.

Piemērs Nr.2.:

Sub Parlasit_pirmdokumenti()

Call Application.COMAddIns.Item("Horizon Excel AddIn").Object.RefreshList("Pirmdokumenti")

End Sub

Piemērs Nr.3.:

Sub Parlasit_PL()

Call Application.COMAddIns.Item("Horizon Excel AddIn").Object.RefreshList(ActiveWorkbook.Sheets("PL saraksts"))

End Sub

Pivot tabulas pārlasīšana

Ja darblapa satur PivotTable, kas apkopo datus no Horizon ielasītās atskaites, pēc kopējās atskaites pārlasīšanas būtu jāveic atsevišķa PivotTable datu atjaunošana.

Lai nebūtu jāveic šāda manuāla PivotTable pārlasīšana, iespējams izveidot makro komandas tā, lai pēc visas atskaites pārlasīšanas, tiktu pārlasītas arī Pivot tabulas.

Piemērs Nr.4.:

Sub Pārlasit_ar_pivot()

    Call Application.COMAddIns.Item("Horizon Excel AddIn").Object.RefreshAll

    Do While Not Application.COMAddIns.Item("Horizon Excel AddIn").Object.IsCompleted

        DoEvents

    Loop

    Worksheets("Pivot tabula").PivotTables("PivotTable1").RefreshTable

End Sub

Piemērā darbības tiek veiktas secīgi:

1. Application.COMAddIns.Item("Horizon Excel AddIn").Object.RefreshAll - pārlasa visas darbgrāmatas Excel spraudņa atskaites;
2. Application.COMAddIns.Item("Horizon Excel AddIn").Object.IsCompleted - gaida, līdz pilnā pārlasīšana pabeigta;
3. Worksheets("Pivot tabula").PivotTables("PivotTable1").RefreshTable -pārlasa Pivot tabulu nosaukumu “PivotTable1”, kura atrodas Excel darblapā ar nosaukumu “Pivot tabula”.

Kā nomainīt atskaites parametrus vairākās atskaitēs vienlaikus

Ja Excel darbgrāmata sastāv no vairākām darblapām, kas satur datus no Horizon Publicētajām atskaitēm, tad katrā darblapā jāveic atlases parametru maiņa (piemēram: askaites datuma) un datu pārlasīšana.

Ar komandu SetParam iespējams ierakstīt atskaites parametra vērtību (piemēram, atskaites datumu) konkrētai atskaitei. Izsaucot SetParam vairākām atskaitēm ar vienu un to pašu vērtību, datumus atjaunotos visās atskaitēs vienlaikus.

SetParam("<atskaite>", "<parametra kods>", <vērtība>) – iestata parametra vērtību un atgriež, vai iestatīšana izdevās (True/False).

Svarīgi!!! Parametru kodus (piemēram, DATUMS – atskaites datums, PKNOL – noliktava) no 685.versijas iespējams apskatīt (un labot/pielāgot, pēc nepieciešamības) Horizon Publicētās atskaites konfigurācijas ievadformā “Atlases parametru” sadaļā laukā „Parametra nosaukums" vai “Lietotāju filtru” sadaļā laukā „Parametra nosaukums". Sīkāk skatīt šeit->>

Ekrānuzņēmums Nr. 1 no 6. Publicētās atskaites “Parametra nosaukums”

Piemērs Nr.5.:

Sub SetParam()

    formattedDate = Format(ActiveWorkbook.Sheets("Pogas").Range("D1").Value, "yyyy-mm-dd")

    Dim Result As Boolean

    Result = Application.COMAddIns.Item("Horizon Excel AddIn").Object.SetParam("Atlikumu atskaite", "DATUMS", formattedDate)

    Result = Application.COMAddIns.Item("Horizon Excel AddIn").Object.SetParam("Inventārs", "DATUMS", formattedDate)

End Sub

Piemērā darbības tiek veiktas secīgi:

1. formattedDate = Format(ActiveWorkbook.Sheets("Pogas").Range("D1").Value, "yyyy-mm-dd")- Datumu pirms saglabāšanas parametra vērtībā formatē kā "yyyy-mm-dd"
2. Result = Application.COMAddIns.Item("Horizon Excel AddIn").Object.SetParam("Atlikumu atskaite", "DATUMS", formattedDate) 

  Result = Application.COMAddIns.Item("Horizon Excel AddIn").Object.SetParam("Inventārs", "DATUMS", formattedDate) – D1 ailē ievadītā vērtība tiek ierakstīta  darblapās “Atlikumu atskaite” un “Inventārs” atskaites parametrā ar nosaukumu “DATUMS”, ar atbilstoši formattedDate noformētu datumu.

Rezultātā Makro nolasa datumu no D1 vērtības darblapā ar nosaukumu “Pogas”, formatē to un ar SetParam iestata to vairākām atskaitēm (“Atlikumu atskaite” un “Inventārs”).

Pēc parametru maiņas atskaites jāpārlasa (piemēram, ar „Pārlasīt visu" vai makro ar RefreshAll), lai dati atbilstu jaunajam datumam.

Ekrānuzņēmums Nr. 2 no 6. Lapa „Pogas" ar pogām parametru nolasīšanai un saglabāšanai visām atskaitēm.

*Ja nepieciešams pārliecināties par izmantoto atskaites parametra vērtību un formatējumu, iespējams izmantot komandu GetParam.

GetParam("<atskaite>", "<parametra kods>") – nolasa pašreizējo parametru vērtību (piemērā nolasa atskaites datumu un noliktavu).

Piemērs Nr.6.:

Sub GetParam()

    ActiveWorkbook.Sheets("Pogas").Range("A1").Value = Application.COMAddIns.Item("Horizon Excel AddIn").Object.GetParam("Atlikumu atskaite", "DATUMS")

    ActiveWorkbook.Sheets("Pogas").Range("B1").Value = Application.COMAddIns.Item("Horizon Excel AddIn").Object.GetParam("Atlikumu atskaite", "PKNOL")

End Sub

Piemērā darblapas “Pogas” A1 ailē tiek ierakstīta vērtība no “Atlikumu atskaite” parametra “DATUMS” un B1 ailē tiek ierakstīta vērtība no “Atlikumu atskaite” parametra “PKNOL”.

Piemērā SetParam ir piesaistīts pogai „Saglabāt visām atskaitēm jauno parametru", bet GetParam – pogai „Nolasīt datus no parametriem".

Kā pievienot pogas un piesaistīt pogām Makro darbības

1. Cilnē „Developer" spiež Insert → Button (Form Control) un uzzīmē pogu lapā. Pogai ievada saprotamu nosaukumu (piemērā – „Pārlasit visu", „Pārlasit PL sarakstu", „Pārlasit Pirmdokumenti", „Pārlasit ar Pivot").

Ekrānuzņēmums Nr. 3 no 6. Lapa „Pogas" ar četrām makro pogām.

2. Uz pogas spiež ar peles labo pogu un izvēlas „Assign Macro..." (Piesaistīt makro).

Ekrānuzņēmums Nr. 4 no 6. Pogas labā klikšķa izvēlne ar komandu „Assign Macro...".

3. Ievada Macro name un izvēlas New.

Ekrānuzņēmums Nr. 5 no 6. „Assign Macro..." logs.

4. Makro komandu izveide

Atvērtajā Module logā iespējams pievienot atbilstošās komandrindas (skat.iepriekš aprakstītos piemērus).

Ekrānuzņēmums Nr. 6 no 6. Microsoft Visual Basic for Application „Module..." logs.