Placing query results in a cell

  • Thread starter Thread starter Tangier
  • Start date Start date
T

Tangier

I have a project where I can connect to a database, construct a query
within a VBA module:

mysql01 = " select ChildID, ChildName "
mysql02 = " from tblChild "

Now suppose I want to put the results of the query anywhere in the
spreadhseet, i.e. in a specific cell. How do I do that?
 
I have a project where I can connect to a database, construct a query
within a VBA module:

mysql01 = " select ChildID, ChildName "
mysql02 = " from tblChild "

Now suppose I want to put the results of the query anywhere in the
spreadhseet, i.e. in a specific cell. How do I do that?
Click to expand...


I don't know how you'd do it in Excel, but in Resolver One
(http://www.resolversystems.com/), which I work on, you could use the
ADO.NET data access APIs to run that query. Then you could put the
data that comes back into a cell (or cells), and have formulae that
use that data in the grid. Something like this:


from System.Data.SqlClient import SqlConnection

connection = SqlConnection('Data Source=your db server; Initial
Catalog=your database; other details...')
connection.Open()
command = connection.CreateCommand()
command.CommandText = 'select ChildID, ChildName from tblChild'
reader = command.ExecuteReader()

destinationSheet = workbook['Children Data']
row = 2

while reader.Read():
destinationSheet['ChildID', row] = reader['ChildID']
destinationSheet['ChildName', row] = reader['ChildName']
row += 1

reader.Close()
connection.Close()



Resolver One is scripted in IronPython, which means you can use .NET
objects as well as Python code integrated with your spreadsheet data.
 
Back
Top