How to Use the Shell Automation Library in VBA | Part 1

The Microsoft Shells Control library makes working with system operations a breeze. With this library, we can execute a wide variety of scripts, grab system settings and information, and grab system folders. In this tutorial, we will explore some of the fundamentals of using the Shell library and how to interact with different system settings.
Video Resources:
--------------------------------------------------
Resource: GitHub File
Link: github.com/are...
Resource: Microsoft Shell Folder Constants
Link: docs.microsoft...
Resource: Microsoft Shell Object
Link: docs.microsoft...
Resource: Microsoft Shell Settings
Link: docs.microsoft...
Resource: Microsoft System Info
Link: docs.microsoft...
Resources:
--------------------------------------------------
Facebook Page: / codingsigma
Facebook Group: / sigmacoding
GitHub Sigma Coding: github.com/are...
Support Sigma Coding:
--------------------------------------------------
Patreon: / sigmacoding
Amazon Associates: amzn.to/3bsTI5P **
Related Topics:
--------------------------------------------------
Title: How To Programmatically Add Library References Using VBA
Link: • How To Programmaticall...
Title: How to Work With the Command Bar Object in VBA
Link: • How to Work With the C...
Title: How to Work With the Command Bar Control Object in VBA
Link: • How to Work With the C...
Title: How to Export Macros Using VBA
Link: • How to Export Macros U...
Title: How To Run A Python Script Using Excel VBA
Link: • How To Run A Python Sc...
Title: How to Use the Shell Automation Library in VBA | Part 1
Link: • How to Use the Shell A...
Title: How To Control The Visual Basic Editor With VBA
Link: • How To Control The Vis...
Title: Web Scraping With VBA | Part One
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Part Three
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Part Two
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Scraping Data Tables
Link: • Web Scraping With VBA ...
Title: Making API Requests in VBA | JSON
Link: • Making API Requests in...
Title: Making API Requests in VBA | XML
Link: • Making API Requests in...
Title: How To Pull Data From Access Using VBA
Link: • How To Pull Data From ...
Title: Importing Text Files In VBA
Link: • Importing Text Files I...
Title: How to Use the File System Object in VBA
Link: • How to Use the File Sy...
Title: How to Build Windows Forms Applications in Python | Pt. 2
Link: • How to Schedule a Macr...
**Amazon Associates Disclosure:
--------------------------------------------------
I am a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com. One of the ways I support the channel is by using Amazon Associates to earn fees on purchases you make. That means when you use the link above, it will track any purchases made from that link and give a small portion of it to the Sigma Coding. I love this approach because it allows you to do what you're already doing (shopping) but also helps support the channels you care about. Also, it makes it where I can invest that revenue to help improve and grow the channel.
Tags:
--------------------------------------------------
#Excel #VBA #ShellObject

[@danielkrajnik3817]

wow, this actually looks really useful

[@SigmaCoding]

:)

[@dildokafir5058]

Thanku sigma coding...love you

[@firstcommenter202]

File System Objects can do the folders

[@H-do8tr]

GREAT! How Can I export the first four sheets of the workbook as PDF and export another word file in the same folder then export the rest of sheets. All these exported should be merged in one PDF in that order

[@ganeshs1360]

i have a scenario where i need to write file properties like Tags and Comments to any file type. Is there a way i can do this using Shell32?

[@kabukijoe99]

good lesson

[@marcelitosweb]

Does the ShellExecute method wait until the called script has ended executing to move on with the program? I've been using the default vba shell command and that's an annoying issue I have. Obviously I can put an application.wait after it, but that's just a workaround. Thanks.

[@SigmaCoding]

Unfortunately, it is asynchronous and it will not wait until the script is finishing running before moving on to the next line. The work around is what you mentioned above, making Excel "sleep" for a specified time.

[@SigmaCoding]

I also found a post on Stack Overflow that might work for you, they use the `WScript.Shell` but with that object you can define the `WaitOnReturn` parameter that will make it synchronous. stackoverflow.com/questions/15951837/wait-for-shell-command-to-complete

[@Matt-zp9jg]

