Training videos for learning VBA - WORKING WITH FILES

We have 12 VBA - working with files videos listed below. You might also like to consider booking a place on one of our online or classroom VBA courses, where you will meet the owls behind the videos!

Excel VBA Part 22 - Files and Folders

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.

Excel VBA Part 23 - Text Files

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.

Excel VBA Part 24 - File Dialogs

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.

Excel VBA - Save as PDF

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.

How do I copy sheets from multiple Excel files into one workbook in VBA?

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.

How do I export multiple PDF files from Excel when looping over cells?

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.

How do I copy sheets from multiple Excel files into one worksheet?

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.

How do I find a folder with a partial name in VBA?

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.

How do I loop through folders and subfolders in Excel VBA?

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.

How do I copy data from multiple worksheets in other workbooks?

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.

How do I unzip files using VBA?

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.

How do I list all properties of a file using VBA?

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.

This page has 0 threads Add post