Working With Folders using VB Script - Rating: Advanced

  • Hi All,

    This ones probably for the slightly more advanced user.

    Ever had to move / copy / replace folders and wanted it to happen external to Excel so as not to freeze up what your working on?? & as an extra bonus include a progress indicator ??

    Well check this little trick I picked up along the way using Visual Basic Scripting.

    Now for anyone not sure what VBS is..... well it's essentially the same as VBA but designed to run in the Windows environment whereas VBA runs in an application environment.

    What to do:

    1. Make a *.VBS file (using the attached as a template) that defines what files you want to go where. The attached file can be edited with a right mouse click > edit.

    The code in the attached file reads:

    'The destination folder
    ParentFolder = "C:\Marketing"
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.NameSpace(ParentFolder)
    'The source folder
    objFolder.CopyHere "P:\Marketing\Quote Package", FOF_CREATEPROGRESSDLG

    As you can see it's fairly straight forward you spec the source folder to copy and destination folder to replace.

    2. Using VBA code you can call the *.VBS file to execute:

    Sub AutoUpdateQuotePackUpdateMsg()
    Shell "wscript C:\Windows\UpdatePackage.vbs", vbNormalFocus
    End Sub

    What happens ??

    When you call the *.VBS file to execute through VBA it runs the code. This causes Windows to prompt you to copy the folder.

    The copying takes place in Windows rather than freezing up Excel while folders are moved around.

    Anyways I think it's cool.

    BTW: There is a lot more VBS code that you can use to do a range of things in Windows. Using VBS from within a VBA enhanced application can dramatically reduce the drain on that applications resources as you passing the workload over to Windows. There's a vast range of VBS code on the WEB, just do a search.

    WARNING: VBS code can be and is used as a virus carrier so be carefull of what you download. Also be carefull of what you write as it can do a lot of harm to your system if you get it wrong.

    Other than that...........enjoy!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!