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?
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?