Excel and the World Wide Web Straight to the Point

By Eduardo N Sanchez

If you have an Excel workbook that needs to regularly harvest data from a web page, this book is for you. The book covers various methods for getting data from the web, from VBA to Selenium to Power Query. Addresses the complexities of getting data from the Modern Web and the lack of VBA support in MIcrosoft Edge.

• Language: English
• Publisher: Independent Publishers Group
• Release date: Feb 1, 2021
• ISBN: 9781615471584

Book preview

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 Web

Straight 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