如何使用 Excel VBA 将 Excel XLS 转换为 Access MDB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4045521/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Do I Convert an Excel XLS to an Access MDB using Excel VBA
提问by DontFretBrett
I need to use VBA from Excel to load an Excel workbook in access and transfer it out to a database.
我需要使用 Excel 中的 VBA 来加载 Excel 工作簿，并将其传输到数据库中。
Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Test.xls"
I got that far and Excel crashes and has to restart. My plan was to use the following but I can't get that far.
我做到了，Excel 崩溃了，必须重新启动。我的计划是使用以下内容，但我做不到那么远。
acc.DoCmd.TransferDatabase
Any ideas? I've googled for days and come up with nothing.
有任何想法吗？我已经用谷歌搜索了好几天，但一无所获。
*Edit: Thanks for the responses so far
I absolutely have to use Excel VBA, unfortunately. There is an excel spreadsheet that has a bunch of empty columns that are being recognized by the Jet engine as defined columns, too many in fact > 255 (or is it > 256?). I do NOT want to open the Excel worksheet for any reason (this takes far too long over the network). I don't have the option or choice to format it correctly or clean it up. It's easy to convert an XLS spreadsheet into a MDB database inside of access as you all say, but that's not an option. So like I said, I need to use VBA in Excel to manipulate the access object to convert an XLS workbook to an MDB database; Once I have this, the rest will be cake. Thanks so much! I love this site.
*编辑：感谢到目前为止的答复
，不幸的是，我绝对必须使用 Excel VBA。有一个 excel 电子表格，其中有一堆空列被 Jet 引擎识别为定义的列，实际上太多了 > 255（或者是 > 256？）。我不想出于任何原因打开 Excel 工作表（这在网络上花费的时间太长）。我没有正确格式化或清理它的选项或选择。正如大家所说，在 access 内将 XLS 电子表格转换为 MDB 数据库很容易，但这不是一种选择。所以就像我说的，我需要在 Excel 中使用 VBA 来操作访问对象以将 XLS 工作簿转换为 MDB 数据库；一旦我有了这个，剩下的就是蛋糕。非常感谢！我喜欢这个网站。
回答by mechanical_meat
This task is straightforward if you're able to use VBA from within Microsoft Access; e.g.:
如果您能够在 Microsoft Access 中使用 VBA，则此任务很简单；例如：
DoCmd.TransferSpreadsheet , , _
"tblImportFromExcel","C:\path\to\myfile.xls", True, "A1:B200"
TransferSpreadsheetdocumentation.
TransferSpreadsheet文档。
回答by Andreas J
What is wrong with the suggested solution from Adam Bernier (with the addition from PowerUser concerning using an access object from within Excel); your last comment was after those suggestions and you did not reply.
Adam Bernier 建议的解决方案有什么问题（PowerUser 添加了关于在 Excel 中使用访问对象的内容）；您的最后一条评论是在这些建议之后，您没有回复。
Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Test.mdb"
acc.DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
Spreadsheettype:=acSpreadsheetTypeExcel8, _
TableName:="tblImportFromExcel", _
Filename:="C:\path\to\myfile.xls", _
HasFieldNames:=True, _
Range:="A1:B200"
(Adapt as needed, especially the HasFieldNames and Range). If this does not work, then there is probably something really wrong with your Excel-Sheet.
（根据需要进行调整，尤其是 HasFieldNames 和 Range）。如果这不起作用，那么您的 Excel 表格可能真的有问题。
The only other thing I can think of (but that would mean to open the file) is to save the Excel-Sheet as XML and transform the values via XSLT in a more suitable format, then import the resulting XML. But that might be overkill (how complex is your file, how often do you need this import to proceed).
我能想到的唯一另一件事（但这意味着打开文件）是将 Excel-Sheet 保存为 XML 并通过 XSLT 以更合适的格式转换值，然后导入生成的 XML。但这可能有点矫枉过正（您的文件有多复杂，您需要多久进行一次导入）。
HTH Andreas
HTH安德烈亚斯
