Posted by
Andrew Gould
on 03 March 2014
The Scripting Runtime Object Library allows you to easily write code in an Excel VBA project which can manipulate the file and folder structure of your computer. It's and incredibly useful, although potentially quite dangerous thing to be able to do and this video will show you how. You'll learn how to reference the Scripting Runtime Library, what a FileSystemObject is and how to use it and how to perform various methods such as create folders, copy and move files and even how to delete them. Towards the end of the video you'll see how to loop over a collection of files in a single folder and then, as an encore, how you can loop through the complete set of folders and subfolders from a given starting point.
Posted by
Andrew Gould
on 10 March 2014
The Scripting Runtime Object Library allows you to write VBA code to create, open, read from and write to text files using very simple methods. This video teaches you how to work with tab-delimited and comma-separated value files including how to create them, append data to them and how to read from them. The video also includes a section on using text functions for parsing the contents of a text file and the final section talks about combining these techniques with workbook events to create a simple change log.
Posted by
Andrew Gould
on 11 March 2014
Using FileDialogs in VBA provides a convenient method for selecting files and folders and performing useful actions on them. This video covers how to use the standard FileOpen and SaveAs dialogs, and how to perform their default actions. The video also covers the use of the FilePicker and FolderPicker dialogs and how to use FileSystemObjects to process a collection of selected files to perform actions such as copying files to another folder.
Posted by
Andrew Gould
on 26 November 2015
This video explains how to use the ExportAsFixedFormat method to save various elements of an Excel file as a PDF document. You can apply this technique to and entire workbook, single worksheets and charts and even to a specific range of cells.
Posted by
Andrew Gould
on 03 April 2021
This video explains how to use VBA to loop through a collection of Excel files in a folder, opening each one, looping through the collection of worksheets and copying each one into a new workbook. You'll learn how to use file system objects to work with the file and folder structure of your computer.
Posted by
Andrew Gould
on 03 April 2021
The video explains how to use the ExportAsFixedFormat method in Excel VBA to create a PDF file for each row of a table. You'll learn how to loop through a collection of cells, copy input values into a calculation model and export the report sheet as a PDF document. You'll also learn how to check if a folder exists and create it if not using the Dir and MkDir VBA methods.
Posted by
Andrew Gould
on 03 April 2021
Learn how to import multiple Excel files into a single worksheet using VBA. You'll learn how to use the Dir function to loop through the files in a folder, open the files as Excel workbooks, copy the data from a worksheet and paste it to the bottom of a list.
Posted by
Andrew Gould
on 03 April 2021
Learn how to use the Dir function in Excel VBA to find a folder with a partial name using wildcard characters. You'll see how to loop through all the matching folders and copy files from each one into a new folder. We'll also compare the Dir function with similar techniques using the FileSystemObject class from the Microsoft Scripting Runtime library.
Posted by
Andrew Gould
on 06 April 2021
Learn how to write a recursive subroutine in Excel VBA to loop through all the folders and subfolders from any starting folder. You'll use the Microsoft Scripting Runtime library to work with FileSystemObjects and loop through the Files and Subfolders collections of a Folder object. As a bonus you'll learn how to insert pictures into an Excel worksheet and resize and position these into a neat arrangement.
Posted by
Andrew Gould
on 07 April 2021
This video explains how to loop through the Excel files in a folder, opening each one in turn then looping through the collection of worksheets in the workbook and copying the data from each worksheet into a master list in another workbook.
Posted by
Andrew Gould
on 18 August 2021
This video shows you how to use the Windows Shell object to unzip a folder with VBA. You'll learn how to reference the Microsoft Shell and Automation Controls library and create a new instance of the Shell class. You'll see how to reference folders using the Namespace method, how to apply the CopyHere method to a destination folder and use the Items method to return the contents of a zipped folder. You'll also see how to achieve the same results using either early-binding or late-binding techniques.
Posted by
Andrew Gould
on 03 October 2021
This video shows you how to use a Shell object in VBA to loop through the files in a folder and write all the file properrties to a worksheet using the GetDetailsOf method.
