如何运行查询，使用 VBA 宏和 Excel 自动化，在协调时制作“加载”功能？

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

提问by Techgirl09

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:

我正在通过 VBA 构建一个协调工具，它可以自动从我的 oracle 数据库和工作表进行查询。当我运行查询时，我希望用户输入要查询的 ITEM（在本例中为管道）（工作表有许多项目）和结束/开始日期。我在弄清楚以下问题时遇到了麻烦：

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!

1) 它正在查询 - 如果值为 NULL，我如何告诉它打印出“DATA NOT AVAILABLE”
2) 当我查询管道 B 时，如何清除管道 A 的旧输出？
3) 我的日期在 Oracle 中保存为字符串 - 如何将其转换为日期？
谢谢！

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

回答by Todd

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"

1）它正在查询 - 如果值为 NULL，我如何告诉它打印出“DATA NOT AVAILABLE”

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

哪个值？我怀疑您的意思是查询不返回任何记录。要检查这一点，请测试 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).

您还可以测试 rsObject.BOF 或 rsObject.EOF 之一或两者是否为真（分别为“文件开头”或“文件结尾”）。

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:

在 VBA 中开发东西时，尤其是在使用我不熟悉的新功能时，我做了很多测试，将东西输出到立即窗口。为了帮助解决这个问题，我使用了以下小程序：

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).

它使生成始终键入“Debug.Print”的测试输出更容易一些（甚至比使用 Intellisense 键入“Debug.P”+ Enter 稍微容易一些）。

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.

稍后，如果您想在文本文件中捕获调试语句，只需更改 Say() 例程的操作即可。

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

2) 当我查询管道 B 时，如何清除管道 A 的旧输出？

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?

3) 我的日期在 Oracle 中保存为字符串 - 如何将其转换为日期？

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.

从技术上讲，您不需要将生成的日期字符串转换为日期值，您可能会发现只需将它们放入单元格中，Excel 就会将它们视为日期值。

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.

上面的查询字符串仍显示包含用户日期的两行。使用 Format() 将它们转换为“dd-MMM-yyyy”格式的一种是您想要保留的一种。删除另一行，确保您的字符串连接语法仍然正确。

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

要将日期字符串实际转换为日期值，您可以使用 CDate() 函数：

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).

我们可以验证它是否将字符串转换为日期值，因为它以我机器的默认日期格式显示，并响应日期数学（加 1 天）。

回答by Todd

Answers to previous questions (paraphrased):

先前问题的答案（释义）：

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

1）“如何确保结束日期在开始日期之后”：

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).

有效的日期值是浮点数，因此 DateEnd 应 >= DateStart。整数部分是自 1900-01-01 以来的天数。小数部分是一天中的当前时间（例如中午 12 点 = 0.5）。

2) "use fancy calendar entry controls for dates"

2）“对日期使用花哨的日历条目控件”

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.

查看“插入”>“对象”菜单下的可用控件（在 Excel 2003 及更早版本中 - 在 2007 中也是如此，但在不同的位置）。其中之一是日历控件。在对象列表中双击它会将其插入当前单元格并将工作表置于设计模式。右键单击控件并选择属性。在 LinkedCell 字段中键入单元格地址。然后从应该弹出的小工具栏中单击“退出设计模式”按钮。现在，当您在控件上选择一个日期时，它将在您链接到的单元格中显示该值。

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?"

3) “为什么我在 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.

DateEnd 错误可能是由于您指定的单元格中的值丢失或无效，正如我在评论中所问的那样。

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

你在哪个版本的 Excel 中执行此操作？Excel 2003