Makro export listu do XLSX (VYŘEŠENO)

barevnej · 6. 4. 2022 20:06:49

Zdravím,

je možné pokud mám více listů vytvořit makro které by nejdříve všechny buňky v aktivním listě převedlo vše na hodnotu a následně uložilo pouze ten aktuální list do XLSX se jménem původního souboru vedle ODS.

Zatím to vždy dělám ručně, ale říkal jsem si že by to šlo zautomatizovat.

Editoval barevnej (10. 5. 2022 07:37:32)

neutr · 7. 4. 2022 16:53:53

Otestujte

Sub KopirujeListDo_ExcelXLSX
Doc = thisComponent
Dim oSheet as object
Dim oCell As Object
Dim oCursor As Object
oView = ThisComponent.getCurrentController()
oSheet = oView.getActiveSheet()
oAddr = oSheet.getRangeAddress()
nS = oAddr.Sheet
oDoc = ThisComponent
oSheet = ThisComponent.Sheets.getByIndex(nS)
oCell = oSheet.GetCellbyPosition(0, 0)
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(False)
LR = oCursor.RangeAddress.EndRow
LC = oCursor.RangeAddress.EndColumn

zkopiruj =  oSheet.getCellRangeByPosition(0,0,LC,LR)
doc.CurrentController.select(zkopiruj)
obsah = doc.CurrentController.getTransferable()
secondDoc = StarDesktop.loadComponentFromUrl("private:factory/scalc","_blank",0,dimArray())
vloz = secondDoc.Sheets(0).getCellRangeByPosition(0,0,LC,LR)
secondDoc.CurrentController.select(vloz)
secondDoc.CurrentController.insertTransferable(obsah)

osource_range=secondDoc.Sheets(0).getCellRangeByPosition(0,0,LC,LR)
osource_array=osource_range.getDataArray()
otarget_range=secondDoc.Sheets(0).getCellRangeByPosition(0,0,LC,LR)
otarget_range.setDataArray(osource_array)

Dim ZdrojURL As string
ZdrojURL = ThisComponent.URL
lenURL = LEN(ZdrojURL) 
ZdrojURL = Left(ZdrojURL, lenURL-3) & "xlsx"
sSaveToURL = ZdrojURL
secondDoc.storeToUrl(sSaveToURL, Array(MakePropertyValue("FilterName", "Calc MS Excel 2007 XML"))) 
secondDoc.close(true)
print "konec"
End Sub

barevnej · 8. 4. 2022 07:41:50

Děkuji za odpověď.
Po spuštění se otevře nová tabulka "Bez názvu 1"

Co vidím tak se nepřevedli buňky na hodnotu a ty jsou pak #N/A.
_
To že se buňky zmenší není zas tak veliký problém, to mohu obejít nastavením menšího fontu ve výchozí tabulce. Ovšem kdyby to šlo byl by to příjemný bonus, nebo nastavit všem sloupcům šířku 3,5cm :-)
_
Nicméně celé to skončí chybou

_

neutr · 8. 4. 2022 12:28:48

Ano to máte asi pravdu, ačkoliv to #N/A asi znamená, že se čtou hodnoty z jiného listu, nebo zdroje. Ale to co neuvádíte je skutečnost zda se uložil sešit XLSX. U mne se uloží do stejného adresáře se zdrojem a má jen hodnoty. U Vás by to mělo být podobně, ale pokud mohu soudit, používáte Linux a tak vzniká chyba před uložením, nebo při ukládání XLSX. Proto se nic neuloží, ale i tak by tam bylo jen to co je v Bez názvu 1.

Dejte info zdali potřebujete formáty jako jsou barvy a podobně. Já to kopíruju jako "transferable" - tedy vše a pak to přejedu kopírovánín na hornoty (pak tam formáty zůstanou).

Takže upřesněte. Makro lze snadno upravit aby v tom sešitě Bez názvu 1 byly přímo hodnoty. problém ukládání do formátu XSLX existuje - uvádí se jako bug, ale někdy se dá obejít. Můžeme otestovat místo XSLX jenom XLS? Doufám, že používáte LibreOffice. V nejhorším byste musel sešit Bez Názvu ukládat manuálně.

