met VBA Excel data naar een formatted TXT bestand

[DarkValley]

Hoi Allemaal,

Het is al weer een tijdje geleden dat ik een beroep doe op jullie VBA goeroes. Normaliter kan ik het wel redelijk vinden binnen dit forum. Maar als het om de combi Excel naar TXT gaat ben ik het spoor nu toch helemaal bijster!
Ik probeer via VBA vanuit het tabblad <DATA> een opgemaakt TXT bestand te maken via VBA. De code moet daarbij rekening houden met de instellingen in het tabblad <Template>

Hierin staan de kolomkoppen met begin positie en het aantal posities die max gebruikt mogen worden (per kolom).
Als het aantal gebruikte posities (in een cel tabblad DATA) kleiner is, dan wat het template aangeeft moet dit worden aangevuld met spaties tot aan de maximale lengte.

Dus als voorbeeld <Gereserveerd> zijn dan de eerst 0 t/m 22 posities alleen maar spaties.

NB. Alle informatie over recordlengte heeft betrekking op de maximale recordlengte plus CR/LF. Een record kan korter zijn als de rest van de record alleen uit spaties bestaat. Records eindigen echter altijd met een CR/LF (Carriage Return (decimaal 13) / Line Feed (decimaal 10) combinatie.

Ideaal zou het zijn als de code per <Naam> kijkt in tabblad DATA wat de gegevens zijn en deze dan in een TXT bestand zet, rekening houdend met begin Pos en de Lengte.
De Header moet niet worden meegenomen in het TXT bestand

Tabblad: Template



Max record lengte 400 posities (komt dus CR/LF nog bij !)

Tabblad: DATA




Final TXT bestand:



Ik hoop dat jullie mij kunnen helpen.

Groet
Jeffrey

Voorbeeld bestanden:
Bekijk bijlage Voorbeeld bestand Dummy.xlsx
Bekijk bijlage 231.txt

 

[edmoor]

Druk op de knop in dit document.
Het TXT bestand komt dan in de map waar je document staat:

 

[DarkValley]

Beste Edmoor,

Jeetje wat kun je dit snel man :thumb:

Top. Het lijkt erop dat dit hem is. Ik moet nog wel even in een extern programma testen. Maar hier kan ik zeker iets mee.

 

[edmoor]

Laat het maar weten

 

[DarkValley]

@Edmoor, kun jij mij ook in de richting sturen om het geheel helemaal dynamisch te maken. Daarmee bedoel ik dat als eventueel de <namen>in template en DATA bestand) veranderen en bv ook de posities en lengte. Dit dan automatisch ingelezen wordt in de variabelen en daarmee ook kijkt naar juiste kolom in DATA?

 

[snb]

Sub M_snb()
  sn = Blad2.Cells(1).CurrentRegion
  sp = Blad1.Cells(1).CurrentRegion.Columns(4)
  
  For j = 2 To UBound(sn)
    For jj = 1 To UBound(sn, 2)
      c00 = c00 & Left(sn(j, jj) & Space(300), sp(jj + 1, 1))
    Next
    c00 = c00 & vbLf
  Next

  CreateObject("scripting.filesystemobject").createtextfile("G:\OF\voorbeeld.txt").write c00
End Sub

 

[DarkValley]

Beste Snb,

Bedankt voor jouw bijdrage. Deze lijkt meer dynamisch te zijn.

Ik neem aan dat de Space(300) de lengte 300 is uit de template (URL), klopt dit?

 

[snb]

Dat lijkt niet alleen maar zo.
Dat klopt niet. Het is de maximale lengte van een veld; dat dit in dit geval de URL is is 'toeval'.

 

[DarkValley]

Ah oke. Bedankt voor jou hulp en antwoord. Ik ga met beide testen.

 

[snb]

Je kunt ook space(400) gebruiken; het resultaat blijft hetzelfde.

 

[DarkValley]

Ja thnx dat had ik begrepen. 255 zal ook kunnen denk ik dan

Ik denk dat het is om aan te geven de max lengte per item. Klopt dat?

 

[snb]

Nee hoor, zet er maar eens een msgbox tussen.
De maximale lengte per item zit toch in variabele sp ?

 

[DarkValley]

Ja ok. Maar kun je dan uitleggen wat die space(300) doet?

 

[AHulpje]

snb plakt de data uit werkblad data aan elkaar in één lange string:

c00 = c00 & Left(sn(j, jj) & Space(300), sp(jj + 1, 1))

Om ieder item op de juiste plaats in die string te krijgen wordt ieder item aangevuld met spaties tot de lengte van dat item volgens de specificatie op werkblad template.
Voor DocNaam is dat Left(DocNaam & Space(300),30)
Space(300) is dus niets anders dan een string van 300 spaties.
In jouw voorbeelddocument zou Space(30) al genoeg zijn, maar als er een URL van meer dan 300 karakters tussen zou zitten dan wordt die dan afgekapt tot 30 karakters, vandaar dat snb terecht gekozen heeft voor 300.
Overigens een heel fraaie zeer compacte oplossing!

 

[DarkValley]

@AHulpje, bedankt voor de uitleg. Ik ga dit alles even met stappen nalopen om hier van te leren. En idd @Snd heeft een fraaie en compacte oplossing gemaakt. Eigenlijk zo compact dat het voor mij niet direct af te lezen is hoe het werkt. Bij de oplossing van @Edmoor kan ik enigszins de logica langslopen.

Dit alles is echt helemaal TOP !

Nogmaals iedereen bedankt voor de hulp en ik laat weten of het uiteindelijk door extern programma geaccepteerd wordt.:thumb:

 

[DarkValley]

@AHulpje, stel dat de template een item zou bevatten met max 500 karakters dan moet Space() toch worden => Space(500)

Begrijp ik het dan goed?

 

[snb]

Gebruik voor de 'opvulling' het aantal spaties van de maximumlengte van enig veld.

 

[DarkValley]

Ok

 

[DarkValley]

Beste @Edmoor, @Snb & @AHulpje.

Het externe programma heeft het TXT bestandje geaccepteerd. Allemaal bedankt voor jullie hulp en tot een volgende vraag.

 

[snb]

Het vreemde is, dat normaal gezien er voorlooptekens worden gebruikt; in jouw geval wilde je 'nalooptekens'

voorloop:

  msgbox right(space(300) & "abcde",10)

nalooop

  msgbox left("abcde" 7 space(300),10)