Turn 3 dimensional (pivot) table to junction table

Scenario: You have a "three dimensional table" in Excel where the rows are articles and the columns are suppliers, and each cell in the table are marked with an x (or number indicating something) indicated which articles and suppliers that are related. You have decided to create a database instead with one table with articles and another with suppliers. Let's say one article can have multiple suppliers and one supplier can deliver multiple articles, i.e. you need an junction table. How do you create a junction table from this three dimensional table?

My Solution: Search thorugh each row in the table fro a value larger than zero (anything but zero or empty) , for each hit write a line in a textfile with the supplier, article and cell value. Then use the text file to create your junction table.

The code

Sub ConvertTable()

Dim str, strForb As String

Dim col As Integer

Sheets("Sheet1").Select

Dim objFSO, objFile

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.CreateTextFile("Rows.txt")

For i = 2 To 5 'enter which row that contains data

strArticle = Range("A" & i).Value

For Each Cell In Range("B" & i & ":E" & i)

If Cell.Value > 0 Then

col = Cell.Column

str = strForb & "," & Range(ColNo2ColRef(col) & 1) & "," & Cell.Value

objFile.WriteLine (str & cbrlf)

End If

Next Cell

Next i

objFile.Close

End Sub

Function ColNo2ColRef(ColNo As Integer) As String

If ColNo <> 256 Then

ColNo2ColRef = "#VALUE!"

Exit Function

End If

ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)

ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)

End Function