|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/vba-manage-files-and-connect-ms-office-applications/
课程评论：没有评论
本课程旨在教授如何使用VBA（Visual Basic for Applications）管理文件，以及在Microsoft Office应用程序之间交换数据。 **核心内容概览：** * **基础知识：** 涵盖VBA文件和文件夹功能、属性、数据结构、常用数据类型、高级数据结构、字符串处理、日期时间操作、数组、Excel对象、类、文件读写函数、StringBuilder类，以及Application对象的关键属性（如RecentFiles、defaultPath）。 * **文本文件操作：** 学习读写、修改、追加文本文件，将文件数据加载到数组，以及CSV文件的导入导出。 * **二进制文件操作：** 掌握读写二进制文件，将二进制文件导入Excel，从文本文件创建二进制文件并读取，以及追加到二进制文件。 * **文件格式转换：** 学习各种文件格式之间的转换，包括CSV转Excel、Excel转CSV、Excel转PDF、HTML转文本、文本转Excel、HTML转PDF。 * **Office应用程序互联：** 学习如何控制一个Office应用程序，实现Excel与PowerPoint、Word、Access之间的数据交换，如复制数据、激活其他应用、导出演示文稿文本、导出Word表格到Excel、导入导出Access数据等。 * **文件夹与文件处理：** 深入学习检查文件夹是否存在、创建、打开、移动、删除文件夹，设置文件只读，复制指定类型文件，按类型或名称组织文件，以及使用FileSystemObject进行文件管理（包括重命名、移动、复制、删除、读写文本/二进制文件）。 * **文件对话框：** 学习使用文件对话框进行文件的打开和保存，以及自定义对话框、设置过滤器、使用Dialogs集合和Dialog对象。 * **OneDrive集成：** 了解OneDrive，学习使用VBA下载文件、在OneDrive中进行SaveAs操作，以及处理OneDrive中的文件打开问题。 * **JSON文件处理：** 掌握将JSON数据导入VBA Dictionary，使用JSON VBA库，以及JSON与Excel之间的导入导出，包括嵌套JSON的导出。 * **XML文件处理：** 学习使用XPath读取XML文件，保存到XML文件，以及XML文件的结构、DOM节点和解析。 * **HTML文件处理：** 学习读取HTML文件，使用VBA创建HTML表格，将Excel数据导出为HTML，以及使用Excel单元格样式美化HTML文档。 * **ADODB Stream：** 学习使用ADODB Streams进行文件的读写，二进制流与字符串的转换，保存和读取二进制/文本数据，以及使用Recordset、Record和Stream对象。 * **字符串编码：** 学习Unicode字符串和Windows API，UTF-8编码的文本文件读写（包括无BOM），以及各种编码格式（ANSI、UTF-8、ISO-8859-1）之间的转换。 * **远程文件操作：** 学习使用WinHttpRequest（XMLHTTP, ServerXMLHTTP）与远程文件交互。 * **数据迁移：** 学习Excel中的数据导入导出，实现单元格间、数组到范围、Recordset到工作表区域的数据转移，创建查询表，使用剪贴板，通过ADO转移数据，以及使用Querytables导入文本文件。 * **压缩文件管理：** 学习文件压缩、解压缩，创建和管理Zip文件（使用7-Zip、Windows Shell），压缩加密目录，以及压缩保存Excel文件。 * **FTP文件交换：** 学习在VBA中使用FTP进行文件下载、上传，异步下载，以及通过VBA导出CSV文件。 本课程为学习者提供了全面的VBA文件管理和Office应用程序互操作能力的指导。
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 functionsVBA files and folder attributesVBA data structuresVBA typesAdvanced Data Structures in VBAVBA - StringsVBA - Date and TimeVBA - ArraysVBA Excel ObjectsVBA ClassesVBA filesFunctions needed to write files in VBAThe StringBuilder classSome 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 VBAVBA 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 fileFile 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 PDFConnect 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 VBAFolders 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 Exceldir 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 filesExcel VBA File Management Using The FileSytemObjectAll 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 fileFile 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 objectOneDrive and VBA: What is OneDrive, Excel's fullname property with OneDrive, VBA download a File from OneDrive, SaveAs in OneDriveMacros disabled when opening workbooks in OneDrive, Select a Workbook from OneDrive, How do I open files saved in Microsoft Onedrive using vbaVBA and JSON files: JSON file data into a VBA Dictionary, The JSON VBA libraries already available and ready to useImport/Export JSON to/from Excel, export Excel to Nested JSONVBA 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 DocumentADODB 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 documentsVBA 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 VBARemote files and VBA: WinHttpRequest (XMLHTTP, ServerXMLHTTP) and remote filesData 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 QuerytablesManage 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 WinRarFTP 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
