External Data from Sharepoint

[Dan]

Using Excel 2003. I'm bringing in data from Sharepoint via IQY as
shown:

WEB1
SharePointApplication=http://www.helloworld.com:5060/_vti_bin
SharePointApplication=http://www.helloworld.com:5060/_vti_bin
SharePointListName={CD33EA8B-A0B3-4EAF-8BA3-C8BEAD289D5C}
SharePointListView={1B35BCF5-6842-4841-AC41-5D3D18B1676A}


This works great, if I 'double-click' the .iqy file, except I'd like to
take things a step further, but I'm having issues.

One, I'd like the data to come in as static data, not as a data range.
As a range I can't insert a row, sum a column, etc, without first
copying and pasting the data (values) to a second worksheet. Is this
possible, perhaps by way of add'l IQY properties? Excel settings?

Second, I'd like to save the query with the workbook, configured to
auto refresh when the workbook is opened. So, I set the Data Range
properties 'Refresh Data on File Open' and 'Remove External Data ...
Before Saving'. Then I save and close. Then I open, all looks good, but
then 'A connection to sharepoint site cannot be established'. So the
refresh fails. The sharepoint site requires authentication, BTW.
Appreciate feedback immensely. Thanks,

DAN

 

[Don Guillett]

I couldn't get the url to work past helloworld.com. So, where on the site
did you want to go and get info?
In general, you must import the table and then use a macro or an INDIRECT
formula to extract the info to another worksheet. You could put a refresh
macro in the Workbook_Open event of the ThisWorkbook module.

 

[Dan]

Thank you Don for the response.

I should have mentioned the 'helloworld.com' domain is hypothetical. I
changed the name. I'm sorry I didn't mention that.

You have set me straight on one point: after the external data is
imported, I have to process that data to another worksheet. That helps
me to know that.

Second, however, is the issue of 'A connection to sharepoint site
cannot be established', or associated ideas explaining why I can't get
an update when the workbook opens. I'm pretty certain you're suggesting
that's what I'd use the refresh macro for? Is password protection an
issue assuming I would not want to code the credentials in the macro,
would I be prompted?

Thanks a million,

DAN

 

[Don Guillett]

If you are trying to download stock quotes why not just use yahoo.com which
does not require a password to get quotes.

 

[Dan]

Don

I'm not trying to download stock quotes, rather import data from my
company Sharepoint site. Access to the site requires login, and I'm
wondering if the refresh macro can accomodate the required login, or
more acceptable, that the user would be prompted when the Excel
workbook opened and the refresh macro runs.

DAP

 

[Don Guillett]

I have not been able to get the automatic login to work.