neutr · 8. 4. 2022 12:43:28

Otestujte :

Sub KopirujeListDo_ExcelXLSX
Doc = thisComponent
Dim oSheet as object
Dim oCell As Object
Dim oCursor As Object
oView = ThisComponent.getCurrentController()
oSheet = oView.getActiveSheet()
oAddr = oSheet.getRangeAddress()
nS = oAddr.Sheet
oDoc = ThisComponent
jmeno = oDoc.Title
oSheet = ThisComponent.Sheets.getByIndex(nS)
oCell = oSheet.GetCellbyPosition(0, 0)
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(False)
LR = oCursor.RangeAddress.EndRow
LC = oCursor.RangeAddress.EndColumn

osource_range=oSheet.getCellRangeByPosition(0,0,LC,LR)
osource_array=osource_range.getDataArray()
secondDoc = StarDesktop.loadComponentFromUrl("private:factory/scalc","_blank",0,dimArray())
secondDoc.Title = jmeno
otarget_range=secondDoc.Sheets(0).getCellRangeByPosition(0,0,LC,LR)
otarget_range.setDataArray(osource_array)

Dim ZdrojURL As string
ZdrojURL = ThisComponent.URL
lenURL = LEN(ZdrojURL) 
ZdrojURL = Left(ZdrojURL, lenURL-3) ' & "xlsx"
sSaveToURL = ZdrojURL
'secondDoc.storeToUrl(sSaveToURL, Array(MakePropertyValue("FilterName", "Calc MS Excel 2007 XML"))) 
'secondDoc.close(true)
print "konec"
End Sub

neutr · 9. 4. 2022 10:26:40

Tak snad se mi podařilo vše vyřešit - otestujte : Uložit jako XSLX
Problém ukládání jako xslx jsem obešel pomocí UNO, protože i když dialog nabízí „Uložit jako", tak jde skutečně o export typu HTML, PDF, nebo email. Basic to neumí i když na W7 se mi to podařilo, ale autoru na Linuxu to nefungovalo. Já nevím, zda to ukládání (které na Linuxu nefungovalo) není pouze obyčejné XLS jen s přepsanou koncovkou.

Také jsem snad vyřešil export formátů. Proto doufám, že to už bude na Linuxu fungovat jak má. Pokud byste otestovali a nešli chybu – dejte mi vědět děkuji.

PS:
Nyní se vytvoří sešit který obsahuje tlačítko uložit a zavřít. U mne se to zavře a po otevření ze složky tam už tlačítko (Uložit a zavřít) není. To také není košer, ale když by to na nějakém systému zůstalo i po novém otevření tak tam přidám kód, který to tlačítko smaže.

Editoval neutr (9. 4. 2022 10:31:20)

barevnej · 10. 4. 2022 10:25:42

Nejsem teď u pc, hned jak budu vše otestuji.
Ano stačí i XLS.
Ano používám výhradně Linux.
Když jsem před tím testoval tak se soubor neuložil.
Jinak jste mistr, používám několik vašich Maker X krát denně.

Editoval barevnej (10. 4. 2022 10:26:44)

barevnej · 11. 4. 2022 09:47:18

To makro co jste psal nahoře sice otevře nový sešit správně ale neuloží.
_
To z Uložto je lepší. Spustím makro a otevře se nový sešit se správným názvem a data jsou na hodnotách
Vlevo nahoře se objeví tláčítko "Ulož xlsx a zavři" kliknu na něj a...

nic se neuloží, a vyskočí ty dvě hlášky... Ovšem kliknu na "Budiž" a ještě spodní "Budiž", čímž hlášky zmizí a poté znova kliknu na tlačítko "Ulož xlsx a zavři" a hle uloží se XLSX a okno se zavře
Takže nebýt těch dvou hlášek které musím odkliknout a znova kliknout na "Ulož xlsx a zavři" tak to funguje jak má.

ludviktrnka · 11. 4. 2022 10:52:38