Is part two coming soon?

[@evanrudibaugh8772]

Hey: how do you handle namespace with variable inputs? Set objFolder = shellObj.namespace("C:\ExampleFolder") will work fine for me, but if I have something like folderName = "C:\ExampleFolder" Set objFolder = shellObj.namespace(folderName) I end up with errors.

[@dildokafir5058]

Thank you for the beautiful code. There is a requirement like creating folders on daily basis naming them with current date n month. But the issue is everything day I need to collate data in Excel from different sources (may be 50 or so) and flash them to management keeping segregated regional folders n at the same time I need to attach bunch of Excel files which is awkward.. is there any way to zip those files using VBA script before sending them. That will be very helpful to me. I know you are genius you can do it.. Also pardon me if I am expecting more from you.. Thank you in advance

[@KhalilYasser]

Thanks a lot. The link to Github seems to be broken. Can you provide with the code please?

[@SigmaCoding]

Here is the link to the file: github.com/areed1192/sigma_coding_youtube/tree/master/vba/advanced-vba/scripting-automation-library FYI, I change the organization of the folders sometimes so I can't guarantee the link always works. Normally I just direct people to the main GitHub page and I organize it in such a way that usually it's easy to find. Some videos though I never posted the code, but those are very limited cases.

[@dildokafir5058]

Hi, is there a way to create zip folder using vba. .i need to have many excel files in them

[@SigmaCoding]

Sorry for getting back so late, but I saw a lot of answers online that were just ugly and didn't seem right. I did a little research and realized we can’t create a zip file from the command line, but we can if we use PowerShell. In fact, if we use PowerShell we can dramatically reduce the number of lines of code we have to write because there are built in command line arguments we can call instead. Here is the code: Sub ZipFiles() Dim ShellObj As Shell32.Shell Dim TargetFolder As Shell32.Folder Dim DestinationFolder As Shell32.Folder Dim DesktopName As Shell32.Folder 'Define the desktop DesktopPath = "C:\Users\305197\Desktop" 'Define the folder that contains the objects you want to zip TargetPath = "C:\Users\305197\Desktop\Target" 'Define the Destination folder path, this is the zipped file DestinationPath = "C:\Users\305197\Desktop\Destination" 'Declare a new shell object Set ShellObj = New Shell32.Shell 'Create a new folder on the desktop, to contain all the items we want to compress Set DesktopName = ShellObj.Namespace(DesktopPath) 'Define the folder we are copying the items from. Set TargetFolder = ShellObj.Namespace(TargetPath) 'Make the new folder. DesktopName.NewFolder bName:=DestinationPath 'Copy each item to the destination folder. For Each fldrItem In TargetFolder.Items ShellObj.Namespace(DestinationPath).CopyHere vItem:=fldrItem Next 'If we use power shell we can use command line arguments args = "Compress-Archive -Path " + DestinationPath + " -DestinationPath " + DestinationPath + ".zip" 'Call PowerShell and pass through the arguments. ShellObj.ShellExecute File:="Powershell", vArgs:=args End Sub

[@dildokafir5058]

Thank you. I will use this command

[@dildokafir5058]

@@SigmaCoding hello i tried your powershell code its not working.. pls upload a video on this topic.

[@dildokafir5058]

@@SigmaCoding the moment i execute the line [DesktopName.NewFolder bName:=destinationpath] it throws an error [runtime error '-2147467259 (80004005)'] . As soon as i comment out this line of command and then run it runs actually & at the end a power shell window with blue background screen appears & some line of code runs in a flash and then it disappears.. i dont know to which location zipped folders are saved.. pls help..

[@SigmaCoding]

@@dildokafir5058 Can you paste a copy of your code here?

[@lautarob]

Thank you! Sorry, but the link is broke. Further, the content for the .py file is needed.

[@SigmaCoding]

Here is the link: github.com/areed1192/sigma_coding_youtube/tree/master/vba/advanced-vba/scripting-automation-library Sadly I don't have the python file anymore but it's easy to create one for a test case.

[@lautarob]

@@SigmaCoding Thank you!