Excel / Sharepoint / Query Question

worktolive

New Member
Joined
Nov 30, 2005
Messages
3
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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

worktolive

New Member
Joined
Nov 30, 2005
Messages
3
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,162
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top