Unable to import excel file in vbscript due to error: External table is not in the expected format
I'm unable to import an excel sheet that was export by:
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "attachment; filename="example.xls"
I'm getting the following error:
"开发者_Python百科Microsoft JET Database Engine error '80004005'
External table is not in the expected format."
Please assist, Thanks!
I'm guessing that you aren't converting the HTML output to a pure xls workbook. All you are doing is creating a way to get the file to save as xls in order to open it in excel, not providing access with a valid file with which it can work. Try saving the output as a pure excel workbook and see how access behaves after that.
It's going to depend a lot on how you are exporting the file as well as how you are trying to import the file. In the absence of posted code for the export, I'll include both sides as used by a project we have here at work.
For context, these snippets are from to VBScript files that are run locally that import/export the files to the server.
Export Script
In addition to using the content type/headers you posted, the HTML has a few special requirements to be recognized by Excel as a spreadsheet. Here's what some of the header function looks like:
Function XLSHeader()
XLSHeader = "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">" & vbNewLine
XLSHeader = XLSHeader & "<head>" & vbNewLine & "<style>" & vbNewLine
XLSHeader = XLSHeader & "<!--table" & vbNewLine
XLSHeader = XLSHeader & "@page" & vbNewLine
XLSHeader = XLSHeader & "{mso-header-data:""&CRegistrationList\000A"
XLSHeader = XLSHeader & "Printed\: &D\000APage &P"";}" & vbNewLine
XLSHeader = XLSHeader & "br" & vbNewLine
XLSHeader = XLSHeader & "{mso-data-placement:same-cell;}" & vbNewLine
XLSHeader = XLSHeader & "-->" & vbNewLine
XLSHeader = XLSHeader & "td {border:1px solid black; border-collapse:collapse;}" & vbNewLine
'-- other sheet-specific styles and formatting went here --'
XLSHeader = XLSHeader & "</style>" & vbNewLine
XLSHeader = XLSHeader & "<!--[if gte mso 9]><xml>" & vbNewLine
XLSHeader = XLSHeader & "<x:ExcelWorkbook>" & vbNewLine
XLSHeader = XLSHeader & "<x:ExcelWorksheets>" & vbNewLine
XLSHeader = XLSHeader & "<x:ExcelWorksheet>" & vbNewLine
XLSHeader = XLSHeader & "<x:Name>RegistrationList</x:Name>" & vbNewLine
XLSHeader = XLSHeader & "<x:WorksheetOptions>" & vbNewLine
XLSHeader = XLSHeader & "<x:Print>" & vbNewLine
XLSHeader = XLSHeader & "<x:ValidPrinterInfo/>" & vbNewLine
XLSHeader = XLSHeader & "</x:Print>" & vbNewLine
XLSHeader = XLSHeader & "</x:WorksheetOptions>" & vbNewLine
XLSHeader = XLSHeader & "</x:ExcelWorksheet>" & vbNewLine
XLSHeader = XLSHeader & "</x:ExcelWorksheets>" & vbNewLine
XLSHeader = XLSHeader & "</x:ExcelWorkbook>" & vbNewLine
XLSHeader = XLSHeader & "</xml><![endif]-->" & vbNewLine
XLSHeader = XLSHeader & "</head>" & vbNewLine & "<body>" & vbNewLine
XLSHeader = XLSHeader & "<table>" & vbNewLine
XLSHeader = XLSHeader & "<tr>" & vbNewLine
'-- all the <th> went here, defining the header row --'
XLSHeader = XLSHeader & "</tr>" & vbNewLine
End Function
'-- Other excel options were defined in the <head> above, but have been stripped to make this code block a little shorter --'
The rest of the table was written out as usual. Some important things to note:
- For whatever reason, Excel is finicky about the CSS it does and doesn't support. I did have to put all the CSS in the
<head>as there were too many odd issues with external style sheets. - There are all sorts of special classes you can add to the
<td>'s to turn on/off various Excel functions (like locking the cell to prevent editing) - Close out the table and HTML as usual.
- There should be no other content than the header, the table, and the proper closing stuff.
Import Script
How you import it will depend on what format it was saved in. Some of the people using our system end up saving stuff in the Excel 2007/2010 format, and some save it back in the Excel <= 2003 format. So the import script starts with:
'Connection string info at http://www.connectionstrings.com/excel-2007
If Right(objFile.Name, 4) = ".xls" Or Right(objFile.Name, 5) = ".xlsb" Then
strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 12.0;HDR=YES"";"
strSQL = "SELECT * FROM [RegistrationList$] ORDER BY EmployeeNumber;"
ElseIf Right(objFile.Name, 5) = ".xlsx" Then
strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
strSQL = "SELECT * FROM [RegistrationList$] ORDER BY EmployeeNumber;"
Else
'-- Error handling code went here --'
End If
Note that the "table name" is the name of the sheet in the workbook. You theoretically can access it without the name, but I was not able to get it to work in practice with the MS reference code.
From here on out you can treat it like any other data source
Set objExcel = CreateObject("ADODB.Recordset")
objExcel.Open strSQL, strExcelConn, adOpenForwardOnly, adLockReadOnly, adCmdText
This does assume that you've got a header row, and that it is at the top of the sheet. (that's part of why we locked down the sheets a little bit more than we might have wanted - people were improperly sorting the sheets and the "header" was ending up in the middle of the sheet...)
We also had to add in some more error handling code for when people saved the sheets back as HTML with a .xls extension, and not as an actual Excel workbook. That's part of why we have it as two scripts - some parts have not yet been automated.
加载中,请稍侯......
精彩评论