Excel Macro to Generate SQL From Sparsely Populated Sheet?

Question

I have a worksheet that has been sparsely populated with data that needs to go into several relational database tables. The sheet looks like this:

A        B        C        D        E        F        G
Table_1  Table_2  Table_2  Table_3  Table_3  Table_3  Table_3
                           Table_3  Table_3  Table_3  Table_3
         Table_2  Table_2  Table_3  Table_3  Table_3  Table_3
                           Table_3  Table_3  Table_3  Table_3
         Table_2  Table_2  Table_3  Table_3  Table_3  Table_3
                           Table_3  Table_3  Table_3  Table_3
Table_1  Table_2  Table_2  Table_3  Table_3  Table_3  Table_3
                           Table_3  Table_3  Table_3  Table_3
         Table_2  Table_2  Table_3  Table_3  Table_3  Table_3
                           Table_3  Table_3  Table_3  Table_3
         Table_2  Table_2  Table_3  Table_3  Table_3  Table_3
                           Table_3  Table_3  Table_3  Table_3

Given this data, I would generate SQL that would populate data in column A in Table_1, columns B and C in Table_2 (with a foreign key reference to the value from Table_1) and columns D, E and F in Table_3 (with a foreign key reference to the value from Table_2). The SQL can be placed in a separate sheet, a text file, the clipboard--pretty much anywhere.

I can manipulate the table if necessary (to fill in values, re-arrange data, whatever) but would prefer to avoid this manual step. What I'm hoping is that there's some Excel/VBA macro where I can pass the source range (or sheet), and then the sub ranges for each table, and have the macro know how to find the reference back to the parent table.

Answer 1

This is a pretty rudimentary example, and is dependent on the fact that your data is laid out exactly as you described it above. (You can change this to pass originCell as an argument if you wanted to, or just manually change the value while troubleshooting/implementing.)

All it does is take the row value of the original cell and create a new range in column A with that same row number. Then it just looks to the cell above it and checks if it's blank. If it's empty, it shifts the range up one row until it finds a populated cell. I have it deposit the value of the found parentCell into I3, obviously you can put it anywhere you want just to verify that it's finding the right value.

Sub findParent()
    Dim originCell As Range, parentCell As Range
    Set originCell = Sheet1.Range("E3")
    Set parentCell = Sheet1.Cells(originCell.Row, 1)
    While IsEmpty(parentCell) = True
        Set parentCell = parentCell.Offset(-1, 0)
    Wend
    Sheet1.Range("I3").Value = parentCell.Value
End Sub

This would be a good jumping off point to any number of solutions. In addition to passing in the originCell as an argument, you could also pass in the column number of, say, the Table_2 value you want to find using a form like this:

Sub findParent(originCell as Range, parentColumn as long)

Answer 2

You could just create named-ranges for the headers (or the whole range if it's static), with a text-string that has the tablename embedded in it, then parse it out in the code.