možná by pomohlo povolit makra v novém souboru:
dim argum(0) as new com.sun.star.beans.PropertyValue
argum(0).Name = "MacroExecutionMode"
argum(0).Value = 4
secondDoc = StarDesktop.loadComponentFromUrl("private:factory/scalc","_blank",0,argum())

neutr · 11. 4. 2022 14:02:21

Tak další verze je zde Uložit jako XLSX_verze2.

Tato verze se ovládá z hlavního menu UKLÁDÁNÍ DO EXCELU. Jsou tam dvě nabídky. Buď UložitListDo_XLSX, nebo UložitListDo_XLS.

Ta první nabídka ukládá skutečně do XLSX a je s tlačítkem. Vložil jsem tam úpravu podle pana Trnky plus jsem přidal k názvu podtržítko (na konci). Je totiž možné, že originál je také XSLX, nebo jen XLS. Potom by to vyhodilo hlášku, i když dle mne trochu jinou. Myslím, že podtržítko na konci názvu nevadí.

Ta druhá nabídka ukládá do obyčejného Excelu. Sama se uloží a hned zavře. Žádné tlačítko. Takže snad se to už povede. Zase bych uvítal kontrolu se zpětnou vazbou.

barevnej · 12. 4. 2022 10:04:43

Tak druhá verze je u XLSX naprosto stejná jako situace jako u první verze, zase ta hláška > dvakrát odkliknu "Budiž" a znova kliknu na žluté tlačítko "Ulož xlsx a zavři" a to už se provede, plus je tam to podtržítko navíc. Podtržítko mi nevadí, ovšem u mě je zdroj ODS naprosto vždy.
_
U XLS to skončí jinou chybou

a nic se neuloží.
_
ps: XLSX má u mě jinak přednost, je spousta firem kde mají přímo od vedení zakázáno otevítar XLS a vyžadují striktně pouze XLSX.

neutr · 12. 4. 2022 11:02:13

To bude problém se kterým jsem se ještě nesetkal. Napadá mne, že to může být sítí, která to vyhodnocuje jako zakázanou operaci. Navrhuji vyzkoušet to makro s XLSX tak, že nebudete mačkat tlačítko, ale pokusíte se to uložit z nabídky "uložit jako".
Další věcí je otevřít ten soubor před uložením a udělat kontrolu chyb makra. Možná se to ukáže. V každém případě bych potřeboval nahrané makro od Vás. Možná se liší od toho mého. Stačí zavolat "Záznam makra", potom zvolit "uložit jako", vyberte Excel 365 a uložte. Nakonec uložte i nahrávku makra. Toto mi potom pošlete.

Editoval neutr (12. 4. 2022 11:03:23)

barevnej · 12. 4. 2022 11:59:58

Udělal jsem tedy následující postup.
Otevřel jsem tabulku > spustil makro"UložitListDo_XLSX" > otevřel se nový sešit a věm spustil záznam makra > Uložit jako > musel jsem vybrat kam jelikož mi to nabízelo úplně jinou složku kde jsem pracoval naposled > a toto je výsledek.

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///home/rrr-hlavni/Ulo%C5%BEit%20jako%20XSLX_verze2.xlsx"
args1(1).Name = "FilterName"
args1(1).Value = "Calc MS Excel 2007 XML"

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())


end sub

Zkusil jsem i makro poté spustit a funguje.

neutr · 12. 4. 2022 17:15:42

Nevím co to je. Vypadá to na problém knihovny (export/import filtru). Možnost odstranění problému se může odvíjet od verze LO. Vy používáte ve firmě 7.3.1.3 a mohla obsahovat chybu. Už vyšla verze 7.3.2, která může být bez chyby.
Verze 7.3.2 je fresh a pro firemní použití je doporučována verze 7.2.6 jako stable. Také se dá čekat, že by zde byla chyba odstraněna.

Z příspěvku číslo #8 je vidět hlášení problému … build/libreoffice-SshRy0/libreoffice-7.3.1-rc3/scriptbaseprov/baseprov.cxx:365. To znamená doslova problém u providera (zavaděč) scriptu u položky cxx:365 což je Excel 2007 365.

