[Excel] Het grote Excelvragentopic #45

En wat als je het niet als csv opslaat maar als txt (delimited)?

Ik vind dat geen loze enter. De applicatie die de csv inleest zou daar maar mee om moeten kunnen gaan.

Zeg nou zelf, wat is logischer:
- elke regel in het bestand wordt afgesloten door een enter, of
- elke regel behalve de laatste wordt afgesloten door een enter?

^^Nu ook een paar varianten mee geprobeerd. Tab delimited txt, txt MSDOS etc. Creëert allemaal min. 1 extra lege regel onderaan.

Ik denk dat het ergens best werkbaar zal zijn voor de gebruiker om deze eenmalige handeling nog in Notepad te doen, omdat we met deze csv sowieso al heel veel tijd gaan winnen, maar ik heb ergens het idee dat ook dit probleem gewoon opgelost zou moeten kunnen worden.

Helaas heb ik geen invloed op hoe de applicatie werkt, die is van een externe partij.

Ik heb een vraagje:

Ik heb 2 kolommen met data (zeg A en B) die niet op alle plekken gevuld zijn. Nu heb ik een IF functie toegepast die me een 1 geeft wanneer A1 en B1 tegelijkertijd een willekeurige waarde in hun cel hebben staan en de functie geeft me een 0 wanneer er in beide of in 1 van beide cellen geen waarde staat, dus: =IF (AND(A1>0;B1>0);1;0).

Deze functie heb ik naar beneden gesleept (zeg in kolom D) en beslaat nu zo'n 2000 rijen met 1-en en 0-len overeenkomstig met de data. Op het einde tel ik alle 1-en op, dus ik neem de som van de hele kolom om zo te zien hoe vaak er een waarde in kolom A en tegelijkertijd in kolom B staat en dat werkt eigenlijk prima. Maar nu vroeg ik me af of het mogelijk is om dit alles in 1 formule te zetten. Zodat ik geen 2000 rijen hoef te beslaan om vervolgens alle cellen op te tellen.

Ik hoop dat de vraag een beetje duidelijk is, als het nodig is kan ik misschien wat meer informatie geven, misschien met screenshots.

Alvast Bedankt!

quote:

Op dinsdag 26 april 2016 13:34 schreef Modus het volgende:
Ik denk dat het ergens best werkbaar zal zijn voor de gebruiker om deze eenmalige handeling nog in Notepad te doen, omdat we met deze csv sowieso al heel veel tijd gaan winnen, maar ik heb ergens het idee dat ook dit probleem gewoon opgelost zou moeten kunnen worden.

Als je een beetje ervaring hebt met vba, dan je hele csv export doen via macro. En dan met stream read/write voorbeeld de text inlezen en dan iets van StrStream = left(StrStream, len(StrStream )-2)
en dan StrStream weer wegschrijven.

(Wel eerst in VBA in -> References -> "Microsoft Scripting RunTime" aanvinken.)

[ Bericht 12% gewijzigd door The_vice op 27-04-2016 12:19:32 (nazorg) ]

quote:

Op woensdag 27 april 2016 11:44 schreef qu63 het volgende:

[..]

SUMIF()

Dankje! Het wil echter nog niet zo vlotten aangezien ik steeds 0 uit krijg op welke manier ik de de functie ook verander.

Is het mogelijk om als "criteria" in de SUMIF functie de functie IF(AND(A1>0;B1>0);1;0) te nemen? Alhoewel, dan kijkt die ook maar naar één cel, dus misschien iets als: IF(AND(A1:A2000>0;B1:B2000>0);1;0). Ben de draad kwijt

quote:

Op woensdag 27 april 2016 12:13 schreef The_vice het volgende:

[..]

Als je een beetje ervaring hebt met vba, dan je hele csv export doen via macro. En dan met stream read/write voorbeeld de text inlezen en dan iets van StrStream = left(StrStream, len(StrStream )-2)
en dan StrStream weer wegschrijven.
[ code verwijderd ]

(Wel eerst in VBA in -> References -> "Microsoft Scripting RunTime" aanvinken.)

