VBA - manage files and connect MS Office Applications

VBA - manage files and connect MS Office Applications

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

• VBA types

• Advanced Data Structures in VBA

• VBA - Strings

• VBA - Date and Time

• VBA - Arrays

• VBA Excel Objects

• VBA Classes

• VBA files

• 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

Manage text and binary files with VBA, exchange data, formats, objects and information between MS Office Applications

Url: View Details

What you will learn

• Their knowledge in Excel VBA will improve
• Manage external text files with VBA
• Manage binary files with Excel

Rating: 4.05

Level: All Levels

Duration: 12 hours

Instructor: Daniele Protti