I am really hoping this can be accomplished. Basically, I have a routine that queries an view I have created in Oracle SQL Developer. However, the entire view has thousands of more rows than I need and I want to pull just a subset based on a list of part numbers I have within the same Excel file, but everything I have tried results in errors.
Here's the code right now:
--Create Connection and Recordset objects
Set odmConn = New ADODB.Connection
Set odmRec = New ADODB.Recordset
odmConn.ConnectionTimeout = 0
odmConn.CommandTimeout = 0
odmRec.CursorLocation = adUseClient
--Open Connection
odmConn.Open "Driver={Microsoft ODBC for Oracle};SERVER=ODBCServer;UID=MyID;PWD=MyPass;"
--Define SQL string and sheet name
strSQL = "SELECT A.* " & _
"FROM MyID.MyTable A" & _
"INNER JOIN " & _
"(SELECT * FROM [Excel 12.0 Xml;HDR=Yes;Database=MyExcelPath].[MAT$]) B" & _
"ON A.MATERIAL = B.MATERIAL"
shName = "MySheet"
--Extract new data to Recordset
odmRec.Open strSQL, odmConn, adOpenStatic, adLockReadOnly
If odmRec.RecordCount <> 0 Then
--Copy new data from Recordset
ThisWorkbook.Sheets(shName).Range("A2").CopyFromRecordset odmRec
End If
The above results in an "ORA-00903: invalid table name" error.
I have also tried OPENROWSET and OPENDATASOURCE to no avail. Both of these result in an error that says either "missing right parenthesis" or "SQL command not properly ended."
Is there anyway to do this?