How to run query, automate using VBA Macro and Excel, make "loading" feature while reconciling?

Question

Hi, 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:

1) It is querying - if the value is NULL, how may I tell it to print out "DATA NOT AVAILABLE"
2) How can I clear up the old output from pipeline A, when I am querying pipeline B?
3) My dates are saved as strings in Oracle - how can I convert that to date?
Thank you!

Here is what I have so far:

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, " & _
"pipeline, station, stationname, drn, state, county, owneroperator, companycode, " & _
"pointcode, pottypeind, flowdirection, pointname, facilitytype, pointlocator, " & _
"pidgridcode from pipelineflow, pipelineproperties " & _
"where pipelineflow.lciid = piplineproperties.lciid " & _
"and pipelineflow.audit_active = 1 " & _
"and pipelineproperties.audit_active =1 " & _
"and pipelineflow.ldate >= '" & Format(DateStart, "dd-MMM-yyyy") & "' and pipelineflow.ldate < '" & Format(DateEnd, "dd-MMM-yyyy") & "' " & _
"and pipelineflow.ldate >= '" & DateStart & "' and pipelineflow.ldate < '" & DateEnd & "' " & _
"and pipelineproperties.pipeline = '" & Pipeline & "' "


    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

Answer 1

Answers to previous questions (paraphrased):

1) "how to make sure end date is after start date":

Valid date values are floating point numbers, so DateEnd should be >= DateStart. The whole number part is the number of days since 1900-01-01. The fractional part is the current time of day (eg 12 noon = 0.5).

2) "use fancy calendar entry controls for dates"

Look at the controls available under the Insert> Object menu (in Excel 2003 and earlier - it's in 2007 too, but in a different place). One of them is a Calendar control. Double-clicking it in the Objects list will insert it into the current cell and put the sheet into Design Mode. Right click the control and choose Properties. Type a cell address into the LinkedCell field. Then click the "Exit Design Mode" button from the little toolbar that should have popped up. Now when you select a date on the control, it will show the value in the cell you linked it to.

Similarly there is a drop down list control that you can use to select your pipeline types.

3) "why am I getting an error on DateEnd = Range("B3").Value?"

The DateEnd error is probably due to a missing or invalid value in the cell you specified, as I asked in my comment.

What version of Excel are you doing this in? Excel 2003

Answer 2

Since you changed your questions, I'll add another answer.

1) It is querying - if the value is NULL, how may I tell it to print out "DATA NOT AVAILABLE"

Which value? I suspect that you mean when the query returns no records. To check this, test for rsObject.RecordCount = 0:

Dim ws As Worksheet
Set ws = Worksheets("ZaiNet Data")

ws.UsedRange.Clear '' remove results of previous query if any

If rsObject.RecordCount = 0 Then
  ws.Cells(1, 1) = "DATA NOT AVAILABLE"
Else
  ws.Cells(1, 1).CopyFromRecordset rsObject
End If

You can also test for one or both of rsObject.BOF or rsObject.EOF being true ("Beginning Of File" or "End Of File" respectively).

When developing things in VBA, especially when using new features that I'm unfamiliar with, I do lots of tests that output things to the Immediate Window. To help with this, I use the following little routine:

Sub Say(s as String)
  Debug.Print s
End Sub

It makes it a little easier to generate testing output that typing "Debug.Print" all the time (even slightly easier than typing "Debug.P" + Enter using Intellisense).

So when you open your recordset, show the record count after it:

rsObject.Open strQry, cnnObject, adOpenStatic
Say rsObject.RecordCount & " records"

Do something like this any time you want to verify a value.

Later on, if you want to capture your debugging statements in a text file, you just need to change the operation of the Say() routine.

2) How can I clear up the old output from pipeline A, when I am querying pipeline B?

As shown in context above:

ws.UsedRange.Clear '' remove results of previous query if any

3) My dates are saved as strings in Oracle - how can I convert that to date?

You don't technically need to convert the resulting date strings to date values, you may find that just by putting them in a cell, Excel will treat them as date values.

You just need to make sure that the user's dates get converted to the format that the database is expecting.

Your query string as it stands above still shows two lines incorporating the user's dates. The one that uses Format() to convert them to "dd-MMM-yyyy" format is the one you want to keep. Delete the other line, making sure your string concatenating syntax is still correct.

To actually convert the date string to a date value though, you would use the CDate() function:

Sub DateTest()
  Dim sDate As String
  Dim dDate As Date

  sDate = "09-Jul-2009"
  dDate = CDate(sDate)

  Say "sDate = " & sDate
  Say "dDate = " & dDate

  dDate = dDate + 1
  Say "dDate = " & dDate
End Sub


Immediate Window output:
sDate = 09-Jul-2009
dDate = 7/9/2009
dDate = 7/10/2009

We can verify that it converted the string to a date value because it shows up in the default date format for my machine, and responds to date math (adding 1 day).