将 Excel 文件导入 Access 表运行时错误 '-2147352565 (8002000b)'

发布于 2024-09-30 03:59:28 字数 1214 浏览 3 评论 0原文

场景：用户要求我向他们提供一个按钮，他们可以在其中选择 .xls，然后它将数据导入到表中的相应列中。

问题：我将提供下面的代码，但基本上一旦它尝试打开工作簿，就会出现以下错误。我已经用谷歌搜索了许多解决方案，但仍然收到此错误。替代文本

    Private Sub Command20_Click()
     Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim strSelectedFile As String

Set fdg = Application.FileDialog(msoFileDialogFilePicker)

With fdg
  .AllowMultiSelect = False
  .ButtonName = "Select"
        .InitialView = msoFileDialogViewList
        .Title = "Select Input Files"

        'add filter for excel
        With .Filters
            .Clear
            .Add "Excel Spreadsheets", "*.xls"
        End With
        .FilterIndex = 1
    If .Show = -1 Then
      For Each vrtSelectedItem In .SelectedItems    'onby be 1
      Dim app As New Excel.Application

      app.Workbooks.Open (vrtSelectedItem)
      app.Worksheets(1).Activate
      For Each rwRow In ActiveWorkbook.Worksheets(1).Rows
        ' Do things with the rwRow object
        Next rwRow
        strSelectedFile = vrtSelectedItem
      Next vrtSelectedItem
      Me![txtSelectedFile] = strSelectedFile
    Else     'The user pressed Cancel.
    End If
End With

Set fd = Nothing
End Sub

原文

Scenario: A user has asked me to provide them a button where they can select a .xls and it will import the data into the corresponding columns in a table.

Question: I will provide the code below, but basically once it tries to open the workbook it gives me the error below. I have googled for a number of solutions but I'm still getting this error.
alt text

    Private Sub Command20_Click()
     Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim strSelectedFile As String

Set fdg = Application.FileDialog(msoFileDialogFilePicker)

With fdg
  .AllowMultiSelect = False
  .ButtonName = "Select"
        .InitialView = msoFileDialogViewList
        .Title = "Select Input Files"

        'add filter for excel
        With .Filters
            .Clear
            .Add "Excel Spreadsheets", "*.xls"
        End With
        .FilterIndex = 1
    If .Show = -1 Then
      For Each vrtSelectedItem In .SelectedItems    'onby be 1
      Dim app As New Excel.Application

      app.Workbooks.Open (vrtSelectedItem)
      app.Worksheets(1).Activate
      For Each rwRow In ActiveWorkbook.Worksheets(1).Rows
        ' Do things with the rwRow object
        Next rwRow
        strSelectedFile = vrtSelectedItem
      Next vrtSelectedItem
      Me![txtSelectedFile] = strSelectedFile
    Else     'The user pressed Cancel.
    End If
End With

Set fd = Nothing
End Sub

分享到QQ

分享到微博

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

发布评论

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

星星的轨迹 2024-10-07 03:59:28

嗯..为我工作。

之后的四行

我注释掉了 app.Worksheets(1).Activate 和 Me![txtSelectedFile] = ... The set fd = Nothing code> 我不得不更改为 fdg

我想知道 Excel 文件本身是否有问题。您尝试过从头开始创建一个吗？

回复收藏 0 原文

傲性难收 2024-10-07 03:59:28

我不明白为什么 app.Workbooks.Open 会导致该错误。但是，我会建议一些更改，这些更改可能会也可能不会帮助您识别问题。

将 Option Explicit 添加到模块的声明部分；然后从 VBE 主菜单运行“调试”->“编译”，以显示
在 Dim app As New Excel.Application 之后您还没有 Dim 包含 app.Visible = True 的使用 Excel 添加 app.Quit 和 Set app = Nothing 来监控发生的情况
变量code> 以便您可以在程序结束之前