Ha interessant. Wat ik tot nu toe heb gaat ook via VBA, en stream gedoe ken ik wel maar gebruik het niet vaak dus ook niet meteen aan gedacht.

Dus wat ik eik moet doen is:
-basis xlsm bestand, daarin de data formatten, data kopiëren naar nieuw leeg bestand en dat opslaan als csv
-vanuit de macro met stream object die csv uitlezen
-vanuit de macro met stream object nieuw bestand aanmaken en de content (min die 2 kar.) daar in wegschrijven en opslaan als nieuw csv.

Moet ik in die laatste stap echt een nieuwe filenaam geven (zodat ik daarna dus 2 csv's heb) of kan ik daarmee die bestaande csv overschrijven? Als dat eerste van toepassing is, dan kan ik denk ik ook met dit streamobject verhaal dat eerste csv bestand (want niet meer nodig) deleten, toch?

quote:

Op woensdag 27 april 2016 14:37 schreef qu63 het volgende:

[..]

Eigenlijk bedoelde ik SUMPRODUCT()

=SOMPRODUCT(--(A1:A2000<>"");--(B1:B2000<>""))

Gelukt! Bedankt!

quote:

Op woensdag 27 april 2016 15:03 schreef Modus het volgende:
[..]

Door in de laatste stap het opnieuw te open (met zelfde filenaam), maar dan middels "ForWrite" wordt alles overschreven met de inhoud van je stream.
(andere mogelijkheden zijn Writeline of Append, maar dat is voor toevoegen)

Verder nog. De netste methode is om direct vanuit je sheet data naar file stream te schrijven.
zelf zou ik dan beginnen om alles in een Array te duwen middels:

Thx, ga er vandeweek mee verder.

Bij "Insert Object from a file" -> Create from file krijgt het icon als standaardcaption het volledige bestandspad. Kan ik die default ergens zetten op puur de bestandsnaam zelf, zodat ik dat niet elke keer handmatig hoef aan te passen?

Excel 2013 Engels.

Is er een manier om ervoor te zorgen dat de data die ik omzet naar een tabel altijd tot de laatste cel in die tabel gaat? Ik heb hier een macro voor maar de tabellen verschillen dus elke keer in het aantal regels.
De complete macro verzamelt verschillende tekst bestanden, laadt deze allemaal onder elkaar in 1 sheet, zet de data om naar kolommen en vervolgens zet ik die kolommen om naar een tabel.
Nu is het zo dat die lijsten elke keer verschillend zijn qua aantal regels. Uit voorzorg selecteer ik tot regel 10.000 voordat ik het omzet naar tabel. Maar daarna maak ik nog weer een overzicht in een pivot, die pivot telt natuurlijk al die lege regels ook mee. Dat kan ik uitzetten in het filter maar ik heb liever dat tijdens het omzetten naar tabel gewoon alle gevulde regels worden omgezet.

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$10000"), , xlYes).Name _
= "Table1"

De range zou ik dus graag aangepast hebben zodat ie elke keer de laatste cel met data van de tabel vindt.

quote:

Op vrijdag 29 april 2016 11:25 schreef Puckzzz het volgende:
Excel 2013 Engels.

Is er een manier om ervoor te zorgen dat de data die ik omzet naar een tabel altijd tot de laatste cel in die tabel gaat? Ik heb hier een macro voor maar de tabellen verschillen dus elke keer in het aantal regels.
De complete macro verzamelt verschillende tekst bestanden, laadt deze allemaal onder elkaar in 1 sheet, zet de data om naar kolommen en vervolgens zet ik die kolommen om naar een tabel.
Nu is het zo dat die lijsten elke keer verschillend zijn qua aantal regels. Uit voorzorg selecteer ik tot regel 10.000 voordat ik het omzet naar tabel. Maar daarna maak ik nog weer een overzicht in een pivot, die pivot telt natuurlijk al die lege regels ook mee. Dat kan ik uitzetten in het filter maar ik heb liever dat tijdens het omzetten naar tabel gewoon alle gevulde regels worden omgezet.

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$10000"), , xlYes).Name _
= "Table1"

De range zou ik dus graag aangepast hebben zodat ie elke keer de laatste cel met data van de tabel vindt.

Na enig zoeken en testen heb ik een code gevonden die werkt:

quote:

ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "Table1"

Ok, ik kom er niet meer uit. Het zal ongetwijfeld belachelijk simpel zijn, maargoed

Ik werk met Excel 2013, Nederlandse taal.

Ik heb 4 rijen (zie hieronder). Kolom 1 is wat we hebben gehaald, kolom 2 is de eigenlijke norm, kolommen 3 en 4 zijn de gegevens die ik koppel aan het resultaat uit kolom 1. Klom 3 en 4 horen per rij bij elkaar.

Ja, vaag . Maar ik wil dus:
Als 8:06 gerealiseerd is, dat de formule dit ziet, kijkt naar kolom 3 en het bijbehorende gegeven uit kolom 4 ophaalt. Dus dat er dan 95 wordt opgehaald.
Ook zou ik willen zien dat als er een resultaat is van 8:00 of eerder, er 100 komt te staan.

Ik heb al geklooid met een eindeloze =ALS formule en daarmee kom ik een eind, maar ik kreeg een foutmelding vanaf 8:06 (formule denk ik te lang?)


Help!

^^Zijn de gegevens in kolom A en B een tijdstip of een tijdsduur?

quote:

Op vrijdag 29 april 2016 18:35 schreef Modus het volgende:
^^Zijn de gegevens in kolom A en B een tijdstip of een tijdsduur?

Tijdstip

quote:

Op vrijdag 29 april 2016 18:41 schreef GotC het volgende:

[..]

Tijdstip

In E2 zou dan zoiets moeten komen


en dan doortrekken, maar om mij nog onduidelijke reden kan ie dan de 08:03:00 en de 08:06:00 niet vinden die er wel degelijk in staan, dan geeft ie een #N/B, de 8:02 en 8:01 kan ie wel vinden. Echt GEEN IDEE hoe dit kan. Ms toch iets met dat tijdrekenen, dat is altijd gekut.

11:01:00 vindt ie logischerwijs ook niet, want die staat niet in kolom C.

[ Bericht 15% gewijzigd door Modus op 29-04-2016 19:44:33 ]

quote:

Op woensdag 27 april 2016 12:13 schreef The_vice het volgende:
(Wel eerst in VBA in -> References -> "Microsoft Scripting RunTime" aanvinken.)

Zonder hier naar te kijken vanmorgen een scriptje gemaakt met FSO en dat werkte zoals ik wilde, dus ook zonder die laatste enter nu. Thanks. Als dat vinkje een vereiste is, neem ik aan dat het toen aanstond.

's Middags ergens kreeg ik ineens op de regel met de fso.CreateTextFile opdracht een Permission Denied error, terwijl die regel vanaf begin af aan niet veranderd is geweest. Heb ik ineens geen schrijfrechten meer in betr. dir?? Toen dat vinkje maar eens gaan checken, stond niet aan. Aangevinkt, toen werkte het weer. Ehhhhh? Ben nooit aan dat vinkje geweest.

Kan IT op de achtergrond hebben lopen rommelen om te voorkomen dat ik dit soort scriptjes ga gebruiken?

-edit
toen ik de CSV uiteindelijk probeerde in te lezen begon hij weer te miepen. Na uitzoeken blijkt dat te slaan op enkele harde line breaks binnen cellen die ik in het Excelbestand heb staan. Bij voorkeur moeten die er wel in blijven staan, maar die moeten blijkbaar ook op 1 of andere manier 'geformatteerd' worden om bruikbaar te zijn.

Bij het inlezen van de csv kan ik nog een paar dingen instellen, zoals de seperator (werkt verder goed nu) en de line break, maar ik neem aan dat die slaat op einde van de hele regel en niet op 1 'losse entry'.

[ Bericht 11% gewijzigd door Modus op 29-04-2016 20:10:15 ]

quote:

Op vrijdag 29 april 2016 19:44 schreef Modus het volgende:
[..]

Kan ook zijn dat een ander process het nog open had (bijv. preview in Windows Explorer.) Of dat tijdens debuggen het bestand nog niet goed was afgesloten.

Speciale karakters zijn vaak struikelpunten in code. Je wilt niet weten hoe vaak ik me blind zit te staren op dubbele spaties, Non-Breaking spaties etc. Wat ik soms dan doe is "vbLf" of chr(10) even tijdelijk vervangen door de tekst "[Linefeed]". En dan verderop weer terug omzetten Replace(.... "[Linefeed]" -->chr(10) ) o.i.d. Niet al te sjiek, maar zorgt er wel voor dat je weer verder kan.

Zou ik dan wel in een aparte functie(s) wegwerken, dan vervuil je je hoofdcode niet, en kan je het makkelijk verder bijpoetsen of uitbreiden binnen de functie(s) waar nodig.

quote:

Op woensdag 23 maart 2016 10:59 schreef Doevelaer het volgende:
Wie weet of dit kan en hoe het dan werkt:

Ik wil graag opmaak, formules e.d. van het werkblad A naar werkblad B kopiëren, echter zonder dat de gegevens in werkblad B overschreven worden.
.......

Als de formulerijen in alle 50 sheets écht op dezelfde adressen staan.
Dan (in backup testen)

• Groepeer de desbetreffende sheets • En dan word alles wat je doet op de achterliggende gegroepeerde sheets herhaald. Dus wat je plakt, typt, opmaakt, print instellingen etc.
• niet vergeten, groepering weer opheffen.

persoonlijk ben ik geen fan van meerdere werkbladen met min of meer zelfde inhoud. Maar als je het echt nodig hebt dan zou ik het middels boven beschreven groepeer oplossing oplossen.

quote:

Op vrijdag 29 april 2016 20:59 schreef The_vice het volgende:
Speciale karakters zijn vaak struikelpunten in code. Je wilt niet weten hoe vaak ik me blind zit te staren op dubbele spaties, Non-Breaking spaties etc. Wat ik soms dan doe is "vbLf" of chr(10) even tijdelijk vervangen door de tekst "[Linefeed]". En dan verderop weer terug omzetten Replace(.... "[Linefeed]" -->chr(10) ) o.i.d. Niet al te sjiek, maar zorgt er wel voor dat je weer verder kan.

Hm lastig, nog nooit eerder zoiets gedaan. Ik denk dat ik 2 opties heb om uit te werken:

-de harde enters in de cellen laten staan en VBA die bij het uitlezen van de cellen laten herkennen en op 1 of andere wijze in de tekststring verwerken; geen idee of dat kan en zo ja hoe
-de harde enters in die cellen weghalen en in betreffende celtekst op de juiste plekken iets van chr(10) ertussen fietsen??

dus een celtekst a la adres1chr(10)plaats1 ??

-edit
na wat googelen denk ik dat ik wellicht het beste die cellen met line breaks in mijn basislijst kan vervangen door losse cellen met elk 1 regel
En in de code die ik nu heb die de tekststring samenstelt daar waar nodig een char(10) tussen plakken
-edit2
dat laatste heb ik net getest, dat krijg ik werkend
vandeweek verder

[ Bericht 7% gewijzigd door Modus op 29-04-2016 23:01:39 ]

quote:

Op vrijdag 29 april 2016 19:44 schreef Modus het volgende:
Bij het inlezen van de csv kan ik nog een paar dingen instellen, zoals de seperator (werkt verder goed nu) en de line break, maar ik neem aan dat die slaat op einde van de hele regel en niet op 1 'losse entry'.

Integendeel, lijkt me juist heel onlogisch dat die op het einde van de regel slaat en niet op een 'losse entry'. Heb je het al geprobeerd?

quote:

Op vrijdag 29 april 2016 22:09 schreef ralfie het volgende:

[..]

Integendeel, lijkt me juist heel onlogisch dat die op het einde van de regel slaat en niet op een 'losse entry'. Heb je het al geprobeerd?

Ja. Hij liep vast op de 2e regel in de csv (dus de 1e regel met data). Toen gezorgd dat de cel die daarbij hoort geen line breaks had, en opnieuw geprobeerd. Toen begon de appl. te miepen over hetzelfde item, maar dan 1 regel lager. De meldingen die die app. geeft zijn nogal summier, maar geeft wel aan welke regel (en welk item op die regel) van de csv problemen geeft.

quote:

Op vrijdag 29 april 2016 22:17 schreef Modus het volgende:
[..]
Ja. Hij liep vast op de 2e regel in de csv (dus de 1e regel met data). Toen gezorgd dat de cel die daarbij hoort geen line breaks had, en opnieuw geprobeerd. Toen begon de appl. te miepen over hetzelfde item, maar dan 1 regel lager. De meldingen die die app. geeft zijn nogal summier, maar geeft wel aan welke regel (en welk item op die regel) van de csv problemen geeft.

Heb je notepad++? Daarin kan je met knop "¶" (Show all characters) controleren wat in je csv zit (of terecht is gekomen).
Als ik een linefeed in een cell stop, komen er in de .csv Saveas van Excel zelf ineens quotes omheen te staan. Kan soms ook weer onwenselijk zijn.

Is de applicatie waar je in wilt importeren voorzien van een goede help file/specificatie, of ergens een support van de maker beschikbaar? Dan wil voorbeeld opsturen van CSV ook nog wel eens voor verheldering kunnen zorgen. Als de makers uitgingen van netjes gekamde fris gewassen csv's heb je zomaar kans op issues

Ik heb geen Notepad++ beschikbaar. Voor de app. is een handleiding beschikbaar maar die is ook vrij beperkt. Namen van de kolomheaders en nog wat info en daar houdt het nu mee op.

1 van de velden is bv. een eenheid (kg, liter etc). Bij handmatig invoeren krijg je hier een dropdown (de app is een webform). Je zou verwachten dat de csv dan precies die termen moet gebruiken. Nee dus. Tig variaties geprobeerd, kg, KG, KILO, KILOGRAM etc. Werd allemaal niet geaccepteerd. Wat moet het wel zijn: als je in de source van dat webform kijkt zie je dat elk item in die dropdown een code heeft (100, 200, 300 etc). Die maar eens geprobeerd en dat bleek het geval. Wordt nergens over gerept in de handleiding en dat vond ik wel slecht.

Met jouw tips en verder googelen lijkt dit linebreakding nu opgelost (maar kan dat dinsdag pas weer testen).

Verder moeten ingevoerde getallen altijd 3 cijfers achter de komma hebben voor de app. Mijn eerste versie deed dat zonder problemen, maar jouw script door elke cel uit te lezen naar een tekststring pakt die niet, ondanks ingestelde veldsettings. Moet ik ook nog uitzoeken.

quote:

Op zaterdag 30 april 2016 10:23 schreef Modus het volgende:
Ik heb geen Notepad++ beschikbaar.

Is gratis te downloaden en ook als portable van usb stick te draaien, mocht je niet kunnen installeren.

quote:

Verder moeten ingevoerde getallen altijd 3 cijfers achter de komma hebben voor de app. Mijn eerste versie deed dat zonder problemen, maar jouw script door elke cel uit te lezen naar een tekststring pakt die niet, ondanks ingestelde veldsettings. Moet ik ook nog uitzoeken.

Kan wel kloppen, de Array is als een Variant gedefinieerd, die zal dus zelfstandig nummer afronding toepassen, als een waarde numeriek is. In je directe opgeslagen CSV wordt het aantal cijfers achter de komma zoals je in Excel hebt ingesteld (per veld) als tekst opgeslagen.

Middels een If Then Else afhandeling kan je in specifiek rijen/kolommen (array coordinaten) het wel oplossen.
Zie voorbeeldje binnen j loop:

In voorbeeld slechts met 2 cijfers achter de komma. Ik ga het je natuurlijk niet te makkelijk maken.

Is er een formule om het aantal cellen in een reeks te tellen met een bepaalde opmaak?

Een collega van mij is zo slim geweest om een heel schema te maken met gekleurde vakjes zonder waarde...