Excel VBA export PDF naar map op basis van klantnummer

gewoon_marvin · 20 sep 2019

Goedendag,

Ik ben op zoek naar een VBA mogelijkheid om met één druk op de knop bepaalde bladen uit een Excel werkmap te exporteren naar PDF. Het probleem waar ik tegenaan loop is dat de map waar dit document dient te worden opgeslagen varieert per klant, en de mappenstructuur zo is ingericht dat eerst (een deel van) de klantnaam is verwerkt en vervolgens een spatie, een koppelstreepje (-), nog een spatie en dan een uniek klantnummer. De klantnaam kan uit 1 t/m 3 woorden bestaan mocht dit relevant zijn. Ook staan alle mappen op één schijf (Z).

Ik heb een werkende VBA code voor het printgedeelte (al is deze ook nog niet optimaal):

Code:

Sub Print_test()
'
' Print_test Macro
'

'
Dim Klantnr As String
Dim Jaar As String
Dim Periode As String

Klantnr = ActiveSheet.Range("C1").Value ' De macro haalt met dit command het klantnummer op in het werkblad Start, om deze later als naam voor het PDF-bestand te gebruiken.
Jaar = ActiveSheet.Range("B2").Value ' De macro haalt het jaar op uit werkblad Start
Periode = ActiveSheet.Range("D3").Value ' De macro haalt de periode op uit werkblad Start
    
If Dir("Z:\KLANTNAAM - 1000044\" & Jaar & "\WERK\" & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf") <> "" Then
   MsgBox "Het bestand: " & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf bestaat reeds"  ' Een controle om geen dubbel PDF-bestand te maken.
        ' De map waarin je de PDF-bestanden in wilt creëeren moet op voorhand aangemaakt zijn!!
       Exit Sub  'Verlaat de routine als het PDF-bestand reeds bestaat.
     Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Z:\KLANTNAAM - 1000044\" & Jaar & "\WERK\" & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=4, OpenAfterPublish:=True

End If
End Sub

Mijn vraag is dus: Hoe krijg ik het voor elkaar dat het geëxporteerde bestand in de juiste map wordt opgeslagen, op basis van het unieke klantnummer?

Op dit mooie forum kwam ik dit (oude) topic tegen: https://www.helpmij.nl/forum/showthread.php/699527-Specifieke-folder-openen-in-explorer-via-VBA. Mijn kennis reikt echter niet zover dat ik deze code begrijp, maar hieruit kan ik wel afleiden dat het mogelijk zou moeten zijn om op basis van het unieke klantnummer de juiste map op te laten zoeken door de macro.

Met vriendelijke groet!

VenA · 20 sep 2019

Als je toch al van alles aan elkaar knoopt dan is het toch niet zo heel moeilijk?

Code:

If Dir("Z:\[COLOR=#ff0000]"& KLANTNAAM & "[/COLOR] - [COLOR=#FF0000]"& Klantnr & "[/COLOR]\" & Jaar & "\WERK\" & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf") <> "" Then

Hetzelfde moet je dan ook even doen met de 'Filename'

gewoon_marvin · 20 sep 2019

VenA zei:
Als je toch al van alles aan elkaar knoopt dan is het toch niet zo heel moeilijk?

Code:

