0

To start, I have a scraping function that scrapes a table from a web page, and stores the data in a 2D array.

The 2D array starts from row 0 to however many rows of data are on the page, and columns 1 to however many columns there are.

Row 0 simply contains all the column names.

My ReDim:

ReDim Addresses(0 To lngTotalRecords, 1 To columns.Count) As String

I've then stored this 2D array into a scripting dictionary called dictClients, as there are multiple clients that all have their own entries for the same table on the web page.

So in my dictionary I have something like the following to refer to a particular address table for a particular client: 

dictClients(1)("Addresses")
dictClients(2)("Addresses")

I now want to be able to check if a cell in a certain row contains a specific value, however the web page allows the columns to be reorganized so that:

dictClients(1)("Addresses")(1,1) 'row 1 column 1

will not always refer to the "Street Number" column. The street number column could be the following for someone else for example:

dictClients(1)("Addresses")(1,3) ' row 1 column 3

Given that these cells:

dictClients(1)("Addresses")(0,1) '(0,2) (0,3) etc.

all refer to the column's names, what's the best way for me to find the position of a particular named column?

Example: I want to get the value of the Postal Code cell in row 1, so I need to look in dictClients(1)("Addresses")(1, POSTALCODECOLUMN), which isn't always in the same position on the web page.

I was thinking of using the following function:

Public Function column(strArr() As String, strColumnName As String)
Dim i As Long
For i = 1 To UBound(strArr, 2)
If strArr(0, i) = strColumnName Then column = i
Next
End Function

But it just feels so lengthy calling it like:

strPostalCode = 
dictClients(1)("Addresses")(1,column(dictClients(1)("Addresses"), "Postal Code")

Is there a better and easier way to do this?

Thanks.

Community
  • 1
  • 1
loael
  • 21
  • 3
  • 3
    It's strange how all the scraping questions come in at once. My standard answer: ***Welcome to Stack Overlow!* "How to scrape web pages?" is one of the most common Excel/VBA questions on this site (close to 1000 questions & answers) and elsewhere. I find it hard to believe that you've exhausted all [options](https://stackoverflow.com/search?q=scrape+excel) before posting. Please see the [help/on-topic] and also "[ask]" and [these tips](https://codeblog.jonskeet.uk/writing-the-perfect-question/) and also: [mcve]. If you run into a problem not covered elsewhere, [edit] your post.*Good Luck* – ashleedawg Mar 14 '18 at 21:36
  • Possible duplicate of [Scraping data from website using vba](https://stackoverflow.com/questions/27066963/scraping-data-from-website-using-vba) – ashleedawg Mar 14 '18 at 21:39
  • @ashleedawg looks to me like web scraping is incidental to the Q. Real Q is about accessing an array with named columns – chris neilsen Mar 14 '18 at 22:57
  • @loael your function look ok to me as is. But if you really want to improve it, look into using a Class. You could then create a Method that accepts a row number and column name, returns the required data. Its call might look like `strPostalCode = Clients.GetData(1, "Addresses")` – chris neilsen Mar 14 '18 at 22:59

0 Answers0