title: How to run query, automate using VBA Macro and Excel, make "loading" feature while reconciling?
body: <p>I am building a reconciliation tool via VBA that automates queries from my oracle database and a worksheet. When I run the query I want the user to input what ITEM (in this case pipeline) to query (the worksheet has many items) and the end/start dates. I am having trouble figuring out the following:</p>
  
  <p><strong>1) It is querying - if the value is NULL, how may I tell it to print out "DATA NOT AVAILABLE"<br>
  2) How can I clear up the old output from pipeline A, when I am querying pipeline B?<br>
  3) My dates are saved as strings in Oracle - how can I convert that to date?<br>
  Thank you!</strong></p>
  
  <p>Here is what I have so far:</p>
  
  <pre><code>Option Explicit 
  Option Base 1
  
  Dim cnnObject As ADODB.Connection
  Dim rsObject As ADODB.Recordset
  Dim strGPOTSConnectionString As String
  
  Dim startDate As Date
  Dim endDate As Date
  Dim strPipelineName As String
  Dim strQuery As String
  
  Sub ClickButton2()
  
  Debug.Print ("Button has been clicked")
  Dim Pipeline As String
  Dim DateStart As Date
  Dim DateEnd As Date
  
  Pipeline = InputBox("Enter PipeLine", "My Application", "Default Value")
  DateStart = InputBox("Enter Start Date", "My Application", DateTime.Date)
  DateEnd = InputBox("Enter End Date", "My Application", DateTime.Date + 1)
  
  Pipeline = Range("B1").Value
  DateStart = Range("B2").Value
  DateEnd = Range("B3").Value
  
  strQuery = "select pipelineflow.lciid lciid, ldate, volume, capacity, status, " &amp; _
  "pipeline, station, stationname, drn, state, county, owneroperator, companycode, " &amp; _
  "pointcode, pottypeind, flowdirection, pointname, facilitytype, pointlocator, " &amp; _
  "pidgridcode from pipelineflow, pipelineproperties " &amp; _
  "where pipelineflow.lciid = piplineproperties.lciid " &amp; _
  "and pipelineflow.audit_active = 1 " &amp; _
  "and pipelineproperties.audit_active =1 " &amp; _
  "and pipelineflow.ldate &gt;= '" &amp; Format(DateStart, "dd-MMM-yyyy") &amp; "' and pipelineflow.ldate &lt; '" &amp; Format(DateEnd, "dd-MMM-yyyy") &amp; "' " &amp; _
  "and pipelineflow.ldate &gt;= '" &amp; DateStart &amp; "' and pipelineflow.ldate &lt; '" &amp; DateEnd &amp; "' " &amp; _
  "and pipelineproperties.pipeline = '" &amp; Pipeline &amp; "' "
  
  
      Call PullZaiNetData(strQuery)
  
      Call TieOut
  
  End Sub
  
  Sub PullZaiNetData2(ByVal strQry As String)
  Set cnnObject = New ADODB.Connection
  Set rsObject = New ADODB.Recordset
  
  strGPOTSConnectionString = "DRIVER={Microsoft ODBC for Oracle}; SERVER=hhh; PWD=hhhh; UID=hhh"
  
  
  cnnObject.Open strGPOTSConnectionString
  
  rsObject.Open strQry, cnnObject, adOpenStatic
  Worksheets("ZaiNet Data").Cells(1, 1).CopyFromRecordset rsObject
  
  
  rsObject.Close
  cnnObject.Close
  
  Set rsObject = Nothing
  Set cnnObject = Nothing
  
  End Sub
  
  Sub TieOut()
  
  End Sub
  </code></pre>