Příspěvky uživatele

Všimnul jsem si dvou problémů.

V kódu dotazu má být:
html = Text.Replace(html_maska, "<<Datum>>", Date.ToText(datum, "d.M.yyyy") ),

Tj. ve formátu má být "M" a ne "m". Jinak stránka vrací (zřejmě) poslední data.

Dále je vhodné zakázat aktualizaci dotazu na pozadí:
Pravý klik na na dotaz "Table 0", vlastnosti, zrušit zaškrtnutí u "povolit aktualizaci na pozadí".

Adresa v dotazu je normální text. V PQ se to opraví snadno:

let
datum = #date(2022,2,9),
html_maska = "https://www.kurzy.cz/~nr/kurzy-men/nejlepsi-kurzy/EUR-euro/D-<<Datum>>-Ordb--Ord--Z-3-s-/",
html = Text.Replace(html_maska, "<<Datum>>", Date.ToText(datum, "d.m.yyyy") ),
Zdroj = Web.Page(Web.Contents(html)),
...

Datum je tu natvrdo, nenapsal jsi, kde se má brát. Asi bych ho četl z listu.

ve vba je to podobné:

Dim wb As Workbook
Dim dotaz As String

Set wb = ThisWorkbook

dotaz = wb.Queries("Table 0").Formula

v proměnné dotaz je text dotazu. Musíš jen najít datum, nahradit ho a vrátit opravený dotaz zpět.

Za mne je řešení přes PQ snažší.

Dotazuješ se na jméno souboru v jednom dotazu a soubor čteš v jiném. To vadí ochraně osobních dat. V prostředí PQ jde o hlášení Formula.Firewall.

Když to provedeš v jednom dotazu, tak to projde.

// Zdroj
let
Soubor = Excel.CurrentWorkbook(){[Name="FPath"]}[Content][Column1]{0},
Zdroj = Table.FromColumns({Lines.FromBinary(File.Contents(Soubor), null, null, 1250)})
in
Zdroj

Ochrana se zablokuje v nastavení. Tam se dostaneš z excelu:
Data/rozbalit "Načíst data"/"Možnosti dotazu"

Nebo z prostředí PQ:

Soubor/Možnosti a nastavení/Možnosti dotazu.

V dialogu : Ochrana osobních údajů/Vždycky ignorovat....

Zkuste v nastavení, ochrana osobních údajů, zadat vždy ignorovat.

Problematika typů u pq je dost složitá. Tak jen krátce.

Pokud přidáte sloupec a deklarujete typ, tak je to deklarace jen na úrovni tabulky. Obsahu jednotlivých buněk netýká se to netýká a pokud nějaký typ požadujete, je to nutné ošetřit ve funkci.

Tohle bude fungovat

let
Zdroj = Table.FromList({1 .. 10}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Přidané: Vlastní" = Table.AddColumn(Zdroj, "Vlastní", each "asg", Int64.Type)
in
#"Přidané: Vlastní"

a sloupec "Vlastní" bude deklarován jako celé číslo.

Jinak řečeno: V tabulce se deklarují "typy" sloupců, ale tyto typy nemusí odpovídat typu jednotlivých buněk.

Navíc během výpočtu na deklarovaném typu moc nezáleží, hlídají se hlavně typy předávané funkcím.

Navíc k přepsání deklarovaného typu dochází v procesu zpracování běžně.

Změna typu je něco jiného, tam občas dochází ke skutečné korekci dat.

Typ má ale význam při interakci s okolím. Tj. v případě exportu tabulky do listu, kt, datového modelu je k typu přihlíženo.

Závěr: Nastavení typu je důležité na konci zpracování. V procesu výpočtu je potřebné jen výjimečně.

ps. Přidávání sloupce nebo oprava sloupce. Pokud má tabulka pár řádků je to jedno. Když má pár (desítek) miliónů řádků , tak to jedno není.

Dokud jsem to používal, tak ano. Teď už to nepotřebuju, pbi to má vestavěné.

Hláška "váš dotaz byl vytvořen v jiné verzi" je celkem běžná, pokud se otevírají starší sešity. Obvykle to není problém.

Co se týká nových verzí, je to samostatná diskuse, občas se chování potichu mění.

Bez problémů funguje najít/nahradit.

Vyberte sloupec tabulky (třeba Sloupec1), klikněte na najít, nahradit.
najít: null, nahradit: 0 je to celkem jedno. Jen je třeba kliknout na nahradit celou buňku.

Kontrola: V kódu je Replacer.ReplaceValue. Pokud ne, tak to opravte.

Dál. Parametry v Table.ReplaceValue mohou být funkce.
Pokud je funkcí hledaná nebo nahrazovaná hodnota je parametrem record odpovídající aktuálnímu řádku - stejné jako v addcolumn, ...)

