VBA工具从给定电子表格确定SpreadsheetType [英] VBA tool to determine SpreadsheetType from given spreadsheet

问题描述

我希望使用 TransferSpreadsheet 命令将电子表格链接或导入到Access2010。
它要求 SpreadsheetType （从 AcSpreadSheetType 枚举中提取）。
什么是Access VBA工具从给定的电子表格文件名确定SpreadsheetType？
也许唯一的方法是检查文件扩展名并进行查找？
如果您以前已经解决了此问题，请分享。

I wish to use the TransferSpreadsheet command to link or import a spreadsheet to Access 2010. It requires a value for SpreadsheetType (drawn from the AcSpreadSheetType enumeration). What is the Access VBA tool to determine SpreadsheetType from given spreadsheet filename? Maybe the only means is to examine the file extension and do a lookup? If you've solved this problem before, please share.

推荐答案

鉴于似乎肯定有一个从文件扩展名到特定Excel格式的多对多映射（请参见下表），我决定使其保持（非常）简单，并假设只有几种可能的格式是可能的。

Given that there seems to be a definite one-to-many mapping from file extension to specific Excel formats (see table below), I decided to keep it (very) simple, and assume only a few possible formats are likely.

我的代码：

Private Function excel_type(file As String) As Integer
    Dim ext As String
    ext = LCase(extension(file))
    excel_type = IIf(ext = "xlsx", acSpreadsheetTypeExcel12, _
                 IIf(ext = "xls", acSpreadsheetTypeExcel9, _
                 IIf(ext = "xml", acSpreadsheetTypeExcel12Xml, -1)))
End Function

Public Function extension(file As String) As String
    extension = Right(file, Len(file) - InStrRev(file, "."))
End Function

参考表（免费）编辑以确保完整性）：

Reference table (feel free to edit for completeness):

Extn    SpreadsheetType          Value  File format

?       acSpreadsheetTypeExcel3      0  Microsoft Excel 3.0
?       acSpreadsheetTypeExcel4      6  Microsoft Excel 4.0
xls     acSpreadsheetTypeExcel5      5  Microsoft Excel 5.0
xls     acSpreadsheetTypeExcel7      5  Microsoft Excel 95
xls     acSpreadsheetTypeExcel8      8  Microsoft Excel 97
xls     acSpreadsheetTypeExcel9      8  Microsoft Excel 2000
xlsx    acSpreadsheetTypeExcel12     9  Microsoft Excel 2010
xml     acSpreadsheetTypeExcel12Xml 10  Microsoft Excel 2010 XML
?       acSpreadsheetTypeLotusWJ2    4
?       acSpreadsheetTypeLotusWJ1    
?       acSpreadsheetTypeLotusWJ3    
?       acSpreadsheetTypeLotusWJ4    

这篇关于VBA工具从给定电子表格确定SpreadsheetType的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！