I am writing VBA in Excel (Office 365) to retrieve data from external sources via the ActiveX Data Objects (ADO) 6.1 library
Code works blisteringly fast to retrieve data data from XLS(X) and CSV files, but I have now encountered what appears to be XLS (i.e. this is the file extension), but is actually a 'Web Archive' file which the Excel application can handle (albeit with an error message that the file contents don't match the extension), but which ADO complains that 'The external table is not in the expected format'
This is the header of the document when opened in Notepad++
MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="----=_NextPart_01C3AACE.2206ED10"
This document is a Web archive file. If you are seeing this message, this means your browser or editor doesn't support Web archive files. For more information on the Web archive format, go to http://officeupdate.microsoft.com/office/webarchive.htm.
------=_NextPart_01C3AACE.2206ED10
Content-Location: http://localhost/excel.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="utf-8"
<html xmlns:o=3D"urn:schemas-microsoft-com:office:office"
xmlns:x=3D"urn:schemas-microsoft-com:office:excel"
xmlns:v=3D"urn:schemas-microsoft-com:vml"
xmlns=3D"http://www.w3.org/TR/REC-html40">
If I change the extension to XLSX via Windows Explorer, Excel tells me the file cannot be opened at all
From what I understand, this is not really an Excel file at all, it is a file created in a 3rd-party system which Excel can interpret only if told it's an XLS file
Is there any way to interpret this file in VBA without using `Workbooks.Open' to rely on the Excel application to make sense of the contents?
It seems that it might be possible to read it in as XML and process the data that way, but I don't even know where to begin with that
