Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

extract query in macro to excel

Posted on 2013-10-26
4
Medium Priority
?
386 Views
Last Modified: 2013-10-27
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
Comment
Question by:rutgermons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 

Author Comment

by:rutgermons
ID: 39603247
hope anyone can assist on this
0
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 1000 total points
ID: 39603693
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
 

Author Comment

by:rutgermons
ID: 39604101
thanks! butler, this gets me nearly there
0
 

Author Closing Comment

by:rutgermons
ID: 39604362
not the exact answer but got me in the correct direction,thaks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question