Při tom makro UNO je správně, ale funguje zřejmě jen z hlavního menu (Uložit jako). Vy jste otestoval nahrání makra a to funguje. Přitom je úplně stejné jako to, které je vygenerované makrem.

A) Jak jste makro spouštěl? Zřejmě přímo z okna Basicu. Chtělo by to použít stejné spuštění makra z vygenerovaného sešitu (bez použití tlačítka). Tím bychom obešli a případně vyloučili knihovnu tlačítka. Potom by se podobná funkce dala volat i jinak – například hyperlinkem, nebo jiným typem tlačítka. Tlačítko je podezřelé zejména proto, že ač je vygenerované, tak se neuloží. Tento problém například CheckBox mít nemusí.

B) Ještě se dá vyzkoušet volání šablony formátu xlsx, která se otevře (i třeba skrytě), dostane jen obsah a jiné jméno. Toto bychom už mohli ukládat snadněji manuálně (výběr adresáře + klik), nebo by možná fungovalo i makro. Takže možnosti jsou, ale zase jen dohady – nevím co je příčinou. Ve vlastním makru chyba není.

barevnej · 13. 4. 2022 07:44:43

Ano máte pravdu, měl jsem 7.3.1.3, aktualizoval jsem tedy na 7.3.2 a problém je stejný.
Zkusil jsem tedy nepoužít ono "žluté" tlačítko a pustit makro přímo a hláška se neobjevila a XLSX se uložil.

neutr · 13. 4. 2022 08:34:05

Potom snad už bude chodit některé. nebo obě makra z tohoto souboru Uložit jako XLSX_verze3.
Opět bych uvítal zpětnou vazbu a případný námět k realizaci.

barevnej · 13. 4. 2022 09:02:09

Tak první makro otevře nový sešit s chybou, ani po zavření chyby ani při pokusu spustit makro ručně opakovaně XLSX neuloží.

_
Druhé makro funguje prakticky stejně jako to z Verze 1 a 2, jen je tlačítko link které spustím Ctrl+klik > poté vyskočí chyba a XLSX neuloží

Ovšem zavřu li hlášku "Budiž" a opětovně Ctrl+klik na "Uložit a zavřít" už XLSX uloží a okno správně zavře.
_
I kdyby se toto nepodařilo vyřešit tak je to výborný, akorát bych musel odklikávat hlášky.

neutr · 13. 4. 2022 11:11:44

Ještě mne napadlo, že chyba vzniká možná jen na nově otevřeném sešitě. Zítra, pokud budu moci, pošlu další verzi. Ta by měla dělat něco jiného. Místo nového sešitu upravím ten původní - vybraný list zkopíruje a vložím jako hodnoty, ostatní listy smažu. Následně udělám uložení na vícero způsobů. Například dialogem FillePicker, který by měl mít přednastaven formát i adresu. Mimo toho způsoby uložení stejně jako v předchozích verzích jen s tím, že se sešit zavře (to se děje automaticky a znovu otevřu původní ".ods").
Tenhle postup by měl evokovat podobný postup ze šablony, která je už uložena jako XLSX. To je pro představu Váš zdroj .ods uložený manuálně jako šablona XLSX a proto přidám podtržítko nakonec názvu. Je možné udělat kontrolu na změny, tyto provést a ponechat jen daný list s hodnotami a s názvem rozšířeným o podtržítko. Šablona by se volala z ODS. Věřím, že nějak ten problém vyřešit půjde. Zatím uvádíte, že trojí kliknutí (Uložit, odkliknout Budiž, znovu Uložit) je už akceptovatelné, takže by to mohlo být jan na dvojí kliknutí, nebo snad i na jediné.

barevnej · 13. 4. 2022 12:21:38

No sice úvaha je správná ovšem pokud půjde o mazání ostatních listů tak se trochu bojím o rychlost. Ostatní lity totiž mají i přes 10MB jelikož obsahují i tisíce malých fotek. Ovšem u současných pokusů oceňuji kopírování do nového sešitu což je bleskové :-)
_
Ovšem je to o testování, zatím to co jste už udělal konkrétně první verze je krom nutnosti odklepnutí dvou chybových hlášek nejednodušší a plní co má na 100%.

