MS Excel

Myslím, že se to stalo každému, kdo někomu něco narychlo vytvořil "ze svých zásob". Něco se zkopíruje, něco se přidá, rychle se zkontroluje, jestli to počítá a pryč s tím, protože kdo rychle dává, dvakrát dává. Lehce ale přitom může dojít k rozčarování, když příjemce takového dárku narazí na zprávu o existujících propojeních, s čímž sám těžko něco udělá.
Klasickou příčinou takového stavu jsou zpravidla vzorce ze zdrojového sešitu, které se odvolávají na jiné listy v rámci svého sešitu. Při jejich kopírování dává Excel přednost odkazu na zdroj před odkazem na listy nově vznikajícího sešitu. Při testování funkčnosti nemusíme poznat nic, protože ty propojovací odkazy najdou zdroj a fungují správně. Jakmile se ale sešit s takovými odkazy od nás odstěhuje, problém je na světě.
V příloze je rutina, která výše uvedený problém odstraňuje tím, že propojovací odkazy ve vzorcích vybrané oblasti "lokalizuje", tj. převádí je na odkazy v rámci svého sešitu. Několikrát už mi tenhle postup vytrhl trn z paty, a to zejména u sešitů, které jsem s popsanou vadou obdržel od jiných. Pokud si na to riziko vzpomenu, proženu tou procedurou své odvozené sešity dřív, než je pustím z ruky. Občas se to vyplatí...

Zaslat odpověď >

xlnc(9.10.2015 19:03)#027299

Je to od vás hezké, nic ve zlém, ale odhadem 50-70 % uživatelů to nebude nic platné. Poslední propojení jsem odstraňoval před dvěma dny asi hodinu. Propojení není jen v prostých vzorcích listu... jen namátkou: podmíněný formát, definované názvy, data/ověření, objekty (grafické elementy s propojením, ovládací prvky, ...), dotazy (MS Query a technologie pro sosání dat, XML), reference, připojené knihovny, doplňky... bohužel i VBA je dosti omezené. Umí říct "ano, ve vzorcích je propojení", ale tím to končí, zdroj neukáže, to může znamenat třeba jedno desítek data/ověření na listu... Takže to může pořádně zavařit závity. V neposlední řadě je možné rozbalit XLS? a podívat se do XML, ale to je taky radost v těch vazbách najít původce.citovat

Vovka(9.10.2015 20:16)#027309

Pokud by to mělo třiceti až padesáti procentům lidí, postižených nechtěným propojením, přinést pomoc (dopočet do sta procent), pak budu nadmíru spokojený. Jsem si vědomý, že míst, kde se může propojení skrývat, je opravdu velké množství a já zkoumám jen jedno z nich. Takže váš odhad účinnosti pro zaslaný nástroj pokládám za velkou poctu pro něj.citovat

Vovka(12.10.2015 17:26)#027356

Přece jenom jsem se zamyslel nad tím, jestli bych nedokázal rozšířit záběr nástroje na další objekty při odstraňování vzdálených propojení. Ze svých snah jsem dopředu vyloučil externí linky pro grafické a další objekty. Soustředil jsem se na ty vlastnosti, které připouštějí existenci vzorců ve svém obsahu. Z tohoto výběru jsem se nakonec rozhodl řešit otázku vzdálených odkazů pro následující objekty:
buňky na listech; názvy listu i sešitu; ověření; podmíněné formáty; série v samostatných i zabudovaných grafech; pole v KT.
Vznikl mi ovšem hloupý problém: nemám k dispozici sešity s nechtěným propojením pro většinu z vyjmenovaných případů. Nemohu proto ověřit, jestli napsané rutiny opravdu dělají to, co od nich požaduji. Obracím se proto na ty, kteří mají k dispozici sešity s nechtěným propojením, jestli by nebyli ochotni zaslat některé z nich na mou adresu. Já bych získal materiál k testování a vy byste v příznivém případě dostali zpět "uzdravené" sešity. Pokud bude můj testovací proces zdárně ukončen, jsem rozhodnutý nástroj uvolnit pro obecné použití.citovat

xlnc(12.10.2015 19:47)#027361

Já myslím, že si zvládnete vytvořit "paskvily". Jednoduše odrbejte zdroje (přesuňte je, smažte). Jinak předkládám prvotní "info makro":

Sub ExterniOdkazy()

Dim wksList As Worksheet

'jsou v sešitu externí odkazy?

'odkazy ve vzorcích na listu
'propojené kreslené prvky a textová pole (dynamické odkazy s pomocí vzorců)
'ovládací prvky, prvky formuláře aj.
ExtLinkyVzorce = Not IsEmpty(ActiveWorkbook.LinkSources(xlExcelLinks))

'odkazy typu OLE, resp. DDE (vložené objekty)
ExtLinkyOLE = Not IsEmpty(ActiveWorkbook.LinkSources(xlOLELinks))

'odkazy ve formě dotazů (SQL, web, databáze)
ExtLinkyQuery = False
For Each wksList In ActiveWorkbook.Worksheets
If wksList.QueryTables.Count > 0 Then
ExtLinkyQuery = True
Exit For
End If
Next wksList

'odkazy v podobě dat XML
ExtLinkyXML = ActiveWorkbook.XmlMaps.Count > 0

'odkazy v podobě referencí z VBA
ExtLinkyRef = False
With ActiveWorkbook.VBProject.References
For i = 1 To .Count
If (Right(.Item(i).FullPath, 4) Like ".xl?") Or _
(Right(.Item(i).FullPath, 5) Like ".xl??") Then
ExtLinkyRef = True
Exit For
End If
Next i
End With

SesitObsahujeExtOdkazy = ExtLinkyVzorce Or ExtLinkyOLE Or ExtLinkyQuery Or _
ExtLinkyXML Or ExtLinkyRef

End Subcitovat

Vovka(19.10.2015 17:26)#027551

Vracím se k tématu s tím, že jsem svůj původní algoritmus revize vzorců v buňkách doplnil o prohlídku vzorců v názvech, grafech a kontingenčních tabulkách s následnou lokalizací vzorců. V případě externích odkazů ve vzorcích pro ověření dat a pro podmíněné formáty se ukázalo, že se takové odkazy deformují tak významně, že jejich automatickou lokalizaci nelze realizovat. Na zjištěné případy reaguje prověrka pouze varovnými zprávami.
Proceduru pro indikaci externích linků v prověřovaném sešitě jsem plně převzal z příspěvku od xlnc. Tato procedura linky hledá, aniž by se je pokoušela nějak modifikovat.
Inovovaný nástroj LokalizaceVzorcuX je vybavený nemodálním formulářem s voláním obou možných prověrek. Je napsaný tak, aby mohl být použit jako běžný doplněk.

Příloha: zip

27551_lokalizacevzorcux.zip (38kB, staženo 156x)

citovat

Téma: Likvidace nechtěných propojení

Uživatelské menu

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Aktivní diskuse

Čas od do

Makro smyčka

Makro smyčka

Čas od do

Čas od do

Čas od do

Čas od do