View original (French)

Vba => Bat

Solved
zapp56 -  
 zapp56 -
Hello,

I have been searching for a few hours and I still haven't found an answer to this question, which many must have already asked...

I have a text variable in a textbox in an Excel userform that contains the path of a directory: "c:\Docs\Bdd"

I want certain users to be able to set this path. A batch file takes care of all the folder creation formalities.

The batch file is called "run.bat"

My macro will call the execution of the batch. I would like it to also send the variable containing the access path so that we have an auto-modifiable batch.

Could you please help me?

Best regards.

4 answers

  1.
    pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,

    The easiest and most practical way to create a directory and all its subdirectories only when necessary (if it does not exist): 
    Private Declare Function SHCreateDirectoryEx Lib "shell32" Alias "SHCreateDirectoryExA" (ByVal hwnd As Long, ByVal pszPath As String, ByVal psa As Any) As Long Public Const myDir$ = "C:\Docs\Bdd\d1\d2\d3\" Sub Create_Directory() 'sources : UcFoutu : 'http://www.developpez.net/forums/d613347/logiciels/microsoft-office/excel/macros-vba-excel/tester-repertoire-existe-creer-non/ SHCreateDirectoryEx 0, myDir, ByVal 0& End Sub


    To be adapted, of course. The constant myDir should be a variable if you want it to be modified by the user.
    --
    🎼 Best regards,
    Franck 🎶
    3
    1. zapp56
       
      THANK YOU !!!!!

      A huge thank you, it's exactly what I was looking for. This code works perfectly!
      0
    2. zapp56
       
      Out of curiosity... could you explain the first line to me? I mean, how does it work, what are its arguments, what are "hwnd," "pszPath," "psa"...
      Thank you =)
      0
      1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773 > zapp56
         
        Good evening,

        Having been away for several days, I'm taking just 5 minutes to respond to you.

        Without going into detail, look up (if you don't know yet) what Windows APIs are.
        Basically, these are functions provided to everyone by Microsoft. They are developed in DLL files.
        Here, we will use one of the functions contained in the shell32.dll file. Do a search on your computer, it has it.
        This SHCreateDirectoryEx function is specifically there to serve the purpose you have today, which is to create a directory when needed.
        You declare it in VBA in the module header. (This isn't unlike declaring functions in C language...)
        Once declared in the module header, you can freely use it in your module, or in your entire project if you declared it as Public.

        More info, especially regarding its parameters...
        A++
        0
    3. zapp56
       
      Ok! Thanks again for your help!
      0
  2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello,

    When you run your batch, just add the file path:
    run.bat path
    and in your batch, it will be the first parameter %1
    --
    Always zen
    Perfection is achieved not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
    0
    1. zapp56
       
      Thank you for your response, but I did not understand :(
      For clarity:
      "Run.bat" is my batch file, in the folder "c:\Docs\"
      REPERT = "c:\Docs\Bdd\" (as string) (modifiable in the textbox of the usf)

      When I call: 
      shell("c:\Docs\Run.bat", AppWinStyle.NormalFocus)

      I would like it to send the variable REPERT to my file "Run.bat".
      Thus, the batch would contain the command line: 
      MD %REPERT%


      Am I clearer?
      0
  3. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
     
    Hi,

    in VBA, we can execute file commands, why don't you use this feature? 

    MkDir "directory"


    --
    See you, blux 
     "Fools dare to do anything.
    That's how you recognize them."
    0
    1. zapp56
       
      Hello, blux, and thank you for your response.
      The problem with "MkDir" is that the path cannot contain more than one single folder.
      I know there is a procedure to create certain folders, but I didn't understand it, so I opted for a batch file instead.
      Moreover, there is a possibility that my batch file will be useful to me later if I can send it a variable.

      Or do you have a simple way to create folders according to a variable path in VBA, in a straightforward procedure... I remind you that the extraction of the path is done from a textbox on the user form.
      0
      1. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > zapp56
         
        Everything is possible (or almost) via VBA when it comes to file management...
        What do you mean by limit of a single folder?
        0
    2. zapp56
       
      If I do: 
      MkDir ("c:\Docs\")

      it will indeed create a folder in the C: directory.
      But if I do: 
      Mkdir ("c:\Docs\Bdd")

      it will not create a "Docs" folder and then a "Bdd" folder.
      What I want is for the access path to be completely variable.

      In a batch file, if I do
      MD C:\Docs\Bdd\d1\d2\d3
      it will create all the folders, subfolders, etc., of the access path.
      0
      1. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > zapp56
         
        It's up to you to manage it via a loop...
        0
    3. zapp56
       
      Alright, I'll stick to VB then ;)

      Thank you.
      0
  4. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello,

    I would like her to also send him the variable containing the path
    With 
    shell("c:\Docs\Run.bat " & REPERT & ", AppWinStyle.NormalFocus)
    , you are indeed sending your access.

    Then in a batch, if you do 
    MD %1
    , it will give 
    MD C:\Docs\Bdd\d1\d2\d3
    if REPERT had the value "C:\Docs\Bdd\d1\d2\d3": which is what you want, it seems to me?
    --
    Always zen
    Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
    0
    1. zapp56
       
      I just tested your code. I have an error: list separator [...]
      I tried without :&REPERT&"
      Same thing.
      0
    2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      It's certain that if you put that, &REPERT&, it won't work. There was an extra quote: 
      shell("c:\Docs\Run.bat " & REPERT & ,AppWinStyle.NormalFocus)
      and you need the space.
      0
    3. zapp56
       
      Alright, I'll test that tomorrow. Thanks everyone.
      0