Most applications must work with the Internet (also called the cloud) to keep real-time numbers. You can retrieve data directly from the Internet to make it more useful and convenient to users. For instance, you might need to pull data from a third-party database or scrape data from a web page. You can both read data from the Internet and even upload data to a remote cloud server. In this article, we'll discuss how you can connect to the Internet, read information from a web page, and then write data back to the Internet.
Reading Data from the Internet
You will want to read data from the Internet far more often than you will write data to the Internet. In this section, we'll show you how to open a web page and extract data from it. When working with Excel, you normally want to extract data contained in tables or sections of the page. In our example, we will loop through a table of data to gather its information and place it in the local Excel spreadsheet.
VBA has an Internet Explorer option to download data. The IE object is useful if you know that your users have Internet Explorer installed and you only have a small amount of data to download. The IE object is generally slow and doesn't perform well with large amounts of data. It's also much more difficult to loop through data.
Instead of using the IE object, we will use the "xmlhttp" object, which is also available in VBA. Before we get started, let's first create a shell subroutine. We're using the subroutine name "ScrapeData."
Sub ScrapeData()
End Sub
The next part of our code is the list of variables that we need to work with the scraped data. Let's add the variables to the beginning of the subroutine. If you've noticed, we always add variable declarations to the top of a subroutine. This is standard practice among coders in any language.
Sub ScrapeData()
Dim x As Long, y As Long
Dim page As Object
End Sub
We define two variables in the above subroutine. The x and y variables will be used to loop through the page's data. The page object is used to retrieve the information and reference the web page.
With the variables defined, we now have to create an html object that will be used to pull the data. Let's add the object to our code.
Sub ScrapeData()
Dim x As Long, y As Long
Dim page As Object
Set page = CreateObject("htmlFile")
End Sub
We set the page variable to the htmlFile object. This set command uses the CreateObject function, which is used to create objects that are installed on the local computer. You should not have an issue with this object as it's inherent to Windows and the Excel software. If you have any problems with this code, then you might need to reinstall Excel or even reinstall the Internet Explorer software.
The next part of the code is the section that actually pulls information from the web page. Of course, for this code to work there must be an Internet connection. Not all computers have Internet connections readily available, but it's uncommon now for a computer not to have access to the Internet. Just remember that if there is no Internet connection such as the Internet is down for the user, this code will fail. You can make this subroutine run when the user clicks a button and provide a message box that tells the user that they first need an Internet connection for it to run properly.
We will assume that there is an Internet connection and connect the web and pull a page of information.
Sub ScrapeData()
Dim x As Long, y As Long
Dim page As Object
Set page = CreateObject("htmlFile")
With CreateObject("msxml2.xmlhttp")
.Open "GET", "http://www.google.com", False
.send
page.body.innerhtml = .responsetext
End With
End Sub
The above code adds a lot of processes to the ScrapeData subroutine. The first section uses a With statement that we haven't seen before. The With statement lets you use shorthand for class methods and properties. Since we use the CreateObject code with the With statement, we create an object and allow the rest of the block to just use method and property names instead of typing the variable name again.
Let's take a look at the alternative code for the above block.
With CreateObject("msxml2.xmlhttp")
.Open "GET", "http://www.google.com", False
.send
page.body.innerhtml = .responsetext
End With
Translates to:
Set obj = CreateObject("msxml2.xmlhttp")
obj.Open "GET", "http://www.google.com", False
obj.send
page.body.innerhtml = .responsetext
The first example is the shorthand example and the second section is the same code except it uses the full syntax for the obj variable. The With statement can be used in any of your subroutines to reduce the amount of code you need to type.
Let's return to the original code.
Sub ScrapeData()
Dim x As Long, y As Long
Dim page As Object
Set page = CreateObject("htmlFile")
With CreateObject("msxml2.xmlhttp")
.Open "GET", "http://www.google.com", False
.send
page.body.innerhtml = .responsetext
End With
End Sub
We create an object that is used to query the web. The Open method opens the page. Notice that there are three parameters. The GET parameter retrieves the web page. If we were sending data to a web page, then we would use the POST parameter. We'll use this parameter in the next section. We then define the URL that we want to open. We're just using a simple call to Google's home page in this example.
The next statement is simply "Send." This method tells the application to send the command to the web server. Since we're just retrieving a web page, the object opens the web page, grabs the content, and then stores the results in the innerhtml property of the page object. This innerhtml property contains all the HTML found on the opened web page.
With the web page opened and data retrieved, we can now loop through the data and retrieve it. Let's add the loop code that parses the data.
Sub ScrapeData()
Dim x As Long, y As Long
Dim page As Object
Set page = CreateObject("htmlFile")
With CreateObject("msxml2.xmlhttp")
.Open "GET", "http://www.google.com", False
.send
page.body.innerhtml = .responsetext
End With
With page.getelementbyid("element")
For x = 0 To .Rows.Length - 1
For y = 0 To .Rows(x).Cells.Length - 1
Sheets(1).Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innertext
Next y
Next x
End With
End Sub
We added a new loop that goes through each element on the page. We're assuming that there is a table layout in this code. Google's page does not have tables, but you can use this code with any page that uses the table element. You just need the ID of the element. The "getElementById" function retrieves the inner HTML for an element ID named element. When you test this code with another web page, you will need to identify the element ID for it to work. The "Element" ID is used as an example.
The next set of code is a For loop and then an embedded second For loop. Remember with a multidimensional array, we used embedded for loops to work through rows and then each column within the row. The same concept is used here. We work through each table column and then each column row to grab the data within each table cell. We then use the values of x and y to reference and copy the data to the appropriate Excel cell.
You can test this without the For loop. Just copy the innerhtml content to the spreadsheet and you know that the retrieval is working.
Sending Data to the Internet
It's not often that you need to post data to a web page, but VBA provides you with the functionality to send data to a web page. In the previous section, we mentioned that you do this using the POST parameter rather than the GET parameter.
Let's use the same subroutine, only this time we're going to post data to a web page form. Since we are posting to a web page and not retrieving from it, we change the subroutine name to "SendData."
Sub SendData ()
Dim x As Long, y As Long
Dim page As Object
Set page = CreateObject("htmlFile")
With CreateObject("msxml2.xmlhttp")
.Open "POST", "http://www.google.com", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.send
End With
End Sub
In this example, we've only changed two components in the code. The POST parameter is used to send data to a web page and send data to a third-party form. We still use Google.com in this example, but you should change this web page to your own form submission page. This can be any third-party page or your own website.
We set the RequestHeader element to specify the content type. There are several content types when working with cloud requests. Just know that this content type is specific to form URL submissions, so you'll use it each time you send data to a web page.
Text File Processing
Excel is a desktop application, so you need to work with text files stored on the hard drive or opened from the hard drive. You can export data from your Excel spreadsheet to an external text file that you store on the hard drive. You can also open a text file and use the data to parse and store in the Excel spreadsheet. This section shows you how to create and edit text files that you use in your applications. Just remember that the user running the application must have access and permissions to the hard drive directory to perform these actions.
Creating a Text File
In most cases, you'll need to copy data from a spreadsheet and create a file with the data you extract. To do this, you export the data to a text file and store it on the user's local hard drive. VBA has internal methods and properties that can perform all of the technical aspects of creating and storing a file on the hard drive. You just need to specify a location and the data you want to use.
Again, we need our own subroutine for this functionality. Let's create a subroutine named CreateFile.
Sub CreateFile()
End Sub
Now, we need to create the variables needed to work with the text file functions. We need the name of the file and an integer value that indicates the number for the workbook. Unless you are working with multiple workbooks, this number is usually 1.
Let's add the variable code.
Sub CreateFile()
Dim text As Integer
Dim path As String
End Sub
We created two variables. The first text variable will indicate the number for the file pointer. The path is a string variable that will be used to point to the file's directory.
We now need to populate these variables with the right values.
Sub CreateFile()
Dim text As Integer
Dim path As String
path = "C:\file.txt"
text = 1
End Sub
We added values to the variables in the code above. The path variable now contains the location for the text file, and the text variable contains the number for the pointer.
The next step is to create and open the file. Once it's opened, we can write data to it. The following code creates and opens the file.
Sub CreateFile()
Dim text As Integer
Dim path As String
path = "C:\file.txt"
text = 1
Open path For Output As text
End Sub
The new line of code opens the file in the path variable and then assigns it to a pointer. Since we set the pointer to 1, the pointer is 1 for the time the file is opened until you close it. If you open several files, the pointer should increment by 1 and the next pointer is assigned to the next file.
Now, we can write some data to the file to populate it before we close it. Let's write some basic message to the file content.
Sub CreateFile()
Dim text As Integer
Dim path As String
path = "C:\file.txt"
text = 1
Open path For Output As text
Print #text, "Hello World"
End Sub
Notice that the file pointer is prefixed with a hash character. This statement points to the file and tells VB to write data to the file with the pointer value in the text variable.
Once you create the file, write data to it, and you're finished with it, you need to close it. The Close function closes the file. If you leave the file open, the operating system keeps a lock on it, so always make sure you close the file when you're finished with it. Let's take a look at the code.
Sub CreateFile()
Dim text As Integer
Dim path As String
path = "C:\file.txt"
text = 1
Open path For Output As text
Print #text, "Hello World"
Close text
End Sub
The final line of code closes the file, which releases the lock on it.
Edit a File
Once you know how to write to a file, it's time to understand what you need to do to edit it. Chances are that you'll need to open and edit a file that's later used in other applications or by other users. In this section, we will show you how to edit a file.
Sub EditFile()
Dim text As Integer
Dim path As String
Dim content As String
path = "C:\file.txt"
text = 1
Open path For Output As text
Close text
End Sub
We first added a new variable that will contain the file's data. Notice that the only part of the code that we removed was the part that wrote content to the file. We want to work with this function a bit differently. We want to open the file, find data in the file, and then replace that data with some new data.
Before we can edit the content, we need to retrieve the file content and then store it in a variable. It's only one line of code to complete this process. Take a look at the following code.
Sub EditFile()
Dim text As Integer
Dim path As String
Dim content As String
path = "C:\file.txt"
text = 1
Open path For Output As text
content = Input(LOF(text), text)
Close text
End Sub
In the above code, we read all of the contents of a file and store it in the content variable. Once all the data is stored in the content variable, we can now edit it. We need to add a line of code that edits the content. Since we added Hello World to the file when we created it, we can change the content. Let's change the text "Hello" to the term "Goodbye."
Sub EditFile()
Dim text As Integer
Dim path As String
Dim content As String
path = "C:\file.txt"
text = 1
Open path For Input As text
content = Input(LOF(text), text)
content = Replace(content, "Hello", "Goodbye")
Close text
End Sub
We used the Replace function to change the text Hello to Goodbye. The Replace function is an internal Visual Basic function that replaces the text in the second parameter with the text in the third parameter.
With the content replaced, we can now write the content to it. We opened the file for input, so first we need to close it and reopen it again. The following code replaces the file content with the new content that we created.
Sub EditFile()
Dim text As Integer
Dim path As String
Dim content As String
path = "C:\file.txt"
text = 1
Open path For Input As text
content = Input(LOF(text), text)
content = Replace(content, "Hello", "Goodbye")
Close text
End Sub
With the file content set up, we can now write it to the file. We closed the file, but we now need it opened again. We need to use the file open process again to open the file before we write the new content to it. Let's add this code to it.
Sub EditFile()
Dim text As Integer
Dim path As String
Dim content As String
path = "C:\file.txt"
text = 1
Open path For Input As text
content = Input(LOF(text), text)
content = Replace(content, "Hello", "Goodbye")
Close text
text = 1
Open path For Output As text
End Sub
Notice that we set the path during the open process to output. We first used input when we wanted to read from the file. When we read, we need to set it to input. When we write to a file, we need to use the output statement. We also use the file pointer to indicate to the operating system that it can find the file at the specific pointer, which we set to 1.
Next, we need to print the new content to the file. Let's take a look at the file.
Sub EditFile()
Dim text As Integer
Dim path As String
Dim content As String
path = "C:\file.txt"
text = 1
Open path For Input As text
content = Input(LOF(text), text)
content = Replace(content, "Hello", "Goodbye")
Close text
text = 1
Open path For Output As text
Print # text, content
End Sub
In the code above, we open the file one more time to write the new content to it. We've changed the content from "Hello World" to "Goodbye World." This new text is sent to the file. Because we open the file for output, we can now write content to it. Notice that the same Print statement is used with the file pointer and the content we want to write to the file.
Finally, we need to close the file again. The following code is the final version for our working code.
Sub EditFile()
Dim text As Integer
Dim path As String
Dim content As String
path = "C:\file.txt"
text = 1
Open path For Input As text
content = Input(LOF(text), text)
content = Replace(content, "Hello", "Goodbye")
Close text
text = 1
Open path For Output As text
Print # text, content
Close text
End Sub
The Close function uses the file pointer again to close the file.
Working with files isn't difficult since Visual Basic includes several predefined functions and variables that help you work with them. You just need to define if you want to open the file for input or output, and then open, create and save the data from your Excel spreadsheets.
