Solved

extract query in macro to excel

Posted on 2013-10-26
4
382 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 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

617 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