Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba macro for getting external data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba macro for getting external data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data Warning Message - I have No External Data in wrkbk | Excel Discussion (Misc queries) | |||
Getting External Data based on criteria insde of the external data | Excel Discussion (Misc queries) | |||
external data automatic refresh through macro/query | Excel Discussion (Misc queries) | |||
Get External Data Macro | Excel Discussion (Misc queries) | |||
External data Macro Problem Excel 97 | Excel Discussion (Misc queries) |