VBA将Excel电子表格逐行导入Access

发布于 2024-10-22 04:18:42 字数 499 浏览 2 评论 0原文

我正在调试一些代码，需要找出
DoCmd.TransferSpreadsheet acImport，，“.....
失败了，所以我决定逐行“手动”导入它，看看它在哪里失败。

我想我正在寻找类似的东西：

mySpreadSheet = ConnectTo(Spreadsheet.xlsx)
while(!mySpreadSheet.EOF)
   get(mySpreadSheet.nextLine)
   SQL("UPDATE MyTable with mySpreadSheet.nextLine")

我尝试过谷歌搜索但无济于事。非常感谢任何帮助！

附加信息：

电子表格和 Access 表的列名称相同。
每个数据类型都是 nvarchar(MAX) （或者 Access 称之为“Memo”）
该表是 SQL Server 2008 的链接表

原文

I'm debugging some code and need to find out where a
DoCmd.TransferSpreadsheet acImport, , ".....
fails so I've decided to import it 'manually' line-by-line to see where it falls over.

I suppose something like this is what I'm looking for:

mySpreadSheet = ConnectTo(Spreadsheet.xlsx)
while(!mySpreadSheet.EOF)
   get(mySpreadSheet.nextLine)
   SQL("UPDATE MyTable with mySpreadSheet.nextLine")

I've tried Googling to no avail. Any help is much appreciated!

Additional info:

The column names of the spreadsheet and the Access table are identical.
Every data type is nvarchar(MAX) (Or "Memo" as Access calls it)
The table is a linked table to SQL Server 2008

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

薆情海 2024-10-29 04:18:42

如果您有明确定义的数据表布局，则 ADO 可以很好地工作（HansUp 答案）。如果您在加载对象之前需要添加控制，您可以连接到 Excel 工作簿，然后提取您喜欢的任何数据。这实际上取决于您需要的控制级别。

Public Sub LoadExcelToAccess(xlPath As String)
'uses late binding to open excel workbook and open it line by line

'make reference to Microsoft Excel xx.x Object Model to use native functions, requires early binding however

    Dim xlApp As Object 'Excel.Application
    Dim xlWrk As Object 'Excel.Workbook
    Dim xlSheet As Object 'Excel.Worksheet
    Dim i As Long
    Dim sql As String

    Set xlApp = VBA.CreateObject("Excel.Application")

    'toggle visibility for debugging
    xlApp.Visible = False

    Set xlWrk = xlApp.Workbooks.Open(xlPath)
    Set xlSheet = xlWrk.Sheets("Sheet1") 'modify to your perticular sheet

    'depends on what your trying to do with the sheet
    For i = 1 To 10

        'quick and dirty:  best to load items into custom class collection, then do processing there
        sql = "Insert Into [Temp] (Col1) VALUES (" & xlSheet.Cells(i, 1).Value & ")"
        DoCmd.RunSQL sql
    Next i

    'make sure to dispose of objects
    xlWrk.Close
    xlApp.Quit

    Set xlSheet = Nothing
    Set xlWrk = Nothing
    Set xlApp = Nothing
End Sub

ADO works well if you have a well defined sheet layout of your data (HansUp answer). If you need added control before loading the objects, you can hook into the excel workbook, and then pull out whatever data you like. It really depends on the level of control you need.

Public Sub LoadExcelToAccess(xlPath As String)
'uses late binding to open excel workbook and open it line by line

