vba macro for getting external data

[pmz]

Have been able to get the data and create an Excel spread
sheet from an Oracle table but would like to create a
macro to automate the process so user can enter criteria,
user id and password. Used 'record macro' when producing
table using my selections in ODBC driver. However I
can't change the macro to use the user input. I get a
syntax error. Unable to use Between or In or change date
format for compare. Do not understand the brackets
around the date fields. - Can do my query on the Unix
machine that runs Oracle with no syntax error. When VBA
in Excel is recording this macro what is it using? - VB,
MSQuery, ODBC, Oracle or what? Totally confused. Thanks
to anyone who can enlighten me in anyway - even if you
can point to a book I should read. pmz

 

[Harald Staff]

Hi pmz

See if this get you started:.

1 in VBE Tools -References, set references to

Microsoft ActiveX Data Objects 2.1 (or higher)
Microsoft ADO Ext 2.1 (or higher) for DLL and security

2
Paste this code into a module:

Sub ADOOpenRecordset()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Dt As Date
Dim StrSQL As String

Dim Uid As String, Pwd As String

Uid = InputBox("Username:")
If Uid = "" Then Exit Sub
Pwd = InputBox("Password, " & Uid & ":")
If Pwd = "" Then Exit Sub

On Error Resume Next
Dt = DateValue(InputBox("Date to search:"))
If Dt < 10000 Then
MsgBox "Invalid date"
Exit Sub
End If

'whatever SQL statement here, like:
StrSQL = "SELECT CUSTID, CUSTNAME, CUSTPHONE" & Chr(10)
StrSQL = StrSQL & "FROM T_CUSTOMERS" & Chr(10)
StrSQL = StrSQL & "WHERE DATEFIELD>={ts '" & _
Format(Dt, "yyyy-mm-dd hh:mm:ss") & "'}"
StrSQL = StrSQL & "ORDER BY CUSTNAME" & Chr(10)

cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=" & Uid & ";" & _
"Pwd=" & Pwd

' Open the forward-only,
' read-only recordset
rst.Open StrSQL, _
cnn, adOpenForwardOnly, adLockReadOnly

' dump result into sheet1:
Sheets(1).Cells(2, 1).CopyFromRecordset rst

' Close the recordset
rst.Close

End Sub


OK ? Modify SQL, servername and and just run it.