Query Oracle from Excel

[J-Man]

I am very experienced with Excel VBA...so, don't hold back on any VBA coding
responses ;-)

What I am NOT familiar with, is connecting to Oracle. In particular, I want
to use a value in a Cell in Excel to query an Oracle Database.

Can someone give me directions on what I need to do? If there is more than
one way of doing this...I'd like to hear them all (ADO, ODBC, OLE, or
whatever).

Thanks in advance!!!!

[Ron Coderre]

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


"J-Man" wrote:

> I am very experienced with Excel VBA...so, don't hold back on any VBA coding
> responses ;-)
>
> What I am NOT familiar with, is connecting to Oracle. In particular, I want
> to use a value in a Cell in Excel to query an Oracle Database.
>
> Can someone give me directions on what I need to do? If there is more than
> one way of doing this...I'd like to hear them all (ADO, ODBC, OLE, or
> whatever).
>
> Thanks in advance!!!!

[J-Man]

That's probably what I'm looking for...but, my experience with doing this
type of connection is NIL!!! Can you expain the following:

- The DBDataProvider I use is: MSDAORA
- The DBDataSource is the TNSNAMES.ora file entry specific to the database
you are querying.

What exactly is DBDataProvider and DBDataSource? How do I know these?

Thanks again!


"Ron Coderre" wrote:

> 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
>
>
> "J-Man" wrote:
>
> > I am very experienced with Excel VBA...so, don't hold back on any VBA coding
> > responses ;-)
> >
> > What I am NOT familiar with, is connecting to Oracle. In particular, I want
> > to use a value in a Cell in Excel to query an Oracle Database.
> >
> > Can someone give me directions on what I need to do? If there is more than
> > one way of doing this...I'd like to hear them all (ADO, ODBC, OLE, or
> > whatever).
> >
> > Thanks in advance!!!!

[Ron Coderre]

Check this website and explore the site and ALL of the links.

http://msdn.microsoft.com/library/de...leprovspec.asp

Does that help?

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

XL2002, WinXP-Pro


"J-Man" wrote:

> That's probably what I'm looking for...but, my experience with doing this
> type of connection is NIL!!! Can you expain the following:
>
> - The DBDataProvider I use is: MSDAORA
> - The DBDataSource is the TNSNAMES.ora file entry specific to the database
> you are querying.
>
> What exactly is DBDataProvider and DBDataSource? How do I know these?
>
> Thanks again!
>
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "J-Man" wrote:
> >
> > > I am very experienced with Excel VBA...so, don't hold back on any VBA coding
> > > responses ;-)
> > >
> > > What I am NOT familiar with, is connecting to Oracle. In particular, I want
> > > to use a value in a Cell in Excel to query an Oracle Database.
> > >
> > > Can someone give me directions on what I need to do? If there is more than
> > > one way of doing this...I'd like to hear them all (ADO, ODBC, OLE, or
> > > whatever).
> > >
> > > Thanks in advance!!!!

[J-Man]

Yes, it's starting to make a little more sense. Is there anything I have to
do on the Oracle side to get this to work?

Thanks....AGAIN!


"Ron Coderre" wrote:

> Check this website and explore the site and ALL of the links.
>
> http://msdn.microsoft.com/library/de...leprovspec.asp
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "J-Man" wrote:
>
> > That's probably what I'm looking for...but, my experience with doing this
> > type of connection is NIL!!! Can you expain the following:
> >
> > - The DBDataProvider I use is: MSDAORA
> > - The DBDataSource is the TNSNAMES.ora file entry specific to the database
> > you are querying.
> >
> > What exactly is DBDataProvider and DBDataSource? How do I know these?
> >
> > Thanks again!
> >
> >
> > "Ron Coderre" wrote:
> >
> > > 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
> > >
> > >
> > > "J-Man" wrote:
> > >
> > > > I am very experienced with Excel VBA...so, don't hold back on any VBA coding
> > > > responses ;-)
> > > >
> > > > What I am NOT familiar with, is connecting to Oracle. In particular, I want
> > > > to use a value in a Cell in Excel to query an Oracle Database.
> > > >
> > > > Can someone give me directions on what I need to do? If there is more than
> > > > one way of doing this...I'd like to hear them all (ADO, ODBC, OLE, or
> > > > whatever).
> > > >
> > > > Thanks in advance!!!!

[Ron Coderre]

>>Is there anything I have to do on the Oracle side to get this to work?<<

Nope...so long as you use the correct TNSNAMES.ORA entry, username and
password.

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

XL2002, WinXP-Pro


"J-Man" wrote:

> Yes, it's starting to make a little more sense. Is there anything I have to
> do on the Oracle side to get this to work?
>
> Thanks....AGAIN!
>
>
> "Ron Coderre" wrote:
>
> > Check this website and explore the site and ALL of the links.
> >
> > http://msdn.microsoft.com/library/de...leprovspec.asp
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "J-Man" wrote:
> >
> > > That's probably what I'm looking for...but, my experience with doing this
> > > type of connection is NIL!!! Can you expain the following:
> > >
> > > - The DBDataProvider I use is: MSDAORA
> > > - The DBDataSource is the TNSNAMES.ora file entry specific to the database
> > > you are querying.
> > >
> > > What exactly is DBDataProvider and DBDataSource? How do I know these?
> > >
> > > Thanks again!
> > >
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "J-Man" wrote:
> > > >
> > > > > I am very experienced with Excel VBA...so, don't hold back on any VBA coding
> > > > > responses ;-)
> > > > >
> > > > > What I am NOT familiar with, is connecting to Oracle. In particular, I want
> > > > > to use a value in a Cell in Excel to query an Oracle Database.
> > > > >
> > > > > Can someone give me directions on what I need to do? If there is more than
> > > > > one way of doing this...I'd like to hear them all (ADO, ODBC, OLE, or
> > > > > whatever).
> > > > >
> > > > > Thanks in advance!!!!