neutr · 14. 4. 2022 07:34:47

Nyní jsem použil jak šablonu, tak původní první verzi s malinkatou úpravou a také s doporučením. Soubor 7zip je zde Soubor + šablona. Po rozbalení je to složka se dvěma soubory. Tedy s původním Calcem a soubor uložený předem jako .xlsx – to je celý název „_" + sufix souboru. Nejsou tam žádná data ani makra. Slouží jen k tomu, že se předem otevře už jako formát XLSX a také se do něj uloží. Je to jen obyčejný soubor – nikoliv šablona, a proto musí být ve stejném adresáři s Calcem.

V Calcu jsem vynechal závěrečné hlášení „konec", které má asi také podíl na vícenásobném potvrzování „budiž".

Doporučuji otestovat všechna 3 makra zabudovaná v sešitě zatím bez doporučené úpravy v posledním listu. Teprve následně provést úpravy nastavení upozornění, nebo dokonce upřednostnit otevírání Calcu v xlsx.

To třetí makro zatím vždy vyhazovalo chybu, ale nyní se otevírá ze šablony, takže to možná pomůže. Mělo by mít výhodu v tom, že se nic nepotvrzuje – hned se uloží a zavře. Ta první dvě makra pracují s tlačítkem. První makro je shodné s první verzí (jenom bez potvrzení, že je konec). To druhé makro čte šablonu – jinak dělá totéž co první makro – zabuduje makro UNO do výstupu a aktivuje se tlačítkem.
Je poměrně výhodné, že se výstup uloží jak má a při tom v něm není ani tlačítko, ani makro. Ale musíte zkontrolovat, zda je tomu tak i u Vás. Když ne, bylo by to nepříjemné – Excel by trucoval, nebo by alespoň hlásil nebezpečný kód.

barevnej · 14. 4. 2022 11:54:08

Zkusil jsem vše nastavit dle předlohy, vlastně jen tu poslední možnost jelikož ty předhozí jsem už měl odšrtnuté dávno.
Module1 a Module2 se chovají naprosto totožně. Ovšem bonus je v tom že zmizela jedna hláška takže o klik méně
_
Třetí modul končí stejnou chybou jako v minulosti, a nic se neuloží.

_
Tak teď při psaní jsem znova ještě testoval první Modul a přišel jsem na to že...
Otevřu hlavní tabulku > Spustím Makro Module1 > Otevře se nový sešit s tlačítkem "Uložit a zavři" ovšem já na něj NEKLIKNU a spustím makro ručně "uložXLSX" a žádná hláška a XLSX se uloží a sešit zavře.
_
Jinak klidně můžeme přeskočit kdy se "vizuálně" otevře nový sešit a v něm klikám nebo pouštím další makro na uložení a zavření. Prostě by se mohlo prvním makrem bez následné vizuální kontroly ihned aktivní list uložit do XLSX (jen nápad)
_
Zkusil jsem ještě jeden pokus, a to že znova namapuji ono žluté tlačítko "Uložit a zavřít"

Vím že to vypadá že žádná změna neproběhla ale poté kliknu na žluté tlačítko "Uložit a zavřít" a bez hlášky se uloží a zavře.

barevnej · 14. 4. 2022 12:18:06

Jinak jsem to teď testoval na největší tabulce co mám a ta úspora času je neuvěřitelná, doslova minuty VS vteřiny. Tohle řešení je blesk a odkliknout hlášku není až zas takový problém
_
Zeptám se, je možné aby se pamatovalo i šířku sloupců?

neutr · 14. 4. 2022 13:25:18

barevnej napsal(a)

Jinak jsem to teď testoval na největší tabulce co mám a ta úspora času je neuvěřitelná, doslova minuty VS vteřiny. Tohle řešení je blesk a odkliknout hlášku není až zas takový problém
_
Zeptám se, je možné aby se pamatovalo i šířku sloupců?

