Importing data from an Excel file on a web server using ADO/VBA
 

I'm building an Excel-based application that will be accessible only
by a pre-defined list of users. This list will be kept on my web
server in an Excel file. I need to import data from this file into the
client application during startup using VBA.

I've tried queries from within Excel (both database queries and Import
Data). The database query wouldn't allow an internet connection. The
data import worked, but asks for a user name and password for the web
site in a popup. There doesn't seem to be a way to embed this name
and password in the import.

I have an ADO connection that works when using a local Excel file, but
not when targeting the web-based file.

strSourceFile = "http://www.mysite.com/myfile.xls"
Set cn = New ADODB.Connection
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; _
DriverId=790;ReadOnly=True;" & "DBQ=" & strSourceFile & ";"

I was going to explore going down the RDS path, but I've read that
RDS is no longer supported by Microsoft.

I've also taken a look at other postings very similar to this from mid
2002, and they suggested copying the Excel file to the local
directory, importing data into the application, and then deleting the
Excel file. Not an ideal solution, but one I would employ if there
are no other alternatives.

Any other suggestions out there from ADO/VBA gurus?

Thanks,
Dean Frazier