Using macro to validate data entered in excel against data in oracle database

  • 13 years ago


    I am very new to programming, macros and

    I am interested to find out how i can validate data that was entered into excel form against data in oracle database.

    I had build an excel document and macros module to validate the require fields and allow to add valid status message to a string 

    I need to validate those data entered exist in oracle database and return boolean that also me to add Valida status message to a string which i am not sure how to do go about it. Please advise

    Thank you.


    Private XL As New Excel.Application
    Private iCol As Integer
    Sub ValidateMain()
        Dim ValidStatus As String
        On Error GoTo ErrHandle
        XL.Workbooks.Open (Sheet1.Cells(2, "BB"))
        iCol = 4
        ValidStatus = ""
        If iIsEmpty(XL.Sheets(1).Cells(7, "N")) Then
            ValidStatus = ValidStatus & " [Mode is empty.] "
        End If
        Call WriteData("MODE", XL.Sheets(1).Cells(7, "N"), "System.String")
        If iIsEmpty(XL.Sheets(1).Cells(9, "N")) Then
            ValidStatus = ValidStatus & " [RequestRemarks1 is empty.] "
        End If
        Call WriteData("RequestRemarks1", XL.Sheets(1).Cells(9, "N"), "System.String")

        If ValidStatus = "" Then
            Sheet1.Cells(1, "BB") = "OK"
            Sheet1.Cells(1, "BB") = ValidStatus
        End If
        Call WriteData("DocProcessStatus", Sheet1.Cells(1, "BB"), "System.String")

        XL.ActiveWorkbook.Saved = True
        Exit Sub
        Sheet1.Cells(1, "BB") = "[" & Err & " : " & Err.Description & "]" & ValidStatus
        XL.ActiveWorkbook.Saved = True
     '   ValidateMain = ValidStatus
    End Sub

    Public Function iIsEmpty(ChkValue As String) As Boolean
        Dim rtVal As Boolean
        If IsEmpty(ChkValue) Or Trim(ChkValue) = "" Then
           rtVal = True
           rtVal = False
        End If
        iIsEmpty = rtVal
    End Function

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“In theory, theory and practice are the same. In practice, they're not.”