Šířky sloupců není jiný problém nežli prodloužení času po kterou makro běží. Pokud byste upřesnil kolik sloupců se má zpravit pomohlo by to. Jinak bych musel načítat a přepisovat všech 1024 sloupců. Stačí číslo s nějakou rezervou, třeba 50 i když jich používaáte třeba jen 20?

Jak dopadla všechna různáí makra? Jestli to chápu, tak Vám vyhovuje hned to první u kterého jsem zrušil oznamování konce. Docela mne čílí, že nemám Linux a nemůžu testovat sám.

neutr · 15. 4. 2022 05:27:33

Uložit jako XLSX_verze4

barevnej · 15. 4. 2022 10:43:45

Děkuji čtvrtá verze je super, pamatuje i šířku sloupců. Sice stále vyhazuje chybu ale to se dá přežít.
_
Já jsem koukal a co se týká exportních tabulek tak nejširší mám 11 sloupců, ovšem většinou je to méně.
A na délku pokud by to bylo důležité se pohybuji max kolem 10tis řádků.
_
Také mě občas čílí že nemám nikde ve svém okolí Windows skoro 15 let, ale když je to nutné tak si stáhnu ze stránek MS zkušební verzi W10 a hodím jí do Virtuálboxu. Vlastně stejně by to šlo i u Ubuntu.
_
Samozřejmě mockrát děkuji, jste neuvěřitelně schopný a ochotný člověk.

OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

#1 6. 4. 2022 20:06:49

Makro export listu do XLSX (VYŘEŠENO)

#2 7. 4. 2022 16:53:53

Re: Makro export listu do XLSX (VYŘEŠENO)

#3 8. 4. 2022 07:41:50

Re: Makro export listu do XLSX (VYŘEŠENO)

#4 8. 4. 2022 12:28:48

Re: Makro export listu do XLSX (VYŘEŠENO)

#5 8. 4. 2022 12:43:28

Re: Makro export listu do XLSX (VYŘEŠENO)

#6 9. 4. 2022 10:26:40

Re: Makro export listu do XLSX (VYŘEŠENO)

#7 10. 4. 2022 10:25:42

Re: Makro export listu do XLSX (VYŘEŠENO)

#8 11. 4. 2022 09:47:18

Re: Makro export listu do XLSX (VYŘEŠENO)

#9 11. 4. 2022 10:52:38

Re: Makro export listu do XLSX (VYŘEŠENO)

#10 11. 4. 2022 14:02:21

Re: Makro export listu do XLSX (VYŘEŠENO)

#11 12. 4. 2022 10:04:43

Re: Makro export listu do XLSX (VYŘEŠENO)

#12 12. 4. 2022 11:02:13

Re: Makro export listu do XLSX (VYŘEŠENO)

#13 12. 4. 2022 11:59:58

Re: Makro export listu do XLSX (VYŘEŠENO)

#14 12. 4. 2022 17:15:42

Re: Makro export listu do XLSX (VYŘEŠENO)

#15 13. 4. 2022 07:44:43

Re: Makro export listu do XLSX (VYŘEŠENO)

#16 13. 4. 2022 08:34:05

Re: Makro export listu do XLSX (VYŘEŠENO)

#17 13. 4. 2022 09:02:09

Re: Makro export listu do XLSX (VYŘEŠENO)

#18 13. 4. 2022 11:11:44

Re: Makro export listu do XLSX (VYŘEŠENO)

#19 13. 4. 2022 12:21:38

Re: Makro export listu do XLSX (VYŘEŠENO)

#20 14. 4. 2022 07:34:47

Re: Makro export listu do XLSX (VYŘEŠENO)

#21 14. 4. 2022 11:54:08

Re: Makro export listu do XLSX (VYŘEŠENO)

#22 14. 4. 2022 12:18:06

Re: Makro export listu do XLSX (VYŘEŠENO)

#23 14. 4. 2022 13:25:18

Re: Makro export listu do XLSX (VYŘEŠENO)

#24 15. 4. 2022 05:27:33

Re: Makro export listu do XLSX (VYŘEŠENO)

#25 15. 4. 2022 10:43:45

Re: Makro export listu do XLSX (VYŘEŠENO)

Zápatí