Exporting an Excel Spreadsheet to HTML via Visual Basic
The following syntax is failing when run as Open Event Procedure within an Access form.
I suspect it might be the "8" spreadsheet type and our Access 2002 being dated compared to the Excel 2010 software used to create the xlsx file.
But I'm hoping it's something else.
The tblCOIvendors is a simple design of just two fields: Vendor (text=255) and ExpDate (as Date).
I get the "Run-time error 3274, External table is not in the expected format."
Rem Copy scanfield's "2013 Certificates of Insurance from Vendors.xlsx" to COIvendors.htm
Rem
DoCmd.TransferSpreadsheet acImport, 8, _
"tblCOIvendors", "\\bordensrv\users\scanfie
False, "A6:B99"
DoCmd.OutputTo acOutputQuery, "qryCOIvendors", acFormatHTML, "\\bordensrv\intranet\repo
Rem
I suspect it might be the "8" spreadsheet type and our Access 2002 being dated compared to the Excel 2010 software used to create the xlsx file.
But I'm hoping it's something else.
The tblCOIvendors is a simple design of just two fields: Vendor (text=255) and ExpDate (as Date).
I get the "Run-time error 3274, External table is not in the expected format."
Rem Copy scanfield's "2013 Certificates of Insurance from Vendors.xlsx" to COIvendors.htm
Rem
DoCmd.TransferSpreadsheet acImport, 8, _
"tblCOIvendors", "\\bordensrv\users\scanfie
False, "A6:B99"
DoCmd.OutputTo acOutputQuery, "qryCOIvendors", acFormatHTML, "\\bordensrv\intranet\repo
Rem
Oops ... sorry, I think you're running Access 2002. That constant would not be available in 2002.
You would need to get a different version of the spreadsheet, one that is compliant with your version of Office - or upgrade your Office version (which is what you really should do).
You would need to get a different version of the spreadsheet, one that is compliant with your version of Office - or upgrade your Office version (which is what you really should do).
ASKER
I opened the spreadsheet in Excel 2003 and saved it as XLS and changed the file code to 8 as follows:
DoCmd.TransferSpreadsheet acImport, 8, _
"tblCOIvendors", "\\bordensrv\users\scanfie
False, "A6:B99"
DoCmd.OutputTo acOutputQuery, "qryCOIvendors", acFormatHTML, "\\bordensrv\intranet\repo
The error is "Run-time error 3270; Property Not Found".
The Debugger highlights the first line, so I assume that's where the error is.
Hopefully it's a typo that I'm just not noticing.
From within my session, I'm able to open the source Excel file, the Access destination table and the query. So I don't believe that their availability is the issue.
DoCmd.TransferSpreadsheet acImport, 8, _
"tblCOIvendors", "\\bordensrv\users\scanfie
False, "A6:B99"
DoCmd.OutputTo acOutputQuery, "qryCOIvendors", acFormatHTML, "\\bordensrv\intranet\repo
The error is "Run-time error 3270; Property Not Found".
The Debugger highlights the first line, so I assume that's where the error is.
Hopefully it's a typo that I'm just not noticing.
From within my session, I'm able to open the source Excel file, the Access destination table and the query. So I don't believe that their availability is the issue.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have been sidetracked with other issues. I am simply updating my status so the system's robot doesn't deep six by question.
:)
I'll be back ASAP.
S.
:)
I'll be back ASAP.
S.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I followed the lead of the expert and found a related solution.
If so try this:
DoCmd.TransferSpreadsheet acImport, acSpreadsheettypeExcel12, _
"tblCOIvendors", "\\bordensrv\users\scanfie
False, "A6:B99"