HTML Web Scraping Using VBA

  • Hi,

    I am new to HTML web scraping but let me try to summarize what I'm trying to do real quick before I get into my problem. I need to be able to perform the following steps on a website called using VBA:
    1. Load the webpage
    2. Click the login button
    3. Enter username and password
    4. Click login button
    5. Access needed information

    I have been able to perform the first 2 steps using the following code

    Where I am getting stuck is on Step 3. I have combed through the HTML provided below and determined that the username and password fields that I need are input boxes.

    However, I can't even get my VBA to print the name of all the input fields on the page using Debug.Print. I am using the following code.

    1. 'Prompt user to enter in their login info and login to website
    2. Set HTMLInputs = HTMLDoc.getElementsByTagName("input")
    3. For Each HTMLInput In HTMLInputs
    4. Debug.Print HTMLForm.getAttribute("name")
    5. Next HTMLInput

    Can anyone tell me what am I doing wrong???

  • Ok so I've figured out how to get the username and password into the correct input boxes on the site using the following code. The problem that I'm experiencing now is that the code works when i step through it using F8 and the fields are populated with the right info but when I try to run the code completely without using F8, I get a "Object variable or With block variable not set" error. Can anyone tell me why I'm getting this error?

    1. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px]'Prompt user to enter in their login info and login to website[/SIZE][/FONT][/COLOR][/align]
    2. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] Set HTMLUser = HTMLDoc.getElementById("form-email")[/SIZE][/FONT][/COLOR][/align]
    3. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] HTMLUser.Value = "xxxxxxxxx"[/SIZE][/FONT][/COLOR][/align]
    4. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] Set HTMLPWord = HTMLDoc.getElementById("form-password")[/SIZE][/FONT][/COLOR][/align]
    5. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] HTMLPWord.Value = "**********"[/SIZE][/FONT][/COLOR][/align]
  • [SIZE=13px]I have since changed the code where I am entering the user information into the website input fields to the following for loop.[/SIZE]

    1. [align=left][COLOR=#000000][FONT=Arial]Set HTMLInputs = HTMLDoc.getElementsByTagName("input")[/FONT][/COLOR][/align]
    2. [align=left][COLOR=#000000][FONT=Arial] For Each HTMLInput In HTMLInputs[/FONT][/COLOR][/align]
    3. [align=left][COLOR=#000000][FONT=Arial] Debug.Print HTMLInput.className, HTMLInput.ID[/FONT][/COLOR][/align]
    4. [align=left][COLOR=#000000][FONT=Arial] If HTMLInput.className = "form-control input-lg" And HTMLInput.ID = "form-email" Then[/FONT][/COLOR][/align]
    5. [align=left][COLOR=#000000][FONT=Arial] HTMLInput.Value = "XXXXXXXX"[/FONT][/COLOR][/align]
    6. [align=left][COLOR=#000000][FONT=Arial] End If[/FONT][/COLOR][/align]
    7. [align=left][COLOR=#000000][FONT=Arial] If HTMLInput.className = "form-control input-lg" And HTMLInput.ID = "form-password" Then[/FONT][/COLOR][/align]
    8. [align=left][COLOR=#000000][FONT=Arial] HTMLInput.Value = "*****************"[/FONT][/COLOR][/align]
    9. [align=left][COLOR=#000000][FONT=Arial] End If[/FONT][/COLOR][/align]
    10. [align=left][COLOR=#000000][FONT=Arial]Next HTMLInput[/FONT][/COLOR][/align]

    The issue that I'm having now is that when I run the code fully, I get no output from the debug.print line. When I run it line by line, however, the code spits out exacly what it should and does exactly what I need it to do.

  • Are you still having issues?

  • Sorry been busy lately and this slipped my mind. Need to pick up son soon .. will look at the website in a jiffy.

  • Ok .... I've added two *.bas ( module files )

    Import them ..... remove stuff that doesn't compile for you ( to be quick I had to export code from working projects ) so there are function calls to other routines that I've not included.

    you will probably need to rename the files to *.bas as you can not upload *.bas file extensions. I'll check back later.