'make reference to Microsoft Excel xx.x Object Model to use native functions, requires early binding however

    Dim xlApp As Object 'Excel.Application
    Dim xlWrk As Object 'Excel.Workbook
    Dim xlSheet As Object 'Excel.Worksheet
    Dim i As Long
    Dim sql As String

    Set xlApp = VBA.CreateObject("Excel.Application")

    'toggle visibility for debugging
    xlApp.Visible = False

    Set xlWrk = xlApp.Workbooks.Open(xlPath)
    Set xlSheet = xlWrk.Sheets("Sheet1") 'modify to your perticular sheet

    'depends on what your trying to do with the sheet
    For i = 1 To 10

        'quick and dirty:  best to load items into custom class collection, then do processing there
        sql = "Insert Into [Temp] (Col1) VALUES (" & xlSheet.Cells(i, 1).Value & ")"
        DoCmd.RunSQL sql
    Next i

    'make sure to dispose of objects
    xlWrk.Close
    xlApp.Quit

    Set xlSheet = Nothing
    Set xlWrk = Nothing
    Set xlApp = Nothing
End Sub

回复收藏 0 原文

素染倾城色 2024-10-29 04:18:42

您可以创建与电子表格的 ADO 连接（请参阅Excel 2007 的连接字符串），然后打开 ADO具有该连接的记录集（请参阅 StackOverflow：例如，VBA 中的 ADODB 记录集说 excel 字段为空，但实际上它不是）。

然后浏览记录集行，并使用该行的值创建 SQL INSERT 语句。

strInsert = "INSERT INTO MyTable (first_field, second_field) VALUES ('" & -
    rs2.Field(0).Value & "', '" & rs2.Field(1).Value & "');"
Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailonerror

截断的代码假定first_field 和second_field 是文本数据类型。如果它们是数字，则丢失单引号。

我认为这大致符合你的要求。然而，在求助于代码之前，我会检查电子表格是否干净地导入到新的本机 Access 表中。（此外，检查该新表的数据类型和约束是否与链接的 SQL Server 表的数据类型和约束兼容。）如果有效，可以尝试从 Management Studio 或任何合适的工具将电子表格直接导入 SQL Server。

You can create an ADO connection to your spreadsheet (see Connection strings for Excel 2007), then open an ADO recordset with that connection (see StackOverflow: ADODB recordset in VBA says excel field is empty when it's not for example).

Then move through the recordset rows, and create a SQL INSERT statement using the row's values.

strInsert = "INSERT INTO MyTable (first_field, second_field) VALUES ('" & -
    rs2.Field(0).Value & "', '" & rs2.Field(1).Value & "');"
Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailonerror

That code snipped assumes first_field and second_field are text data types. If they are numeric, lose the single quotes.

I think that does roughly what you asked. However, before resorting to code I would check whether the spreadsheet imports cleanly into a new native Access table. (Also, check whether the data types and constraints on that new table are compatible with those of the linked SQL Server table.) If that works, maybe try importing the spreadsheet directly into SQL Server from the Management Studio, or whatever tool is appropriate.

回复收藏 0 原文

不忘初心 2024-10-29 04:18:42

为了进行故障排除，请尝试链接到电子表格并查看遇到的问题，包括在数据表视图中查看数据时显示错误的数据。

回复收藏 0 原文

我纯我任性 2024-10-29 04:18:42

您还可以尝试将 Excel 数据复制到剪贴板并将其粘贴到 Access 表中。无论发生什么错误，Access 都会将其写入“粘贴错误”表中。

回复收藏 0 原文

看海 2024-10-29 04:18:42

两个附加选项：

像表格一样链接 Access 中的电子表格。在 Access 2007 中，转到“外部数据”窗格并选择“导入 Excel 电子表格”。您应该导入到现有数据表、新数据表或仅链接到 Excel 文件。然后，您将像使用 Access 表一样使用这个新的“Excel”表（考虑到性能问题，在最后一种情况下）。
尝试解决 Docmd.TransferSpreadsheet 问题。我已经使用这种方法好几年了，它工作得很好，尽管在某些情况下它应该有点棘手（我相信它是你的情况）。请提供有关此方法的问题的更多信息，包括您的 Access 和 Excel 版本，这是值得的。

我希望我有所帮助。祝你好运。

回复收藏 0 原文

~没有更多了~