How do I access data stored in a SQL server for vlookup function?
 

My goal-- type in a client number in cell A1 and have a client name appear in
cell B1. Want to use a IF function with an embedded Vlookup function in cell
B1. If there is a value greater than .01 in cell A1 than the Vlookup is to
retrieve the name of client. The problem is that the data is stored in a SQL
server. How do I access that information without actually importing it to a
excel worksheet first? The Vlookup function probably cannot handle this, but
I wanted to give you an idea of what it is that I am trying to do.

Hi,

You cannot do it using functions. Post this question in
Micorosoft.public.excel.progrmamming newsgroup.

Alok joshi

"M.Heer" wrote:

My goal-- type in a client number in cell A1 and have a client name appear in
cell B1. Want to use a IF function with an embedded Vlookup function in cell
B1. If there is a value greater than .01 in cell A1 than the Vlookup is to
retrieve the name of client. The problem is that the data is stored in a SQL
server. How do I access that information without actually importing it to a
excel worksheet first? The Vlookup function probably cannot handle this, but
I wanted to give you an idea of what it is that I am trying to do.

You should be able to do this using SQL.REQUEST.

This file might be a help:
http://www.bygsoftware.com/examples/...SqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

You should be able to adapt it to work with Sql Server.

The code is open and commented.


--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


"M.Heer" wrote in message
...
My goal-- type in a client number in cell A1 and have a client name appear
in
cell B1. Want to use a IF function with an embedded Vlookup function in
cell
B1. If there is a value greater than .01 in cell A1 than the Vlookup is to
retrieve the name of client. The problem is that the data is stored in a
SQL
server. How do I access that information without actually importing it to
a
excel worksheet first? The Vlookup function probably cannot handle this,
but
I wanted to give you an idea of what it is that I am trying to do.

Andy Wiggins wrote...
You should be able to do this using SQL.REQUEST.
....

If SQL.REQUEST works, VLOOKUP would be unnecessary since the SQL Select
statement could pull the desired value directly from the SQL Server
table(s).

I tried this and is not working. Although Excel is recognizing the function
when I type it in a cell, SQL.Request is not listed in the functions library.
I am using Excel 2003. In the Excel help under SQL.Request it states that
"you must install the Microsoft Excel ODBC add-in program" and that you can
get that from the microsoft office website. I can only find the ODBC add-in
for Excel 2002 and XP versions. This makes no sense, they would not get rid
of a function but then still have it in excel help right? Any help will be
greatly appreciated!

"Andy Wiggins" wrote:

You should be able to do this using SQL.REQUEST.

This file might be a help:
http://www.bygsoftware.com/examples/...SqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

You should be able to adapt it to work with Sql Server.

The code is open and commented.


--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


"M.Heer" wrote in message
...
My goal-- type in a client number in cell A1 and have a client name appear
in
cell B1. Want to use a IF function with an embedded Vlookup function in
cell
B1. If there is a value greater than .01 in cell A1 than the Vlookup is to
retrieve the name of client. The problem is that the data is stored in a
SQL
server. How do I access that information without actually importing it to
a
excel worksheet first? The Vlookup function probably cannot handle this,
but
I wanted to give you an idea of what it is that I am trying to do.

I understand that. The problem is that in order to have the SQL.REQUEST
function work you need to have Excel ODBC installed and I can't find where to
get that for Excel 2003 version. On the microsoft website they do have the
download available, but it is for Excel 2002 and XP versions-- will not work
on Excel 2003 version. This makes no sense because it is in Excel 2003 Help
that it states "If this function is not available, you must install the
Microsoft Excel ODBC add-in program. You can install the add-in from the
Microsoft Office Web site." Yet I can't find it on the website and it is not
already installed in Excel 2003 version. ??????????

"Harlan Grove" wrote:

Andy Wiggins wrote...
You should be able to do this using SQL.REQUEST.
....

If SQL.REQUEST works, VLOOKUP would be unnecessary since the SQL Select
statement could pull the desired value directly from the SQL Server
table(s).

"M.Heer" wrote...
I understand that. The problem is that in order to have the SQL.REQUEST
function work you need to have Excel ODBC installed and I can't find
where to get that for Excel 2003 version. On the microsoft website they
do have the download available, but it is for Excel 2002 and XP
versions-- will not work on Excel 2003 version. . . .

And are you *CERTAIN* of that last statement?!

In fact, it's PRECISELY the 2002/XP version you need since it hasn't changed
since Excel 95. [Look under the hood - it's XLM code calling an external DLL
that still carries the Excel 5.0 version label.]

The only mystery here is why, in its infinite wisdom, Microsoft chose to
drop the ODBC add-in from the Office product CD.

I have found that the SQL.REQUEST function is not an option in Excel 2003 any
longer. Is there another function that will access data stored in an SQL
server?

"Harlan Grove" wrote:

"M.Heer" wrote...
I understand that. The problem is that in order to have the SQL.REQUEST
function work you need to have Excel ODBC installed and I can't find
where to get that for Excel 2003 version. On the microsoft website they
do have the download available, but it is for Excel 2002 and XP
versions-- will not work on Excel 2003 version. . . .

And are you *CERTAIN* of that last statement?!

In fact, it's PRECISELY the 2002/XP version you need since it hasn't changed
since Excel 95. [Look under the hood - it's XLM code calling an external DLL
that still carries the Excel 5.0 version label.]

The only mystery here is why, in its infinite wisdom, Microsoft chose to
drop the ODBC add-in from the Office product CD.

As you have found out, this add-in is no longer included with Microsoft
Excel. You can get it from Microsoft by following this link -
http://office.microsoft.com/downloads/2002/xlodbc.aspx

This is where Uncle Bill isn't being too helpful :-(
Here's how to get the files::
Open the xlodbc.exe using, say, Winzip.
Copy the files to a convenient location.
From within Excel, browse to the add-in file and load it as normal

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


"M.Heer" wrote in message
...
I have found that the SQL.REQUEST function is not an option in Excel 2003
any
longer. Is there another function that will access data stored in an SQL
server?

"Harlan Grove" wrote:

"M.Heer" wrote...
I understand that. The problem is that in order to have the SQL.REQUEST
function work you need to have Excel ODBC installed and I can't find
where to get that for Excel 2003 version. On the microsoft website they
do have the download available, but it is for Excel 2002 and XP
versions-- will not work on Excel 2003 version. . . .

And are you *CERTAIN* of that last statement?!

In fact, it's PRECISELY the 2002/XP version you need since it hasn't
changed
since Excel 95. [Look under the hood - it's XLM code calling an external
DLL
that still carries the Excel 5.0 version label.]

The only mystery here is why, in its infinite wisdom, Microsoft chose to
drop the ODBC add-in from the Office product CD.