Tj. co se nahrazuje (vygenerováno by mělo být null) nahraďte funkcí, která vrací hodnotu z prohedávaného sloupce. V tomto případě je to each [Sloupec1]. Sloupec dosaďte vlastní. (Je to funkce. Má generovat hodnotu buňky, která se bude nahrazovat. Jak si ji vytvoříte, taková bude.)

Čím nahradit (vygenerováno by mělo bý 0, jinak 3. parametr)
Funkce by měla být stejná, jako v případném addcolumn.
each [Sloupec1] + [další sloupec].

ps. Možnotí je samozřejmě více. Tuhle považuji za nejjednodušší.

Záleží na verzi.

Fungovala finta. V data,připojení si najděte to, které vytváří vaši tabulku v excelu a vytvořte si na některém listu její kopii.

Pak si tuto novou tabulku načtěte do pq, načtěte nová data, ... kontrola překrývajících se dat, ... a spojte je.

Finta je v tom, že pq nesmí o kopii vědět. Někdy bylo nutno vytvořit i druhou kopii, tu předchozí bylo možné smazat.

Asi nečtete odpovědi. eLCHa už odpověděl.
Rozumné řešení v podobě, kterou požadujete zřejmě neexistuje.

Pokud odmítáte power query i vba, pak zkuste navrhout nějaký jiný postup nebo použít jiný sw, např. některý z mnoha etl nástrojů.

@eLCHa
Na jeden problém je vždy více řešení.

Použil {"YY";"XX"}, i když v tomto případě bych tam nejspíš použil odkaz do tabulky, ve které by byly hledané řetězce. Tj. hledané hodnoty pak lze měnit/doplňovat bez změny vzorce + hledané hodnoty vidím v listu.

To je podle mne hlavní rozdíl proti původně použitému samostatnému testování jednotlivých klíčů.

Mimochodem, v případě, že prohledávaná data jsou velká (toto jistě není takový případ), tak ke zjišťování existence hodnoty používám raději funkce typu ...lookup, ...vyhledat,...

Rozdíl je v tom, že countif vždy prohlídne všechna data, vyhledávací funkce po nalezení shody končí. Data lze také často setřídit, a pak je hledání ještě rychlejší. Vyhledávací funkce také neprovádí konverze dat.

Rychlost závisí na počtu nalezených hodnot a na možnosti setřídit prohledávané pole.

Taky jde

=KDYŽ(SOUČIN.SKALÁRNÍ(--JE.ČISLO(HLEDAT({"YY";"XX"};A2)));"OK";"-")

Nechápu, co tím autor zamýšlel.

Tak jen co to dělá:

COUNTIF($PF$9:PF9;[Barva tyče])

prohledá sloupec PF od začátku až do předchozího řádku a spočítá, kolikrát v něm se vyskytuje každá (asi) barva z [Barva tyče]. Vrátí sloupec v rozsahu [Barva tyče].

Pak výsledek prohledá a vypíše, první barvu z [Barva tyče], která zatím nebyla nalezena.

Text je dobrý, ale...
a) Je náchylný na automatickou změnu typu
b) pokud se při zpracování používají funkce jakou sumif,... a číslo slouží jako klíč, tak i když je číslo uloženo jako text, včetně ', tak tyto funkce automaticky takový text převedou na číslo (včetně zaokrouhlení posledních pozic).

Úplně dobré řešení zřejmě není.

Možnosti:
- číslo vhodně rozdělit na 2 části a ty pak zpracovávat samostatně

- k číslu přidat textový znak (na začátek nebo na konec) - ne ' na začátek.

- zpracovávat v power query - ale to také není úplně bezpečné - "formátovat" jako měnu, nebo používat při výpočtech parametr Precission.Decimal. Stejně se to špatně hlídá. Pokud se s číslem neprovádí žádné výpočty, tak načíst jako text, zpracovat a vrátit. Zase pozor na automatické rozpoznání typu...

64 bit verzi excelu nepoužívám. Detaily např.:

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ee691831(v=office.14)?redirectedfrom=MSDN

Mimo to jsou hlášeny problémy s uvedenými funkcemi:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/94fbf641-3c9a-46e3-a8a0-bba10829e57d/update-vba-code-to-64-bit-from-32-bit?forum=exceldev

Jinak google doručí dost informací na 1. pokus:

https://www.google.com/search?q=win64+SHBrowseForFolderA+excel+vba

Oprav si deklarace api volání.

Nějak tak (netestoval jsem to):

#If VBA7 Then

Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean

Private Type BROWSEINFO
hOwner As LongPtr
pidlRoot As LongPtr
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As LongPtr
lParam As LongPtr
iImage As Long
End Type

Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As LongPtr
#Else
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

#End If

ps. Vždy upozorňuji na to, že tupé kopírování kódu není dobrý nápad.

Příspěvky uživatele

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje