SQL Database Access Using FoxPro Database, Visual Basic Frontend and ADO

I learned about databases today and how to work with them. It took me a long time to run through different code to figure out how to open, view and work with an existing database. For my purposes, I was working with a *.dbf database file, created in FoxPro. I am using visual basic to access this database. The following code shows how you can connect to a database, send an SQL command to read all the fields within the database, and then two different ways to print those fields elsewhere.

Sub Whatever()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
con.ConnectionString = "Provider=vfpoledb;" & _
           "Data Source=C:\ab\dname.dbf;" & _
           "Mode=Read|Share Deny None;" & _
           "Password=''"

con.Open

  With cmd
    .ActiveConnection = con
    .CommandText = "SELECT * FROM name;"
    .CommandType = adCmdText
  End With

  With rst
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd
  End With

ActiveDocument.ActiveWindow.Selection.InsertAfter _
Trim(rst.GetString(2, 1, ",", Chr(13), ""))

For Each x In rst.Fields
ActiveDocument.ActiveWindow.Selection.InsertAfter _
Trim(x.Name) & "=" & Trim(x.Value)
Next


con.Close
Set con = Nothing
Set cmd = Nothing
Set rst = Nothing
End Sub

Now, this code was created with the displaying of only one row of information in mind. I wrote it with a more complex SQL command which should only return one row of information (searching for a row by number). This code will then write out all the information in that row. The first command has many options, especially when taking the data from your sql command result and formatting it into its own table. It has options for column delimiters, row delimiters, how many rows to read, where to start, what to do with null characters, etc. It does not, however, deal well with extra spaces. The second command, however, handles the extra characters well with a Trim() command. It simply asks how many items are in our results list and then loops through each one, printing out the name and the value for that particular field.

Important things to know: The Connection String is going to be the one thing that EVERYONE has to customize. It contains information about the type of database you are connecting to as well as the filename of that database. For example, my "provider" is foxpro (which, by the way, requires the fox pro ole db to be able to access it), my location is C:\ab\name.dbf, and I've set other parameters (the ability to read/write to the DB, sharing of the file, etc.). The other important and customizable line is the command you will send. Here I've used SELECT * FROM name; - this means I'm selecting all (*) from the table "name" which happens to be in my database file, name.dbf. Your table name and .dbf file wont always be the same, and there may be more than one table in each .dbf file. Finally, I just used ActiveDocument.ActiveWindow.Selection.InsertAfter so i could print out the results to the open MSWord window, as this is all being written in VBA.

Handling the faulty WebBrowser control

The last post dealt with using the WebBrowser control to communicate with a website. Today I will take that post a step further and explain how to workaround the faulty responses the WebBrowser control returns regarding its status when loading a page. In the last post, we saw that the following code allows us to communicate with a website, navigating our control to a specific URL, and even sending POST data along with our request:

Sub Brws()
Dim URL As String, PostData() As Byte
URL = "https://www.google.com"

PostData = "name=ben&lname=cavallaro"
PostData = StrConv(PostData, vbFromUnicode)
Me.WebBrowser1.Silent = True
Me.WebBrowser1.Navigate URL, 0, "", PostData, ""
    Do
        DoEvents
    Loop Until Me.WebBrowser1.ReadyState = 4
End Sub

Today we will take this a step further, refining our code for checking whether the browser is done loading and also setting a variable equal to the html code of the page the control has navigated to.

Starting with the easier of the two, all we need to do to read the html from the page is use this line of code at any point after you have successfully completed the WebBrowser.Navigate command.

varname = Me.WebBrowser1.Document.Body.innerHTML

where varname is the variable you want to set equal to the html code, and your WebBrowser control is accessing the "Document" property of the WebBrowser control which allows you access to a number of the elements of the document itself. More info on this can be found at MSDN. For our purposes, this simple line of code pulls all the html from the page. However, because our WebBrowser control does not return a reliable result on whether it has finished loading the page, sometimes setting a variable equal to the Document.Body.innerHTML will return an error "91" because the page has not finished loading and therefore the property is not yet set. To deal with this, we will use error handlers and labels in our code.
An error handler tells your code snippet what to do when an error occurs. By default, an error in vba will trigger a Message box with the error number and a description of the error and then the program will terminate. Other options include "On Error Resume Next" which will simply ignore errors and move forward with the code, skipping the line that caused the error, "On Error Goto 0" which will end code execution silently when an error occurs, or "On Error Goto labelname" where "labelname" is a name you've chosen. By doing this, you will have to build your own error handler. In this case, we want to use "On Error Goto LabelName" to set up our own error handler. It will look like this:

Sub Brws()
On Error Goto errhandler
Dim URL As String, PostData() As Byte
URL = "https://www.google.com"

PostData = "name=ben&lname=cavallaro"
PostData = StrConv(PostData, vbFromUnicode)
Me.WebBrowser1.Silent = True
Me.WebBrowser1.Navigate URL, 0, "", PostData, ""
    Do
        DoEvents
    Loop Until Me.WebBrowser1.ReadyState = 4

