Validate for excel against Oracle DB

• • Reply

  14 years ago

  by spect75

  spect

  , Singapore

  Joined 14 years ago

  Hi,

  I am very new to programming, macros and VB.net.

  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"
      Else
          Sheet1.Cells(1, "BB") = ValidStatus
      End If
      Call WriteData("DocProcessStatus", Sheet1.Cells(1, "BB"), "System.String")
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

      XL.ActiveWorkbook.Saved = True
      XL.ActiveWorkbook.Close
      XL.Quit
     
      Exit Sub
     
  ErrHandle:
      Sheet1.Cells(1, "BB") = "[" & Err & " : " & Err.Description & "]" & ValidStatus
      XL.ActiveWorkbook.Saved = True
      XL.ActiveWorkbook.Close
      XL.Quit
   '   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
      Else
         rtVal = False
      End If
      iIsEmpty = rtVal
  End Function
  
  
  
  
  
  
  
  

  Report abuse