Mappen, submappen aanmaken met VBA

[pvag]

Hallo Experts,

ik heb een moeilijke vraag waarik met mijn kenner van VBA niet uit kom en hier probeer uit te leggen.
Ik heb een excel sjabloon dat elk jaar opnieuw gebruikt moet worden.
In het sjabloon heb ik een werkblad "Param" aangemaakt,
waar gegevens moeten worden ingevoerd om te gebruiken voor aanmaak van mappen en het wegschrijven van het sjabloon
Eén van de gegevens in 'Param' is het path waar het sjabloon naar toe geschreven moet worden als er in gewerkt is.
Het path is: in dit geval: F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet
Elk jaar opnieuw moet er een map met het jaartal worden toegevoegd aan het bestaande path
Voorbeeld: F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\2015
F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\2016 etc

Nu wil ik graag dat met VBA eerst gekeken wordt of het path met alle submappen bestaat.
als het path niet bestaat, moeten de mappen worden aangemaakt, aangevuld met een nieuwe map met het jaartal ( met sYear ).

Wie kan\wil me hierbij helpen

 

[edmoor]

Een voorbeeldje:

Sub CtrPath()
    Dim sPad As String
    Dim Pad() As String
    Dim i As Integer
    
    sPad = "F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\2015"
    Pad = Split(sPad, "\")
    sPad = Pad(0)
    For i = 1 To UBound(Pad)
        sPad = sPad & "\" & Pad(i)
        If Dir(sPad, vbDirectory) = "" Then
            MkDir sPad
        End If
    Next i
End Sub

 

[pvag]

Beste Edmoor,

Heel erg bedankt voor je hulp, je bent een kei!!
Ik heb het script (zie code) iets aangepast omdat het path in 'Param' wordt opgegeven.
Voorts heb ik het getest door de klok een jaar vooruit te zetten, waardoor de map 2016 netjes werd aangemaakt.
Nu moet ik nog even uitzoeken hoe ik het excel sjabloon ' Inkoop_afzet_2015 - 2016.xltm ' in de map 2015 krijg.
Nogmaals mijn dank.

Sub CtrPath()
    Dim sPad As String
    Dim Pad() As String
    Dim i As Integer
    Dim sYear As String
    sYear = CStr(Year(Date))
    
    sPad = Sheets("Param").Range("C8").Value & ("\") & sYear
    'sPad = "F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\2015"
    Pad = Split(sPad, "\")
    sPad = Pad(0)
    For i = 1 To UBound(Pad)
        sPad = sPad & "\" & Pad(i)
        If Dir(sPad, vbDirectory) = "" Then
            MkDir sPad
        End If
    Next i
End Sub

 

[pvag]

Beste Edmoor,

het is toch niet zo makkelijk om het bestand Ink_afzet.xltm in de juiste map op te slaan.
Met je hulp kan ik het juiste path aanmaken, doch het wegschrijven van het bestand geeft problemen.
Het gemaakte path is: G:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\2015"
Als ik het bestand opsla, wordt het niet in de map 2015 maar in d map 'Ink-afzet' neer gezet.
Hoe krijg ik het bestand in de laatste submap '2015' ?

Private Sub AllesOpslaan_Click()
Range("E5").Select
Dim sPath As String


 'vastleggen waar de info wordt gehaald voor het juiste path
  
 'Param C2 = Datum begin schooljaar
 'Param C3 = Datum einde schooljaar
 'Param C8 = Pathnaam
 'Param C9 = Bestandsnaam
 'Param C10 = Bestandsextensie
 
  sPath = Sheets("Param").Range("C8").Value & "\"     ' is VARIABLE path. in dit geval: G:\OOP\ConciergePlus\Excel bestanden\Ink_afzet"
   
  Dim sYear     As String
  ' Vastleggen welk jaar het nu is in een stringvariabele:
    sYear = CStr(Year(Date))
  
  Dim sFileName As String
  'Vastleggen wat de filename is voor het sjabloon
  
   sFileName = Sheets("Param").Range("C9").Value & "_" & Format(Sheets("Param").Range("C2"), "yyyy") & " - " & Format(Sheets("Param").Range("C3"), "yyyy")   ' is werkblad 'Param C9 = Inkoop_afzet.xltm
   

  ActiveWorkbook.SaveAs Filename:= _
  sPath & sFileName, FileFormat:= _
  xlOpenXMLTemplateMacroEnabled, Password:="", WriteResPassword:="", _
  ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

 

[jkpieterse]

Even een backslash voegen tussen het pad en de bestandsnaam zou ik zeggen

 

[edmoor]

@jkpieterse:
Die staat er ook. Zie : sPath

@pvag:
Plaats je document eens, of een relevant voorbeeld er van, want de waarden zijn zo niet duidelijk.
Daarnaast zou ik na het opslaan ActiveWorkbook.Saved = True er in opnemen.

 

[VenA]

sYear aan het pad toevoegen zou ik zeggen. + natuurlijk een "\"

 

[pvag]

Ik heb het sjabloon uitgekleed om alle zo klein mogelijk te houden.
Nogmaals, het bestand moet worden opgeslagen in: G:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\2015
Die map 2015 verandert dus elk jaar.
Al vast bedankt voor de moeite

 

[edmoor]

Nu mis je de wel een \. Deze moet achter sYear + 1
Tevens mis ik in dit document de samestelling van de bestandsnaam.
Als dat wel de bedoeling is dan mis ik de extensie.

Dit is het pad dat er uit komt:
F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\2015-2016

 

[pvag]

In het werkblad 'Param' wordt het path en ook de filname ingevoerd.
Inderdaad wordt de .xltm nergens in opgenomen omdat de extensie wordt gegenereerd als het bestand wordt opgeslagen.
Het hoeft wat mij betreft niets als xltm (dus als sjabloon) te worden opgeslagen. Mag ook als werkmap en macro's.
Sorry ik weet er maar heel weinig van. Alles wat ik doe is gewoon proberen en kijken of het werkt.
Maar met jullie hulp kom ik er wel. Ik probeer natuurlijk wel te begrijpen wat er gebeurd.

Groetjes, Ton

 

[edmoor]

Geen probleem uiteraard, maar is het pad dat ik in #9 liet zien dan correct?
Als dat het pad is moet er dus nog een \ achter en zie ik nergens de naam van het bestand zoals deze moet worden opgeslagen.

 

[pvag]

De naam van het bestand wordt in 'Param' ingevoerd (Inkoop_afzet) en in het script aangevuld "_" en met jaar 2016 - 2016

 

[edmoor]

En m'n eerste vraag uit #11?

 

[pvag]

Ja het path wat je had opgegeven werkte goed, doch met vaste gegevens. Ik heb het toen wat aangepast door path uit 'Param' te halen.
Dit werkt allemaal perfect. Alle mappen worden aangemaakt, incl de map 2015-2016.
Zet ik mijn huidige datum op januari 2016 dan wordt in ' F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet ' de map 2015-2016 aangemaakt.
Dus dat gaat allemaal goed.

 

[edmoor]

Ok dan.

 

[HSV]

Een andere manier.
De cellen c2, c3 en c9 invullen.

Sub hsv()
 CreateObject("shell.application").Namespace("F:\").NewFolder "OOP\ConciergePlus\Excel bestanden\Ink_afzet\" & Year(Date)
 ThisWorkbook.SaveAs "F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\" & Year(Date) & "\" & Sheets("Param").Range("C9").Value & "_" & Format(Sheets("Param").Range("C2"), "yyyy") & " - " & Format(Sheets("Param").Range("C3"), "yyyy"), 53
End Sub

Het bestand wordt opgeslagen als .xltm (53 in de code): wil je de .xlsm extensie, dan 53 veranderen in 52.

 

[snb]

waarom niet simpel ?

Sub M_snb()
  c00="F:\OOP\ConciergePlus\Excel bestanden\Ink_afzet\" & Year(Date)
  mkdir c00
  ThisWorkbook.SavecopyAs c00 & "\" & [Param!C9].Value & "_" & year([Param!C2]) & " - " & year[Param!C3])
End Sub

 

[pvag]

Beste Hsv en snb,

Ik heb de code die Hsv adviseerde (door C2, C3 en C9 in te vullen) gebruikt en dat draaide. Maar de sheet wordt in een vast path opgeslagen, wat dus niet de bedoeling is. Het path in C8 van 'Param' moet worden gebruikt, omdat ik dan niet elke keer het path hoef te veranderen als er iets gewijzigd wordt. De gebruiker moet in C8 het path invoeren waar hij de zaak wil opslaan.

Al vast bedankt.
Ton

 

[snb]

Dan moet je de code aanpassen analoog aan de overige variabele elementen.

 

[pvag]

Wellicht heb ik niet die intelligentie waarover jullie beschikken, snap niet wat je bedoelt en kom er niet uit.