RD:
abc = Me.WebBrowser1.Document.body.innerhtml
Exit Sub

ERRHANDLER:
If err.number = "91" Then
    Resume RD
Else
    Msgbox err.number & err.description
End If
End Sub

This bit of code incorporates labels and GoTo statements as well as an error handler. In the beginning we tell the code to goto "errhandler" if an error occurs. at the END of our code we define our errhandler section using a label "ERRHANDLER:".
Essentially, our errorhandler stops the code from reporting any error #91's, and instead loop back to around to the point in the code that is causing that error. In this way, we can create a loop that keeps attempting to set the variable "abc" to the value of innerHTML until it can successfully do so. If it's not an error #91, the errorhandler reports the error and then ends (because there is no more code to execute - which is why the errorhandler is placed at the end of the code).
Note that in our error handler we use the code "Resume RD" to goto our label "RD:". During a break operation like an error handler, we use "Resume" instead of "GoTo" to direct the code back to a label. Also not that after the last command PRIOR to our error handler code, we insert the "Exit Sub" code so that our code does not continue into our error handler unless directed to do so.

Webbrowser Control in VBA Excel / Word

Using the Webbrowser control is a helpful tool in many programming situations. One of the most common tasks that can be automated is interaction with websites. In the following example, I will setup the webbrowser control within the document itself, navigate to a website and send post data along to that site. I've changed the site location and the post data fields so the code will not function correctly until you enter that information in.

FYI: it is easy to find out the URL and the post data of the form you want to submit by following these steps:
Open your internet browser and view the site where the data would be entered. View the source code. You will find an HTML tag that says "form action=xxxxxx". The URL we want will be the "xxxxx" portion in that line. Then you will want to note all of the input tag name values within that form. These will look like "input type=xxx name=xxx". When posting your data (or when using a GET command, for that matter) you will need to know the input names so you can send the relevant data along with them.
In the example below i've included inputs named "fname", "lname", and "zip" which you will see in the postdata variable. The script below also reads through a list of 10 rows in an excel spreadsheet. If column "A" has data in it, then it pulls data from Columns A, B, and C corresponding to our variables "fname", "lname", and "zip", in that order. Then it moves on to the next row and loops until there is no more data or until row 10 is reached.

Sub getpage()
Dim URL As String, PostData() As Byte, kp as integer
URL = "https://examplepage.com/script.cgi"
For kp = 1 To 10 
'iterate through the first ten rows
   If Not Me.Cells(kp, 1) = "" Then 
   'if cell A1 isn't empty then proceed
      PostData = "fname=" & Me.Cells(kp, 1) & "&lname=" _
      & Me.Cells(kp, 2) & "&zip=" & Me.Cells(kp, 3)
      'set our postdata string to the variables and their 
      'corresponding values
      PostData = StrConv(PostData, vbFromUnicode) 
      'convert the string
      Me.WebBrowser1.Silent = True 
      'stop the browser from alerting us of anything
      Me.WebBrowser1.Navigate URL, 0, "", PostData, ""  
      'tell the browser to goto the URL
      Do
         DoEvents
         Loop Until Me.WebBrowser1.ReadyState = READYSTATE_COMPLETE 
         'let this loop run until the webbrowser has finished 
         'loading the page
   End If
Next kp
End Sub

As you will see, the code can iterate through a list of items and run a request to a website, filling in the data the user desires and retrieving the results. the me.webbrowser1.silent = true code stops the browser from spitting any messageboxes back at us so we can be sure there are no problems automating the process. The line "loop until me.webbrowser1.readystate = readystate_complete" is a line that waits until the webbrowser control has responded that it has fully finished loading the page. the words readystate_complete can be replaced by the number 4, as they are interchangeable. However, I have found that this code is often flawed as the webbrowser does not always return a reliable result about whether it has finished loading (it often says it's done loading but has not loaded the innerhtml property yet). I will deal with this issue in a later post.

For now, this should be a sufficient example to get you POSTing data using the webbrowser control. A GET command would be even simpler. You just append the data that is in the post variable to the end of the web address, with a question mark separating the web address and the postdata (ie. http://testsite.com/script.cgi?variable1=value&variable2=2ndvalue&variable3=thirdvalue)
Note, when combining the variables in the string you separate each new variable with the "&" character and place the input name on the left of the equals sign and the value on the right of it.

Programming

Just wanted to include some of the stuff I've learned over the past 2 months. I used to program a lot, but then I stopped for a very long time. Now it has become relevant again in my life, especially in the context of macros built into MS Office applications. It seems to make sense to me that I should post some of the code that I write - and some of the solutions to problems that have arisen while writing these macros. I often have trouble finding solutions to problems that I run into, and I think it is helpful to post these solutions so that others may find them quicker.