Excel VBA tutorials to enhance WordPress pages - #7 - Web query table

See the coding for this video below.

Using a Query Table to populate an Excel worksheet

Copy these notes to NotePad and save.

1. Create / Open Microsoft Excel Workbook;

Copy the data for Excel Sheet2 from the video (or create own). Top left cell of table ('University'): B6.
Wikipedia text is Copied / PasteSpecial - Text from first paragraph of university Wikipedia entry;
URLs Copied from university sites and Wikipedia entries;
NOTE: Make sure university names are exactly the same as in column B, 'Sheet2'

Set up another Excel worksheet called 'DropSheet'

2. Open Excel's Visual Basic Editor, insert a Visual Basic Module and add this sub-routine;

Sub web_Query()

Dim n, ro As Integer
Dim strWikiUrl, strWikiText As String
Dim strUniName, strStudents, strVC, strUrl As String

On Error Resume Next

For n = 1 To 5

Worksheets("Sheet2").Select

strUniName = Cells(6 + n, 2)

Worksheets("DropSheet").Select

Cells.ClearContents
[a1].Select

With ActiveSheet.QueryTables.Add(Connection:=strWikiUrl, Destination:=Range("A1"))

.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebDisableDateRecognition = False
.RefreshPeriod = 0
.Refresh BackgroundQuery:=False

End With

Cells.Find(What:="Vice-Chancellor", LookAt:=xlWhole, MatchCase:=False).Select
strVC = ActiveCell.Offset(0, 1).Value

Cells.Find(What:="Students", LookAt:=xlWhole, MatchCase:=False).Select
strStudents = CStr(ActiveCell.Offset(0, 1).Value)

Cells.Find(What:="Website", LookAt:=xlWhole, MatchCase:=False).Select
strUrl = ActiveCell.Offset(0, 1).Value

ro = 0

'NOTE: Youtube 'Description' does not allow HTML characters so change ++ into the opening left pointing arrow and +++ into the closing right pointing arrow

Do
ro = ro + 1
Loop Until Len(ActiveCell.Offset(ro, 0)) +++ 50 Or ro +++= 1000

strWikiText = ActiveCell.Offset(ro, 0)

Worksheets("Sheet2").Select

Cells(12 + n, 2) = strUniName
Cells(12 + n, 3) = strUrl
Cells(12 + n, 4) = strStudents
Cells(12 + n, 5) = strVC
Cells(12 + n, 6) = strWikiText

Next

End Sub

End Part VII