excel vba - transform an sql

Has anyone had any luck with a vba macro that would convert this input:

update my_table 
    set time = sysdate, 
    randfield1 = 'FAKE', 
    randfield5 = 'ME', 
    the_field8 = 'test' 
    where my_key = '84' 
    ;

into this output?

select count(*) from my_table
where (randfield1 <> 'FAKE'
or randfield5 <> 'ME'
or the_field8 <> 'TEST')
and my_key = '84';

update (what happens when using Remou's answer):

INPUT (what i have place in cell A1 of first sheet)-

update my_table  
    set time = sysdate,  
    randfield1 = 'FAKE',  
    randfield5 = 'ME',  
    the_field8 = 'test'  
    where my_key = '84'  
    ; 

OUTPUT (what is generated in a1 of the 2nd sheet once the macro is run)-

SELECT Count(*) FROM  my_table
WHERE ()
)
)
)
)
)
)
)
randfield1 <> 'FAKE'
OR )
)
)开发者_如何学运维
)
randfield5 <> 'ME'
OR )
)
)
)
the_field8 <> 'test')
)
)
)
)
AND my_key = '84'
;

I am still not sure what you want, but anyway:

Dim r As Range
Dim cl As Range
Dim s As String
Dim c As String
Dim arys As Variant
Dim i As Long, j As Long

''Assuming an existing worksheet
Set r = Sheet1.UsedRange
j = Sheet2.UsedRange.Rows.Count

For Each cl In r.Cells
    c = cl.Value

    ''Fake spaces
    Do While InStr(c, Chr(160)) > 0
        c = Replace(c, Chr(160), "")
    Loop

    ''Real spaces
    c = Trim(c)

    If c = ";" Then

        arys = Split(s, vbCrLf)

        For i = 0 To UBound(arys)
            Sheet2.Cells(j, 1) = arys(i)
            j = j + 1
        Next

        ''Layout
        j = j + 2

    ElseIf UCase(c) Like "UPDATE*" Then

        s = "SELECT Count(*) FROM " & Replace(c, "update", "", , , vbTextCompare)
        s = s & vbCrLf & "WHERE ("

    ElseIf UCase(c) Like "WHERE*" Then
        s = s & Replace(c, "where", "AND", , , vbTextCompare)
        s = s & vbCrLf & ";"

    ElseIf Left(UCase(c), 3) <> "SET" Then
        c = Replace(c, "=", "<>")

        If Right(c, 1) = "," Then
            s = s & Left(c, Len(c) - 1) & vbCrLf & "OR "
        Else
            s = s & c & ")" & vbCrLf
        End If

    End If
Next

Well, this does at least work for your sample input...

Sub NotExtensibleInTheLeast()

  Dim sql As String

  sql = _
      "update my_table " & Chr$(10) & _
      "    set time = sysdate, " & Chr$(10) & _
      "    randfield1 = 'FAKE', " & Chr$(10) & _
      "    randfield5 = 'ME', " & Chr$(10) & _
      "    the_field8 = 'test' " & Chr$(10) & _
      "    where my_key = '84' " & Chr$(10) & _
      "    ;"

  Dim newSql
  newSql = sql
  newSql = Replace$(newSql, "where", ") and")
  newSql = Replace$(newSql, "update", "select count(*) from")
  newSql = Replace$(newSql, "set", "where (")
  newSql = Excel.Application.WorksheetFunction.Substitute(newSql, "=", Chr$(22), 5)
  newSql = Replace$(newSql, "=", "<>")
  newSql = Replace$(newSql, Chr$(22), "=")
  newSql = Replace$(newSql, ",", " or ")
  newSql = Replace$(newSql, "time <> sysdate or", vbNullString)

  MsgBox newSql

End Sub