Excel and the World Wide Web Straight to the Point E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 44

Veröffentlichungsjahr: 2024

Beschreibung

This book is a comprehensive guide for integrating web data into Excel, ideal for both beginners and experienced users. It begins by introducing essential concepts like HTML and JSON, laying the foundation for web data extraction. Readers start by performing static and dynamic web queries directly in Excel, building confidence in handling foundational tasks.
As the journey continues, the book explores advanced automation using VBA and Selenium. Readers learn to interact with web elements, handle authenticated requests, and navigate modern challenges like working with iframes, downloading files, and creating PDFs. Key techniques, including using XPath and CSS selectors, are explained to streamline data extraction. Chapters also address overcoming VBA’s limitations with modern browsers like Microsoft Edge, providing practical solutions.
In the final chapters, Power Query is introduced as a powerful tool for connecting and transforming web data. Readers explore both direct and advanced methods to integrate Excel with web sources efficiently. By the end, readers will master practical workflows, combining VBA, Selenium, and Power Query to automate and simplify web data extraction in Excel.

Leseprobe

Straight to the Point

The Straight to the Point e-books are designed to thoroughly cover one targeted aspect of Excel.

Excel and

The World Wide WebStraight to the Point

Eduardo Sanchez

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Excel and the World Wide Web Straight to the Point

© 2021 by Tickling Keys, Inc. and Eduardo N. Sanchez

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

First Published: January 2021

Author: Eduardo Sanchez

Cover Design: Suat M. Ozgur

Cover Illustration: Shannon Mattiza, 6'4 Productions

Indexer: Nellie Jay

Compositor: Jill Cabot

Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-158-4

Table of Contents

About the Author

Acknowledgments

Code used in this book

Getting Started

What is HTML?

Performing Queries

A static query

A dynamic web query

Querying with VBA

Interacting with Sites without using a Browser

What is JSON?

Getting data from the Web programmatically

Getting data from Web page elements

Posting data over the Internet

Authenticated requests

Internet Explorer and VBA

Transferring a Web table to the worksheet

Working with events

Introducing Selenium

Installing Selenium

Internet Explorer and Selenium

Google Chrome and VBA

Running JavaScript on a page

Using XPath to find elements

Microsoft Edge and VBA

Finding elements by CSS selectors

Downloading a file

Creating a PDF file from scratch

Wrapping up Selenium

Power Query and the Web

Connecting to a Web table – the direct way

Connecting to a Web table – the long way

Conclusion

Index

This page intentionally left blank. The Introduction starts on the next page.

About the Author

Eduardo N. Sanchez is a Brazilian Chemical Engineer with a long-time penchant for computers. Since college he has been programming using languages such as Fortran, Basic, Algol, Pascal, C and most recently VBA.

Eduardo enjoys answering Excel and Office questions at Internet forums, as this is a great way to get in touch with topics that could otherwise remain unnoticed. When he is not tackling a computational challenge with electronic music in the background, Eduardo likes to watch Star Trek episodes and play with the family’s Dachshund.

Acknowledgments

I want to thank Bill Jelen for believing in this project when he knew nothing at all about me. Thanks also to all blog posters and Internet site owners who share valuable technical knowledge for free; even the most experienced user will benefit from reading well-constructed articles. After retirement I plan to build my own little Excel corner online…

Finally, thanks to my family for being always supportive on all my endeavors, and to my co-workers and Web posters who keep me on my toes by asking Excel questions of varying difficulty levels.

Code used in this book

The code examples are available for download at https://www.mrexcel.com/download-center/excel-and-the-world-wide-web-8051/

Getting Started

In the good old days, when personal computers had no hard drives and monitors were low resolution and monochromatic, there was no Internet. Then it started, shyly at first, and that was a time when people were viewing sites with Netscape and searching with Altavista.

Nowadays, on the other hand, the World Wide Web is everywhere, and it is hard to find an electronic device that is not connect to it in one way or another. The aim of this book is to provide you with tools that will automate repetitive tasks and boost your productivity when working with Excel and the Web.

Most of the techniques shown here can be found online, scattered all over the Web. Here you will find them logically organized and all in one place.

What is HTML?

HTML stands for Hyper Text Markup Language and is used to build websites. Have you ever wondered what hypertext is? It is a content that behaves in a non-linear way. Imagine a web site where each page has several links to other pages, both on the same site or not. The user will navigate by jumping from one page to another; this is hypertext behavior.

A conventional print book is the counter example, it is supposed to be read sequentially.

The markup part stems from the fact that information in HTML is marked with tags; we will see more on this shortly.

There are other languages used to help create websites, such as CSS and Java Script, but we are not going to deal with them in detail.

Note: