I use a variation of this:

First, in separate named cells I have the following:
DestWkshtName
DestRngStartCell:=[DestStartCellRef].Value, _
QryTableName
DataProvider
Sql

I also use a Form to prompt the user for UserName and Password if they
haven't already been entered. (The password is stored in a static variable
that is destroyed upon workbook closure)

This code performs the actual query:
**************************
Option Explicit
Global prmUsername As String
Global prmPassword As String

Sub GetDataFromDatabase( _
  strDestWkshtName As String, _
  strDestRngStartCell As String, _
  strQryTableName As String, _
  strDataProvider As String, _
  strSql As String, _
  strTNSNAME_entry As String, _
  strUserName As String, _
  strPwd As String)

Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset
Dim strConnString As String
Dim intCounter As Integer
Dim strNewEntry As String
Dim qtbQTbl As QueryTable
Dim thing As Variant

'Clear previous data from the destination range
Range(strDestRngStartCell) _
  .Offset(RowOffset:=1, ColumnOffset:=0) _
  .CurrentRegion _
  .ClearContents

'Delete the Data Destination Range Name
'so it can be replaced later in the process
With ThisWorkbook.Worksheets(strDestWkshtName)
  If .QueryTables.Count <> 0 Then
     For Each qtbQTbl In .QueryTables
        If qtbQTbl.Name = strQryTableName Then
           On Error Resume Next
           .Range(strQryTableName).ClearContents
           On Error Resume Next
           qtbQTbl.Delete
           On Error Resume Next
           .Names(strQryTableName).Delete
           On Error GoTo 0
        End If
     Next qtbQTbl
  End If
End With

adoConn.Provider = strDataProvider
adoConn.Properties("Data Source").Value = strTNSNAME_entry
adoConn.Properties("User ID").Value = strUserName
adoConn.Properties("Password").Value = strPwd
adoConn.Open

adoRS.Open strSql, adoConn

With ThisWorkbook.Worksheets(strDestWkshtName) _
  .QueryTables.Add( _
     Connection:=adoRS, _
     Destination:=Range(strDestRngStartCell))
 
  .Name = strQryTableName
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlOverwriteCells
  .SavePassword = True
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
  .Refresh BackgroundQuery:=False

End With
 
  adoRS.Close
  adoConn.Close
  Set adoConn = Nothing
  Set adoRS = Nothing

End Sub
'------end of code---------

This code exerpt engages the query code:
************************************
  'Run the query
  GetDataFromDatabase _
     strDestWkshtName:=[DestWkstName].Value, _
     strDestRngStartCell:=[DestStartCellRef].Value, _
     strQryTableName:=[DestDataRangeName].Value, _
     strDataProvider:=[DBDataProvider].Value, _
     strSql:=[SQLCode].Value, _
     strTNSNAME_entry:=[DBDataSource].Value, _
     strUserName:=UserName, _
     strPwd:=Pwd
'------end of code---------

The values in square brackets are the named ranges I referred to above
The DBDataProvider I use is: MSDAORA
The DBtaSource is the TNSNAMES.ora file entry specific to the database you
are querying.

The above has been exerpted and edited from a complete model I use that
holds many queries (MS Access and Oracle) that are selected via option
buttons and then executed.

Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro