Excel / Sharepoint / Query Question

[worktolive]

Hi, I am trying to create a query from spreadsheet "A" to pull in rows of select data from spreadsheet "B" using import external data query functionality. The kicker, spreadsheet B is stored within sharepoint. I can do this easily if spreadsheet "B" is on a drive (or shared drive). But am having problems referring to it when it is stored within Sharepoint.

I can create a macro to open the file within sharepoint because I have a URL locating it (example: http://sharepoint.xxx.com/sites/blah/B.xls"). But I can't seem to figure out how to run the query either through the menu using "/data/external data query/new database query" (can't ever get to a web location) or VBA by recording a macro that captures the steps to execute a query and editing the section that starts with "Selection.QueryTable .Connection= ...." to try to point at the HTTP location.

WHY am I doing this? Spreadsheet B has rows of items. Spreadsheet A needs to display a subset of qualifying rows on a tab. Spreadsheet A needs to update the rows in the tab each time it opens. They need to be exactly the set of qualifying rows from spreadsheet B, this is not an appending data exercise. Spreadsheet B sits protected (read only) in Sharepoint. I must do this in Excel, no other tools are allowed at this point.

I am running Win XP, Excel 2002, and SharePoint Services 2.0.

Any eye opening suggestions?

 

[worktolive]

Anyone?

 

[worktolive]

Well, for any that may happen across this with a similar problem. I never did figure out how to do this with the query functionality (as described above). What I am doing to address the problem is as follows:

- Use VBA to open excel spreadsheet from sharepoint (file B)
- Used cell references in file A to pull a range of cells from file B (about 200 rows).
- Used /data/filter/advanced filter to only show the rows I want
- Taking various steps in vba to try to make this stable (ensure forumlas aren't overwritten, file B is opened read-only, and so on)

Sure would have been a lot cleaner getting the query functionality working, but hey, this gets me to something I can deliver.