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 |
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 |
All times are GMT +1. The time now is 08:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com