In case anyone is interested I modified the script to be used for writing updates as well. It assumes that the fields which make up your primary key are the first X columns in the excel file. This is needed so they don't try to get updated, and so they appear in the where clause. It prompts for the number of unique columns. I'm sure some of my syntax can be cleaned up, this it my first time with VB.
Sub CreateUpdateScript()
Dim Row As Integer
Dim Col As Integer
'To store all the columns available in the current active sheet
Dim ColNames(100) As String
Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Get Columns from the sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
ColNames(ColCount) = ActiveSheet.Cells(Row, Col)
ColCount = ColCount + 1
Col = Col + 1
Loop
ColCount = ColCount - 1
'Inputs for the starting and ending point for the rows
Row = InputBox("Give the starting Row No.")
Dim MaxRow As Integer
MaxRow = InputBox("Give the Maximum Row No.")
Dim keyCols As Integer
keyCols = InputBox("Give the Number of unique id columns.")
'File to save the generated insert statements
File = "c:\\UpdateCode.txt"
fHandle = FreeFile()
Open File For Output As fHandle
Dim CellColCount As Integer
Dim StringStore As String
Dim whereClause As String
Do While Row <= MaxRow
StringStore = ""
whereClause = ""
CellColCount = 0
'ActiveSheet.Name will give the current active sheet name
'this can be treated as table name in the database
StringStore = StringStore + "update " + ActiveSheet.Name + " SET "
Do While CellColCount <= ColCount
If CellColCount>keyCols Then
StringStore = StringStore + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
End If
If CellColCount < keyCols-1 Then
whereClause = whereClause + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "' AND "
End If
If CellColCount = keyCols-1 Then
whereClause = whereClause + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
End If
If (CellColCount <> ColCount) AND (cellColCount > keyCols) Then
StringStore = StringStore + " , "
End If
CellColCount = CellColCount + 1
Loop
Print #fHandle, StringStore + " WHERE " + whereClause
Print #fHandle, " "
Row = Row + 1
Loop
Close #fHandle
MsgBox ("Successfully Done")
End Sub