Extract from text revised edition of “METs table of Physical Activities”

National Institute of Health and Nutrition has revised ‘METs table of Physical Activities’ 2011 edition. Because it’s difficult to use as PDF file, I have extracted text data from it. I have asked if I could publish the file on this blog on July 3rd, 2012.

1. Open the file and copy all text.

2. Create new EXCEL book and paste with ‘Text File Wizard’. In the second tab, you have to remove all check mark of delimiters. In the third tab, select ‘String’ data type of column.

3. Press ‘Alt’ key and ‘F11’ key to launch VBE, insert module, and run the following code.

Option Explicit
Sub METS()
Dim mySht   As Worksheet
Dim myRng   As Range
Dim myAr    As Variant
Dim i       As Long
Dim j       As Long
Dim myReg   As Object
Dim myMatches   As Object
Dim myMatch     As Object
Const strReg    As String = "^[0-9]{5}$"
Dim CODE()  As String
Dim METS()  As Single
Dim MajorHeading()  As String
Dim SpecificActivities()    As String
Dim myArray()   As Variant
Set myReg = CreateObject("VBScript.RegExp")
With myReg
    .Pattern = strReg
    .IgnoreCase = True
    .Global = True
End With

Set mySht = ActiveSheet
Set myRng = mySht.UsedRange
myAr = myRng
j = 0
For i = LBound(myAr) To UBound(myAr)
    If myReg.Test(myAr(i, 1)) Then
        Set myMatches = myReg.Execute(myAr(i, 1))
        ReDim Preserve CODE(j)
        ReDim Preserve METS(j)
        ReDim Preserve MajorHeading(j)
        ReDim Preserve SpecificActivities(j)
        CODE(j) = myAr(i, 1)
        METS(j) = myAr(i + 1, 1)
        MajorHeading(j) = myAr(i + 2, 1)
        SpecificActivities(j) = myAr(i + 3, 1)
        j = j + 1
    End If
Next i
ReDim myArray(j, 3)
For j = LBound(myArray) To UBound(myArray) - 1
    myArray(j, 0) = CODE(j)
    myArray(j, 1) = METS(j)
    myArray(j, 2) = MajorHeading(j)
    myArray(j, 3) = SpecificActivities(j)
Next j
Set mySht = Worksheets.Add
mySht.Range("A1").Value = "CODE"
mySht.Range("B1").Value = "METS"
mySht.Range("C1").Value = "MajorHeading"
mySht.Range("D1").Value = "SpecificActivities"
mySht.Range("A2:D827") = myArray
End Sub