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.
--
HTH. Best wishes Harald
Followup to newsgroup only please
"pmz" skrev i melding
...
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