How to retrieve value from MSSQL table into a cell

[tek.hong]

Hi all

Is it possible to develop a custom function that I could use to
retrieve a field value from a MSSQL database into a cell?

Example # 1

@DBSEEK('00001','Customer','Name')
which would retrieve the custome name of customer ID, 00001 from the
customer table in a MSSQL database.

@DBSUM('Employee','Salary','Department=[ADMIN]')
which would get the sum of salaries of all employees of
department="ADMIN" from the employee table of a MSSQL database


Your advice most appreciated

Best Regards

 

[Nick Hodge]

You could probably do this more simply by using ODBC (MSQuery) to the SQL
database like so

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

 

[tek.hong]

Thanks Nick

Is there a way I could automate this by writing a VBA function that
will access the MSSQL table programatically? Your advise most
appreciated. Thanks

Best Regards

You could probably do this more simply by using ODBC (MSQuery) to the SQL
database like so

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

Hi all

Is it possible to develop a custom function that I could use to
retrieve a field value from a MSSQL database into a cell?

Example # 1

@DBSEEK('00001','Customer','Name')
which would retrieve the custome name of customer ID, 00001 from the
customer table in a MSSQL database.

@DBSUM('Employee','Salary','Department=[ADMIN]')
which would get the sum of salaries of all employees of
department="ADMIN" from the employee table of a MSSQL database


Your advice most appreciated

Best Regards

 

[Nick Hodge]

Sure

I'm not quite sure how far this gets you but if you record a macro doing the
steps outlined in the article, you can adapt this. Alternatively, bear in
mind that once the workbook has a connection to the datasource (whatever
that may be) you can simply re-query from the database by pressing the
Refresh button on the data menu in Excel. (By default this is saved with the
workbook)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

Thanks Nick

Is there a way I could automate this by writing a VBA function that
will access the MSSQL table programatically? Your advise most
appreciated. Thanks

Best Regards

You could probably do this more simply by using ODBC (MSQuery) to the SQL
database like so

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

Hi all

Is it possible to develop a custom function that I could use to
retrieve a field value from a MSSQL database into a cell?

Example # 1

@DBSEEK('00001','Customer','Name')
which would retrieve the custome name of customer ID, 00001 from the
customer table in a MSSQL database.

@DBSUM('Employee','Salary','Department=[ADMIN]')
which would get the sum of salaries of all employees of
department="ADMIN" from the employee table of a MSSQL database


Your advice most appreciated

Best Regards

 

[Tim Williams]

If you understand ADO then you could write a custom function to do what you
need.
You might have to spend some time parsing the parameters to create the SQL
though, depending on how generic you need it to be.

Tim

--
Tim Williams
Palo Alto, CA

Thanks Nick

Is there a way I could automate this by writing a VBA function that
will access the MSSQL table programatically? Your advise most
appreciated. Thanks

Best Regards

You could probably do this more simply by using ODBC (MSQuery) to the SQL
database like so

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

Hi all

Is it possible to develop a custom function that I could use to
retrieve a field value from a MSSQL database into a cell?

Example # 1

@DBSEEK('00001','Customer','Name')
which would retrieve the custome name of customer ID, 00001 from the
customer table in a MSSQL database.

@DBSUM('Employee','Salary','Department=[ADMIN]')
which would get the sum of salaries of all employees of
department="ADMIN" from the employee table of a MSSQL database


Your advice most appreciated

Best Regards