MS Access VBA acImport 工作表名称，带空格和范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16066903/
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
MS Access VBA acImport sheet name with spaces and range
提问by Bill Merritt
I'm building a form with a button called "Go" to import a specific range of a specific excel sheet from an excel file. Another sub/function takes care of having the file name on the form in the field called text_filepathdata.
我正在构建一个带有名为“Go”的按钮的表单，以从 Excel 文件中导入特定范围的特定 Excel 工作表。另一个子/函数负责在名为 text_filepathdata 的字段中的表单上保存文件名。
I've discovered that spaces in the sheet names are problematic, however it can be resolved by using special quotation.
我发现工作表名称中的空格有问题，但是可以通过使用特殊引号来解决。
Dim sheetname As String
sheetname = "'Commit Volumes!'"
DoCmd.Transferspreadsheet acImport, acSpreadsheetTypeExcel12, _
"lcl_ImportedFile", text_filepathdata, True, sheetname
That works, but I can't specify a range in addition to the sheet name because of the special quotation... (ie. "'Commit Volumes!A6:Z5000'" doesn't work and neither does a concatenation of the two pieces like "'Commit Volumes'" & "A6:Z5000")
这有效，但由于特殊引号，我无法指定除工作表名称之外的范围......（即“'Commit Volumes！A6：Z5000'”不起作用，两者的串联也不起作用像“'提交卷'”和“A6：Z5000”）
I've tried all manner of quotations and concatenations, but the only things that work are:
我已经尝试了各种引用和连接方式，但唯一有效的是：
sheetname = "'Commit Volumes!'"
sheetname = "'Commit Volumes$'"
sheetname = "CommitVolumes!A6:Z5000"
The excel sheet cannot be modified because it comes from another system and the file (after being updated) must be uploaded back to that system.
无法修改 Excel 表，因为它来自另一个系统，并且文件（更新后）必须上传回该系统。
Here is my code:
这是我的代码：
Private Sub button_Go_Click()
On Error GoTo Err_button_Go_Click
Dim sheetname As String
sheetname = "'Commit Volumes!'"
If IsNull(text_filepathdata) Or text_filepathdata = "" Then
MsgBox "Please browse and select a valid file to import.", _
vbCritical, "Invalid File"
Else
DoCmd.OpenForm "f_PleaseWait_Processing", acNormal, , , acFormReadOnly, acWindowNormal
If ifTableExists("lcl_ImportedFile") = True Then
DoCmd.DeleteObject acTable, "lcl_ImportedFile"
End If
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,_
"lcl_ImportedFile", text_filepathdata, True, sheetname
'Call module to process the imported data
Call MainProcess
End If
Exit_button_Go_Click:
Exit Sub
Err_button_Go_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_button_Go_Click
End Sub
I get two different error messages depending on the issue.
根据问题，我收到两种不同的错误消息。
If there's a space in the name I get: Run-time error 3129: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
如果名称中有空格，我会得到：运行时错误 3129：无效的 SQL 语句；应为“删除”、“插入”、“过程”、“选择”或“更新”。
If there's a problem with the name otherwise I get: Run-time error 3011: The Microsoft Access database engine could not find the object "Commit Volumes$A1:Z5000". Make sure the object exists and that you spell its name and the path correctly. If "Commit Volumes$A1:Z5000" is not a local object, check your network connection or contact the server administrator.
如果名称有问题，否则我会得到：运行时错误 3011：Microsoft Access 数据库引擎找不到对象“Commit Volumes$A1:Z5000”。确保该对象存在并且您正确拼写了它的名称和路径。如果“Commit Volumes$A1:Z5000”不是本地对象，请检查您的网络连接或联系服务器管理员。
Any help would be greatly appreciated!
任何帮助将不胜感激！
Bill
账单
Variations I've Tried:
我尝试过的变化：
sheetname = "'Commit Volumes'!A6:Z5000";
sheetname = "'Commit Volumes!'A6:Z5000"
sheetname = "'Commit Volumes$'A6:Z5000"
sheetname = "'Commit Volumes'$A6:Z5000"
sheetname = "'Commit Volumes'$A:$Z00"
sheetname = "'Commit Volumes!'$A:$Z00"
sheetname = "'Commit Volumes$'$A:$Z00"
回答by Eddie
If the file gets exported from the other system in a known\set format, you could refer to it by index rather than by name:
如果文件以已知\集合格式从其他系统导出，您可以通过索引而不是名称来引用它：
For Example: sheets(1), sheets(2). Then add the range.
例如：sheets(1)、sheets(2)。然后添加范围。
回答by kevin
use sheetname = "Commit Volumes$" with no range which seems to work by matching the table field names with the excel column headings
使用 sheetname = "Commit Volumes$" 没有范围，这似乎通过将表字段名称与 Excel 列标题匹配来工作
回答by Daniel Seeling
I had the same issue.
我遇到过同样的问题。
finally i found that you can use:
最后我发现你可以使用：
sheetname = "Commit Volumes!A6:Z5000" example DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "table name", _ filetoimport, False, "Commit Volumes!A6:Z5000"
sheetname = "Commit Volumes!A6:Z5000" 示例 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "table name", _ filetoimport, False, "Commit Volumes!A6:Z5000"
Regards!
问候！
回答by Leo13
I tried the above proposed solutions and unfortunately it did not work in my case: indeed the import method of Access does not like it when the worksheets have spaces in their names.
我尝试了上面提出的解决方案，但不幸的是它在我的情况下不起作用：当工作表的名称中有空格时，Access 的导入方法确实不喜欢它。
I did a workaround: renaming the worksheets during the code to something short and without spaces, for instance: from "Data Q1 2016" to "WS1". In this way, you can pass the value "WS1$" to the Range argument of the import function, and Access will import the used range of the given worksheet
我做了一个解决方法：在代码中将工作表重命名为简短且没有空格的名称，例如：从“Data Q1 2016”到“WS1”。这样就可以将值“WS1$”传递给导入函数的 Range 参数，Access 会导入给定工作表的使用范围
When importing the second worksheet, I again used the code and renamed it to "WS2"... and so forth.
导入第二个工作表时，我再次使用该代码并将其重命名为“WS2”...等等。
in summary: rename the worksheets during code runtime to short names without spaces, and add the '$' symbol at the end to make it grab the used range.
总结：在代码运行期间将工作表重命名为不带空格的短名称，并在末尾添加“$”符号以使其获取使用的范围。
回答by DebbieDoesData
I had the same problems. When I created a range string variable and passed it to the DoCmd.TransferSpreadsheet routine, Access changed the "!" character to "$" and gave me an error. If I passed a string constant to the Range parameter of the DoCmd.TransferSpreadsheet routine, Access did not change the character. In other words, running the following code, ...
我有同样的问题。当我创建一个范围字符串变量并将其传递给 DoCmd.TransferSpreadsheet 例程时，Access 更改了“！” 字符到“$”并给了我一个错误。如果我将字符串常量传递给 DoCmd.TransferSpreadsheet 例程的 Range 参数，Access 不会更改字符。换句话说，运行以下代码，...
Dim strFilename As String
Dim strRange As String
Dim strTableName As String
strFilename = "YourWorkbookName.xlsx"
strRange = "Sheet5!A1:Z99"
strTableName = "tblImport"
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=strTableName, _
FileName:=strFilename, _
HasFieldNames:=True, _
Range:=strRange
... gave me the 3011 runtime error.
...给了我 3011 运行时错误。
However, when I separated the sheet name and the address into two separate variables and passed them to the routine, Access was happy. In other words, when I ran the code, ...
但是，当我将工作表名称和地址分成两个单独的变量并将它们传递给例程时，Access 很高兴。换句话说，当我运行代码时，...
Dim strAddress As String
Dim strFilename As String
Dim strSheetName As String
Dim strTableName As String
strAddress = "A1:Z99"
strFilename = "YourWorkbookName.xlsx"
strSheetName = "Sheet5"
strTableName = "tblImport"
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=strTableName, _
FileName:=strFilename, _
HasFieldNames:=True, _
Range:=strSheetName & "!" & strAddress
... I received no error and the data from the Sheet5 sheet was properly imported into tblImport.
...我没有收到任何错误，并且 Sheet5 工作表中的数据已正确导入到 tblImport 中。
It should be noted that Sheet5 is not the first sheet of the workbook and strAddress does not have "$" characters in it.
应该注意的是，Sheet5 不是工作簿的第一张工作表，并且 strAddress 中没有“$”字符。
Happy coding.
快乐编码。
回答by Scott Holtzman
This is what you need:
这是你需要的：
'Commit Volumes'!A6:Z5000
'Commit Volumes'!A6:Z5000
When there is a sheet name with spaces or dashes, excel places single quotes around the sheet name itself, not around the whole reference.
当工作表名称带有空格或破折号时，excel 会在工作表名称本身周围放置单引号，而不是在整个引用周围放置单引号。
So:
所以：
sheetname = "'Commit Volumes'!A6:Z5000"
