[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

extract query in macro to excel

folks

on my oracle db i have  a table called "sql_statement", this includes the 2 columns called

table_name and query_defintion and queryname i.e.

table_name   query_defintion                             queryname
countries       "where continent is europe"           europe  

i need to have my excel macro connect to the oracle db and then extract from the table called "countries" and extract the values of this object using the "query_defintion" from the "sql_statement" table called "where continent is europe"  

i.e.

countries
belgium
germany
italy
0
rutgermons
Asked:
rutgermons
  • 3
1 Solution
 
rutgermonsAuthor Commented:
hope anyone can assist on this
0
 
ButlerTechnologyCommented:
I am not sure, but I think this coding block may get you moving in the right directions.  It shows you how to connect to an MS SQL server and extract information from a table.  You will need to adjust the connection string to work with Oracle.  I put the output to the worksheet, but you should be able to develop a string based on your description.

Tom

Public Sub Download()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim x As Integer
cn.ConnectionString = "Provider=SQLOLEDB;Server=LF-SQL01;Database=FostersTentRentals;user ID=ftr;password=P@ssw0rd;"
cn.Open
rs.ActiveConnection = cn
rs.Source = "Select * from employee"

rs.Open
While Not rs.EOF
  ActiveCell.Range("A1").Value = rs.Fields("EmployeeID")
  ActiveCell.Range("B1").Value = rs.Fields("FirstName")
  ActiveCell.Range("C1").Value = rs.Fields("LastName")
  rs.MoveNext
  ActiveCell.Offset(1, 0).Activate
Wend

rs.Close
cn.Close
End Sub

Open in new window

0
 
rutgermonsAuthor Commented:
thanks! butler, this gets me nearly there
0
 
rutgermonsAuthor Commented:
not the exact answer but got me in the correct direction,thaks
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now