Excel - Query Excel Table

[khoonkeat]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I used to query Excel Table (Win XP, Excel 2003 named range) without needing any download ODBC driver or install anything. It is built-in in Excel 2003. How do I do that in Excel 2008 Mac?

Thanks.

kk

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I used to query Excel Table (Win XP, Excel 2003 named range) without needing
any download ODBC driver or install anything. It is built-in in Excel 2003.
How do I do that in Excel 2008 Mac?

Thanks.

kk

I'm not sure I quite understand, but do you want Excel 2008 to access a
named range in another workbook? If so, create a link to the range, the same
way you did in Excel 2003.

 

[khoonkeat]

Dear Bob

Thanks for the prompt reply.

Yes, I would like to access a named range in excel but only certain records. I.e., I am using the query function to filter records that meet my criteria and most important of all is the ability to update it as and when I need to.

Is there a better way of doing this?

Thanks.

kk

 

[Jim Gordon MVP]

Dear Bob

Thanks for the prompt reply.

Yes, I would like to access a named range in excel but only certain records. I.e., I am using the query function to filter records that meet my criteria and most important of all is the ability to update it as and when I need to.

Is there a better way of doing this?

Thanks.

kk

Hi,

If you are using SQL.REQUEST worksheet function it won't work it is not
supported on Mac Excel.

-Jim

--
Jim Gordon
Mac MVP

MVPs are independent experts who are not affiliated with Microsoft.


Visit my blog
http://blog.360.yahoo.com/blog-i7JMeio7cqvhotIUwCzaJWq9

 

[khoonkeat]

Dear Jim

I used MS Query (Win XP, Excel 2003) to construct the query via GUI. Never need to use any function. But the same Excel file when opened with Mac Office Excel 2008, it prompted me that the database component was not available. However, digging around, I uncovered Mac Office also has MS Query. So my question is if I only interested to query Excel "table" with Mac Office Excel, what else do I need?

 

[Jim Gordon MVP]

Dear Jim

I used MS Query (Win XP, Excel 2003) to construct the query via GUI. Never need to use any function. But the same Excel file when opened with Mac Office Excel 2008, it prompted me that the database component was not available. However, digging around, I uncovered Mac Office also has MS Query. So my question is if I only interested to query Excel "table" with Mac Office Excel, what else do I need?

Hi,

You need to do two things.

1. Install an ODBC driver for the data source. ODBC drivers are
available for a variety of different types of data sources.

Two vendors of ODBC drivers for Mac:
http://www.actualtechnologies.com/
http://uda.openlinksw.com/odbc/?gclid=CImcubPRvpUCFQQrFQodRW5pQQ

2. When you name the data source use the exact same name for the data
source (DSN) that you used when you created the query in Windows Excel.

Mac Queries are read only. Table actions are not supported (eg. drop,
append, delete, etc). You can use any sort of select, group by, nested,
and even parameter queries.
http://www.agentjim.com/MVP/Excel/RelationalOffice.htm

They syntax for queries on MS Query Mac is identical to the syntax for
Windows MS Query.

-Jim

--
Jim Gordon
Mac MVP

MVPs are independent experts who are not affiliated with Microsoft.


Visit my blog
http://blog.360.yahoo.com/blog-i7JMeio7cqvhotIUwCzaJWq9