ODBC reading excel file over internet

I am trying to read an MS Excel 2003 file using ODBC over the internet.  I am trying to use either the Excel driver or the Jet driver, but neither one seems to work over the internet (the only address I have for the file is an http address).  Does anyone have any idea how to do this?  I know that I have access to the file, because I can use 'workbooks.open' to open it, but that is very slow, so I want to convert it to ODBC.  Any help or ideas to try would be appreciated.

In theory it should work, but why not use a web query, Data>External Data>New web query... Once set up you can just refresh as the data changes.

This will need the data saved as HTML, or some other web technology, but HTML is a native format in 2003, so that should be no issue

If you wish to persevere with ODBC perhaps we may need more information on what file you have, operating system on the server and host, etc. This way there may be an easier way to accomplish what you are doing.

The data in the external spreadsheet is controlled by someone else, and when they update it, they post a copy on our intranet.  It would be difficult, at best, to train them to save it as an HTML type file (I believe I could do the same with CSV, but have the same user difficulty).  In addition, I am using the data in the controlled spreadsheet in macros in my spreadsheet, I am not putting it into my spreadsheet in any area where the user would be able to see it.  Therefore, having the user save the controlled spreadsheet as HTML really doesn't make sense to do.  My spreadsheet needs to be able to read data from the controlled spreadsheet on the web site.

In terms of the OS, I am sure it is an MS Windows 2003 server, but that is about all the info I have access to.  I am sure Excel is NOT loaded (and WON'T be loaded) on the server.  The web site is fairly open, so any of the users of my spreadsheet should be able to open / read the controlled spreadsheet.

An option I have just thought of would be to create a hidden page within my spreadsheet that contains links to the data in the controlled spreadsheet.  I have done this with individual cells, but I think in this case, it would need to link to an entire spreadsheet tab.  Is it possible to do this?  The other thing I would like to have happen in this scenario is I wouldn't want the user prompted to update the links when they open the page.  Is that possible?

I think you are running in the wrong direction with links. You should be able to access it. It is not could to run excel on the server but if it is an intranet you can likley access this through a share on that server, without using http at all. You may even get your IT department to map the share to a drive letter.

Truly all you need is permission. I suspect this is the route of the issue you are having anyhow.

The share would look like \\[YourServerName]\MyFolder\MyFile.xls

This would be the way to go

Nick,

 I know the 'best' way to go would be to get the full share name.  However, due to security reasons, I won't be able to get that (not to mention that they want to be able to move the web server to a different server at any time).  Therefore, I cannot use the full share name.

The links idea was just a possibility that I had thought of.  I didn't think it would be the best way, either, but I can't use the full path name, and I can't seem to get the ODBC to work using a url.

If permissions were a problem, why would I be able to open the file using workbooks.open, but not read it using ODBC?

 Pgmr:  Why don't you just open the workbook in Excel?  You can save it locally for performance reasons.

Sub GetWebBook()
   
    Dim wb As Workbook
    Set wb = Workbooks.Open("http://www.dailydoseofexcel.com/excel/WebBasedBook.xls")
    wb.SaveAs "WebBasedBook.csv", xlCSV
   
End Sub

Opening a workbook from a web server is slow, but if you only have to do it, say, once per day, it should be workable.     

The code is currently opening the file as you describe.  The main reason we are implementing this code is for speed reasons.  Based on that, I am trying to find the absolute fastest way to read this data.  I had done some testing on my local PC and known network locations (with regular drive letters), and found the ODBC access to be significantly faster than opening the file.

Any other ideas for reading one Excel file from a macro within another?