If Dir("Z:\KLANTNAAM - [COLOR="#FF0000"]"& Klantnr & "[/COLOR]\" & Jaar & "\WERK\" & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf") <> "" Then

Hetzelfde moet je dan ook even doen met de 'Filename'

Je hebt gelijk voor wat betreft het klantnummer in de map. Maar het probleem is dat de klantnaam nergens is terug te vinden in het Excel document, dus deze kan ik niet verwijzen naar een cel. De mappenstructuur is zo opgebouwd dat er van alles voor het klantnummer kan staan als klantnaam (soms afgekort, soms volledig, soms met spaties etc.). Het gaat erom dat die op basis van het klantnummer de juiste map zoekt.

VenA · 20 sep 2019

Het volledig quoten van berichten is niet nodig. Om te reageren zijn er betere en grotere knoppen te vinden. Je zal alle mappen moeten doorlopen totdat je het juiste klantnummer gevonden hebt. Dat kan met zoiets

Code:

Sub VenA()
  Set fs = CreateObject("Scripting.FileSystemObject")
  For Each it In fs.getfolder("E:\").subfolders
    x = Split(it.Name)
    If UBound(x) = 2 Then
      If x(2) = Klantnr Then
        MsgBox it.Name
        Exit For
      End If
    End If
  Next it
End Sub

gewoon_marvin · 20 sep 2019

Dankjewel voor de code. Hoe koppel ik deze nou aan de andere code? Ik heb het één en ander geprobeerd met "x" maar ik krijg het niet voor elkaar.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Z:\KLANTNAAM - 1000044\

Ik neem aan dat in deze regel iets moet worden verwezen naar "x"? Als ik het goed begrijp is x de variabele die door middel van jouw code wordt opgehaald uit de diverse klanten-mappen.

VenA · 20 sep 2019

Nee je moet iets doen met it.name. x spitst de mapnaam obv een spatie. Je krijgt dus x(0) = klantnaam, x(1) = - en en x(2) = klantnummer.

gewoon_marvin · 20 sep 2019

Werkt deze code ook als x(6) = Klantnr (bijvoorbeeld). Dus zoals onderstaand voorbeeld;

Code:

Sub VenA()
  Set fs = CreateObject("Scripting.FileSystemObject")
  For Each it In fs.getfolder("K:\").subfolders
    x = Split(it.Name)
    If UBound(x) = 2 Then
      If x(2) = Klantnr Then
      If x(3) = Klantnr Then
      If x(4) = Klantnr Then
      If x(5) = Klantnr Then
      If x(6) = Klantnr Then
      If x(2) = Klantnr Then       
        MsgBox it.Name
        Exit For
      End If
    End If
  Next it
End Sub

De klantnaam kan namelijk meerdere spaties bevatten, ook staan er voor én na het koppelstreepje een spatie.

Kan daarna de functie it.Name gebruikt worden om te linken als volgt?

Code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Z:\it.Name - " & Klantnr & "\etc.

Sorry voor de -wellicht- domme vragen maar tot een week geleden heb ik nooit iets met VBA's gedaan.

VenA · 20 sep 2019

Als je nog nooit wat met VBA gedaan hebt dan lijkt mij dit een ingewikkelde klus om zomaar met VBA te beginnen. De code in #7 gaat sowieso niet werken. Mogelijk dat zoiets werkt al begrijp ik niets van de logica van de map- en bestandsnamen. Blijkbaar is iemand dol op spaties en lange bestandsnamen waarvan de helft dubbel is.

Code:

Sub Print_test()
  Dim Klantnr As String
  Dim Jaar As String
  Dim Periode As String
  Dim c00 As String
  
  Set fs = CreateObject("Scripting.FileSystemObject")
  For Each it In fs.getfolder("Z:\").subfolders
    x = Split(it.Name)
    y = UBound(x)
      If x(y) = Klantnr Then
        c00 = it.Name
        Exit For
      End If
  Next it

  Klantnr = ActiveSheet.Range("C1").Value
  Jaar = ActiveSheet.Range("B2").Value
  Periode = ActiveSheet.Range("D3").Value
    
  If Dir("Z:\" & c00 & "\" & Jaar & "\WERK\" & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf") <> "" Then
    MsgBox "Het bestand: " & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf bestaat reeds"
    Exit Sub
   Else
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Z:\" & c00 & "\" & Jaar & "\WERK\" & "BTW " & Klantnr & "-" & Jaar & "-" & Periode & " Aansluiting " & Periode & " " & Jaar & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=4, OpenAfterPublish:=True
  End If
End Sub

gewoon_marvin · 25 sep 2019

VenA zei:
Als je nog nooit wat met VBA gedaan hebt dan lijkt mij dit een ingewikkelde klus om zomaar met VBA te beginnen.

Daar heb je helaas helemaal gelijk in.. Dit is echter het laatste 'probleem' om het hele bestand goed te laten werken, dus mijn dank tot op heden is zeer groot.

De code zoals bovenstaand werkt nog niet, ik krijg een foutmelding op de rij "ActiveSheet.ExportAsFixedFormat etc.". Mijn vermoeden is dat "c00" niet werkt, want als ik die vervang voor de daadwerkelijke naam van de map werkt de code wel.

Misschien helpt enige verduidelijk over de mappenstructuur op de Z: schijf; Deze zijn als volgt opgebouwd: KLANTNAAM (afgekort, soms met spaties ertussen zoals KLANT NAAM of KLANT NAAM TEST of KLANT_NAAM) vervolgens een spatie, koppelstreepje, nog een spatie en dan het unieke klantnummer (dit staat altijd aan het einde). Dus samengevoegd krijg je zoiets: "KLANT NAAM - 1000873" Het klantnummer (Klantnr) staat in cel C1 waarinaar wordt verwezen in de code.

VenA · 25 sep 2019

Dit blokje moet boven Set fs staan

Code:

Klantnr = ActiveSheet.Range("C1").Value
  Jaar = ActiveSheet.Range("B2").Value
  Periode = ActiveSheet.Range("D3").Value

anders wordt het klantnummer natuurlijk nooit gevonden. (Activesheet is overigens overbodig.) Dit soort foutjes ontstaan als er geen voorbeeld bestandje bijgevoegd is.

Tip:
Als je het vermoeden hebt dat een bepaalde variabele niet de juiste waarde krijgt dan zal je moeten debuggen. Hier wat leesvoer over de verschillende methodes https://bettersolutions.com/vba/debugging/index.htm

gewoon_marvin · 26 sep 2019

Top! Nu werkt de code inderdaad prima!

Nu moet ik nog even uitzoeken hoe ik print op basis van tabbladen in plaats van pagina 1 t/m x en hoe ik zorg dat het bestand wordt overschreven als het reeds bestaat. Daarnaast wellicht nog een pop-up dat het bestand succesvol is geëxporteerd.

U heeft me in ieder geval zeer goed geholpen en ik zal de tips m.b.t. debugging eens gaan lezen.

Excel VBA export PDF naar map op basis van klantnummer

gewoon_marvin

Gebruiker

VenA

Inventaris

gewoon_marvin

Gebruiker

VenA

Inventaris

gewoon_marvin

Gebruiker

VenA

Inventaris

gewoon_marvin

Gebruiker

VenA

Inventaris

gewoon_marvin

Gebruiker

VenA

Inventaris

gewoon_marvin

Gebruiker

Nieuwste berichten

Wij waarderen jouw privacy