将 Excel 文件数据导入 Access 数据库 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19488053/
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
Import Excel file data into Access Database VBA
提问by user2902061
I am completely new to Access & VBA. I need to import the excel file data into access table. I was trying to do it using following code.
我对 Access 和 VBA 完全陌生。我需要将excel文件数据导入访问表。我试图使用以下代码来做到这一点。
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", importFilePath, True
In my input file there is some format and I need to get the data from 8th line.
在我的输入文件中有某种格式，我需要从第 8 行获取数据。
So, I am stuck at this point. How to read the data from 8th column. I have also specified range but then it generated the error. Can anybody help on this?
所以，我被困在这一点上。如何从第 8 列读取数据。我也指定了范围，但它产生了错误。有人可以帮忙吗？
回答by Gord Thompson
Certainly one way to do it would be to create a temporary copy of the file, open that copy in Excel, delete the first 7 rows, save it, and then import that copy:
当然，一种方法是创建文件的临时副本，在 Excel 中打开该副本，删除前 7 行，保存，然后导入该副本：
Sub ExcelImportTest()
Dim fso As Object ' FileSystemObject
Dim f As Object ' File
Dim strTempPath As String
Dim objExcel As Object ' Excel.Application
Dim objWorkbook As Object ' Excel.Workbook
Const TemporaryFolder = 2
Set fso = CreateObject("Scripting.FileSystemObject") ' New FileSystemObject
strTempPath = fso.GetSpecialFolder(TemporaryFolder) & "\" & fso.GetTempName & "\"
fso.CreateFolder strTempPath
Set f = fso.GetFile("C:\Users\Gord\Desktop\toImport.xls")
fso.CopyFile f.Path, strTempPath & f.Name
Set objExcel = CreateObject("Excel.Application") ' New Excel.Application
Set objWorkbook = objExcel.Workbooks.Open(strTempPath & f.Name)
objWorkbook.ActiveSheet.Rows("1:7").EntireRow.Select
objExcel.Selection.Delete
objWorkbook.ActiveSheet.Range("A1").Select
objWorkbook.Save
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ExcelData", strTempPath & f.Name, True
fso.DeleteFile strTempPath & f.Name
fso.DeleteFolder Left(strTempPath, Len(strTempPath) - 1)
Set f = Nothing
Set fso = Nothing
End Sub
