This processes all cells in a worksheet.
it is expected to be called from a module which loops through worksheets hence the worksheet is a parameter
The value "SQL Script" needs to be added to the top left cell
The value "SQL Script End" needs to be added to the bottom left cell after the script


The format of the spreadsheet is
The first 10 rows max. as header rows
One of these rows has the first column with a value "Generate Script" and the folowing cell on the row with a "Y" or "N" to control processing.
The table name is expected to be in row 1 column 2

Following this there will be a row with the first column value as "Column Name"
This indicates the begining of the rows and columns to be processed and is the heading row.
The values in columns in this row are used to add values to the database - the column loop continues until there is a blank heading column value.
The loop processes each row until there is an empty cell in the first column
For each cell an entry is created using the heading value
An sql statement is generated to insert into a table with the table name, heading and cell value
A subroutine WriteLine is then called to add the line to a worksheet. The worksheet has previously been created - see create worksheet.

This leaves us with a worksheet with sql statements that can be executed

Sub AddTableMetadata(ws As Excel.Worksheet, sTableType As String)

    Dim sTableName As String
    Dim sGenerateScript As String
    
    ' Find the "Generate script?" cell in the first 10 rows, first column.
    ' Take the value from the next column (Y/Yes) default to N.
    Dim r As Integer
    
    sGenerateScript = "N"
    For r = 1 To 10
        If ws.Cells(r, 1).Value = "Generate script?" Then
            If ws.Cells(r, 2).Value = "Y" Or ws.Cells(r, 2).Value = "Yes" Then _
                sGenerateScript = "Y"
            Exit For
        End If
    Next
    
    ' Generate the add metadata for this entry.
    If sGenerateScript = "Y" Then
    
        giRow = giRow + 5
        sTableName = ws.Cells(1, 2)
        'The Drop table statement is useful if you're not dropping & recreating the entire database.
        Call WriteLine("delete DW_Metadata where TableName = '" & sTableName & "'")
        Call WriteLine("GO")
        Call WriteLine("")

        Call WriteTableMetadata(sTableName, ws, sTableType)
    End If
End Sub

Sub WriteTableMetadata(sTableName As String, ws As Excel.Worksheet, sTableType As String)
    Dim i As Integer, j As Integer, k As Integer
    Dim iColCnt As Integer
    Dim sXColPropName() As String
    Dim sXColPropGen() As String
    Dim sColName() As String
    Dim sColProp() As String
    Dim sDataType As String
    Dim sNull As String
    Dim sColInfo As String
    Dim sPK As String
    Dim sLine As String
    Dim sFK As String
    Dim sDefault As String
    Dim sColList As String
    Dim sUnkMember As String
    Dim sUnkMemberElement As String
    
    Dim iHeadingsRow As Integer
    Dim iColRow As Integer
    Dim iPropertyCol As Integer
    ' Find the cell in column one that's labeled "Column Name".
    ' This must be in the first column and indicates the start of the column list
    For iHeadingsRow = 5 To 20     'Just in case someone adds or subtracts some rows from the top of the spreadsheet.
        If ws.Cells(iHeadingsRow, 1) = "Column Name" Then
            Exit For
        End If
    Next
    
    ' Now loop throuh all the columns and all the heading writing the values to the metadata table
    iColRow = iHeadingsRow + 2
    Do While ws.Cells(iColRow, 1) <> ""
        sLine = "insert DW_Metadata select '" & sTableName & "', '" & ws.Cells(iColRow, 1) & "', 'ColSeq', '" & iColRow - iHeadingsRow - 1 & "'"
        Call WriteLine(sLine)
        iPropertyCol = 1
        Do While ws.Cells(iHeadingsRow, iPropertyCol) <> ""
            If ws.Cells(iColRow, iPropertyCol) <> "" And ws.Cells(iHeadingsRow + 1, iPropertyCol) = "Y" Then
                sLine = "insert DW_Metadata select '" & sTableName & "', '" & ws.Cells(iColRow, 1) & "', '" & ws.Cells(iHeadingsRow, iPropertyCol) & "', '" & Replace(ws.Cells(iColRow, iPropertyCol), "'", "''") & "'"
                Call WriteLine(sLine)
            End If
            iPropertyCol = iPropertyCol + 1
        Loop
        iColRow = iColRow + 1
    Loop
End Sub
Sub WriteLine(sText As String)
'Write a line to the script
    gwsMetadataScript.Cells(giRow, 1).Value = sText
    giRow = giRow + 1
End Sub