[J-Man]

Ron, thanks for your help...this is REALLY helping me!!!!

Now, let's say that Oracle resides on the LAN/WAN somewhere, and I don't
have an Oracle client locally...where/how to I use the TNSNAMES.ORA file?

Thanks!

"Ron Coderre" wrote:

> >>Is there anything I have to do on the Oracle side to get this to work?<<
>
> Nope...so long as you use the correct TNSNAMES.ORA entry, username and
> password.
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "J-Man" wrote:
>
> > Yes, it's starting to make a little more sense. Is there anything I have to
> > do on the Oracle side to get this to work?
> >
> > Thanks....AGAIN!
> >
> >
> > "Ron Coderre" wrote:
> >
> > > Check this website and explore the site and ALL of the links.
> > >
> > > http://msdn.microsoft.com/library/de...leprovspec.asp
> > >
> > > Does that help?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "J-Man" wrote:
> > >
> > > > That's probably what I'm looking for...but, my experience with doing this
> > > > type of connection is NIL!!! Can you expain the following:
> > > >
> > > > - The DBDataProvider I use is: MSDAORA
> > > > - The DBDataSource is the TNSNAMES.ora file entry specific to the database
> > > > you are querying.
> > > >
> > > > What exactly is DBDataProvider and DBDataSource? How do I know these?
> > > >
> > > > Thanks again!
> > > >
> > > >
> > > > "Ron Coderre" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "J-Man" wrote:
> > > > >
> > > > > > I am very experienced with Excel VBA...so, don't hold back on any VBA coding
> > > > > > responses ;-)
> > > > > >
> > > > > > What I am NOT familiar with, is connecting to Oracle. In particular, I want
> > > > > > to use a value in a Cell in Excel to query an Oracle Database.
> > > > > >
> > > > > > Can someone give me directions on what I need to do? If there is more than
> > > > > > one way of doing this...I'd like to hear them all (ADO, ODBC, OLE, or
> > > > > > whatever).
> > > > > >
> > > > > > Thanks in advance!!!!

[Tim Williams]

You will need the Oracle client sofware installed on the PC you're running
Excel on.

Tim


"J-Man" <[email protected]> wrote in message
news:[email protected]...
> Ron, thanks for your help...this is REALLY helping me!!!!
>
> Now, let's say that Oracle resides on the LAN/WAN somewhere, and I don't
> have an Oracle client locally...where/how to I use the TNSNAMES.ORA file?
>
> Thanks!
>
> "Ron Coderre" wrote:
>
>> >>Is there anything I have to do on the Oracle side to get this to
>> >>work?<<
>>
>> Nope...so long as you use the correct TNSNAMES.ORA entry, username and
>> password.
>>
>> ***********
>> Regards,
>> Ron
>>
>> XL2002, WinXP-Pro
>>
>>
>> "J-Man" wrote:
>>
>> > Yes, it's starting to make a little more sense. Is there anything I
>> > have to
>> > do on the Oracle side to get this to work?
>> >
>> > Thanks....AGAIN!
>> >
>> >
>> > "Ron Coderre" wrote:
>> >
>> > > Check this website and explore the site and ALL of the links.
>> > >
>> > > http://msdn.microsoft.com/library/de...leprovspec.asp
>> > >
>> > > Does that help?
>> > >
>> > > ***********
>> > > Regards,
>> > > Ron
>> > >
>> > > XL2002, WinXP-Pro
>> > >
>> > >
>> > > "J-Man" wrote:
>> > >
>> > > > That's probably what I'm looking for...but, my experience with
>> > > > doing this
>> > > > type of connection is NIL!!! Can you expain the following:
>> > > >
>> > > > - The DBDataProvider I use is: MSDAORA
>> > > > - The DBDataSource is the TNSNAMES.ora file entry specific to the
>> > > > database
>> > > > you are querying.
>> > > >
>> > > > What exactly is DBDataProvider and DBDataSource? How do I know
>> > > > these?
>> > > >
>> > > > Thanks again!
>> > > >
>> > > >
>> > > > "Ron Coderre" wrote:
>> > > >
>> > > > > 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
>> > > > >
>> > > > >
>> > > > > "J-Man" wrote:
>> > > > >
>> > > > > > I am very experienced with Excel VBA...so, don't hold back on
>> > > > > > any VBA coding
>> > > > > > responses ;-)
>> > > > > >
>> > > > > > What I am NOT familiar with, is connecting to Oracle. In
>> > > > > > particular, I want
>> > > > > > to use a value in a Cell in Excel to query an Oracle Database.
>> > > > > >
>> > > > > > Can someone give me directions on what I need to do? If there
>> > > > > > is more than
>> > > > > > one way of doing this...I'd like to hear them all (ADO, ODBC,
>> > > > > > OLE, or
>> > > > > > whatever).
>> > > > > >
>> > > > > > Thanks in advance!!!!