Access可以在不将Excel文件导入表的情况下读取其内容吗? [英] Can Access read the contents of an Excel file without importing it to a table?

问题描述

是否可以读取Excel工作簿的标题行，以确定在导入表之前是否存在某些字段?如果在导入之前缺少某些特定字段，我希望收到通知.

Is it possible to read the header row of an Excel workbook to determine if certain fields exist before importing to a table? I would like there to be a notification if those certain fields are missing before the import happens.

我的Access数据库要求用户从其他系统生成的Excel文件中导入数据，用户可以在其中选择要导出的字段.有时，用户意外地无法选择我的数据库正常工作所需的某些字段，这会导致错误，然后我会收到电子邮件.当前，如果缺少字段，则导入时没有错误，这很好，因为并非所有字段都是必需的，只是某些字段是必需的.

My Access database requires users to import data from Excel files that are generated by a different system where users can choose the fields to export. Sometimes users accidentally fail to select certain fields that are required for my database to work, which causes errors, and then I get emails. There is currently no error on import if fields are missing, which is fine because not all fields are required, just certain ones.

作为参考，这是我现在要导入的代码.它可以正常运行并执行应有的功能:

For reference, this is the code I'm using to import right now. It runs fine and does what it's supposed to:

Private Sub ImportEDAS()
On Error GoTo SubError

    DoCmd.Hourglass True
    DoCmd.SetWarnings False

    If IsFile(Me.ImportFolder & "\perselection.xlsx") = True Then
        DoCmd.OpenQuery "qryClearEDAS"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblEDAS", Me.ImportFolder & "\perselection.xlsx", True
        MsgBox DCount("*", "tblEDAS") & "Records Imported", vbInformation + vbOKOnly, "EDAS Import Status"
    Else
        MsgBox "Could not find perselection.xlsx in the import folder.", vbCritical + vbOKOnly, "EDAS Import Status"
    End If

SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
SubError:
    MsgBox "Error Number: " & Err.Number & "- " & Err.Description, vbCritical + vbOKOnly, "ImportEdas Sub Error"
    Resume SubExit
End Sub

推荐答案

您可以创建查询来查找工作表数据，而无需打开或链接.

You can create a query to lookup Worksheet-Data without opening or linking.

SELECT *
FROM [Sheet1$] IN 'C:\Path\to\File\ExcelFile.xlsx'[Excel 12.0;HDR=No;IMEX=0;];

可以与DAO一起使用，并且可以与 OLEDB 一起使用.

Can be used with DAO and similar with OLEDB.

这篇关于Access可以在不将Excel文件导入表的情况下读取其内容吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！