VBA - manage files and connect MS Office Applications
What you'll learn
- Their knowledge in Excel VBA will improve
- Manage external text files with VBA
- Manage binary files with Excel
- Learn and Understand the FileSystemObject class to manage files and folders
- Transfer information between MS Office Applications: Excel - Word
- Transfer information between MS Office Applications: Excel - PowerPoint
- Transfer information between MS Office Applications: Excel - Access
- Retrieve information and attributes of files
- Manage media data with VBA
- Organize folders and data structures with VBA
- Basic knowledge of Microsoft Excel
- Basic knowledge of Excel VBA
In this course you will learn how to manage files and how to exchange data between files and between different Microsoft Office applications using VBA.
We will start giving some fundamental information e.g.
VBA files and folder functions
VBA files and folder attributes
VBA data structures
Advanced Data Structures in VBA
VBA - Strings
VBA - Date and Time
VBA - Arrays
VBA Excel Objects
Functions needed to write files in VBA
The StringBuilder class
Some important information from the Application object (RecentFiles, defaultPath)
This information is the basis to continue in this course.
In details we will then learn:
VBA and text files: how to read and write to text files, how to modify a text file, how to append text to an existing text file, how to load an Array variable with data from a delimited text file. We will also see how to read and write to CSV files in VBA
VBA and binary files: how to read and write to binary files, how to read from binary file and import into Excel, how to create a Binary File from Text File and Read to Excel, how to append to binary file
File Converters in VBA: VBA – Convert File Formats, how to convert CSV to Excel with VBA, how to convert Excel to CSV with VBA, Excel to PDF exporter, how to convert html to text file, how to convert text file into excel, how to convert html to PDF
Connect MS Office applications: Controlling One Microsoft Office Application from Another, copy data from Excel to Powerpoint, activating Other Applications with Excel VBA, Using VBA to Paste from Excel to Word and PowerPoint, Controlling Powerpoint and Word from Excel using VBA, Export the text of a presentation to a CSV/Excel file, export Word tables to Excel sheets, Import/Export to Access from Excel, update Access with Excel data with VBA
Folders and File Handling in Excel VBA: Check if Folder Exists, opening, moving, deleting and creating folders using VBA Excel, making File Read Only in VBA Excel, copy all Excel Files from One Folder to Another in VBA Excel
dir function: iterate though all the folders inside a path (immediate child folders only), iterate though all the files present at a location. (No need to list files under subfolders), list all the files inside a current location and its subfolder, search files
Excel VBA File Management Using The FileSytemObject
All the methods, copy Files With a Specific File Type, copy All Excel File Type, organize Files Based on File Type, organize Files Based on File Name, retrieve file information, read and write from/to text and binary files using FileSystemObject in VBA, rename, move and copy files, delete a file
File Dialog: Opening /Saving Files Using File Dialog Box in Excel VBA, customize File or Folder Dialog Box in VBA Excel, filters of Dialogs, the Dialogs collection, the Dialog object
OneDrive and VBA: What is OneDrive, Excel's fullname property with OneDrive, VBA download a File from OneDrive, SaveAs in OneDrive
Macros disabled when opening workbooks in OneDrive, Select a Workbook from OneDrive, How do I open files saved in Microsoft Onedrive using vba
VBA and JSON files: JSON file data into a VBA Dictionary, The JSON VBA libraries already available and ready to use
Import/Export JSON to/from Excel, export Excel to Nested JSON
VBA and XML files: reading an XML file with XPath in VBA, save to XML file, reading XML file in VBA ((XML structure, XML DOM nodes, XML file)
VBA and HTML files: reading an HTML file, create HTML Tables with Excel VBA, export Excel to HTML, reating HTML using a Builder Pattern from Excel Table in VBA, using Excel Cell StyleElements to Style HTML Document
ADODB Stream: ADODB Streams to read and write files, convert ADODB binary stream to string vba, Save and read Binary and Text Data, use the ADO recordset, record and stream objects to open documents
VBA and String Encoding: Unicode Strings and the Windows API, save text file UTF8 without BOM encoded, read/write UTF-8 files with VBA, Convert UTF-8 to ANSI , ANSI-String in UTF8-Format, ISO-Text in UTF8-Format, ISO-8859-1 String encoding, VBA XML and encoding, write to UTF-8 text file with Excel VBA
Remote files and VBA: WinHttpRequest (XMLHTTP, ServerXMLHTTP) and remote files
Data Migration: Data Import/Export in Excel, automation to transfer data cell by cell, to Transfer an Array of Data to a Range in a Worksheet, to transfer an ADO Recordset to a Worksheet Area, to create a Query Table in a Worksheet, use the Clipboard to transfer data in VBA, transferring data to a worksheet with ADO, import text file into Excel with Querytables
Manage compressed files in VBA: compress a file, unzip File Through Excel VBA Code, create a zip file from a folder, Unzip a zip file to a folder, create Zip Files with VBA using 7-Zip, create a ZIP archive with the Windows Shell, zip and encrypt a directory from VBA, save excel file to zip file, compress/extract files with WinRar
FTP and VBA for file exchange with remote servers: use FTP in VBA, Download / Upload File using VBA and FTP, Asynchronous File Downloads using VBA, alternative methods to Upload file via FTP from Excel VBA, FTP a text file to a server using VBA in Excel, use VBA to export CSV from webpage
Who this course is for:
- Who already knows how to use Excel and create VBA macros
- Who needs to import/export data between MS Office applications or import/export data in external files
I have 20+ years experience in IT project management, software development and software architecture.
I have led several teams of software developers as project manager, quality manager and as team leader in different companies and different countries in sectors e.g. telecommunication, research & development, travel business, Internet marketing and Internet business, consultancy and services.
I have been responsible to collect the requirements of projects, applications and processes, design the databases and specify the final objects.
During my career I have accumulated a lot of experiences dealing with processes of any kind and the need to define the tools to manage them and collect the information.
I have organized all my experiences in courses which are not only a list of functions and methods but contain also my practical experiences and considerations about how to cope with the different situations and which solutions to suggest.