Solved

extract query in macro to excel

Posted on 2013-10-26
4
347 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
  • 3
4 Comments
 

Author Comment

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

Accepted Solution

by:
ButlerTechnology earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now