如何使用Excel VBA无需打开已关闭工作簿即可获取其Data Table数据

以下是三种可直接落地的实现方案，按需选择即可：

方案1：Power Query（最推荐，无代码，易维护） #

适合绝大多数常规使用场景，刷新方便，无需写代码：

• 打开管理员/员工库存系统工作簿，点击顶部菜单栏「数据」选项卡，选择「获取数据>自文件>自工作簿」
• 在弹出的文件选择窗口中选中作为数据库的第三个工作簿，点击「导入」
• 在导航器窗口中找到你需要提取的Data Table名称，选中后可先点击「转换数据」做字段清洗、筛选等预处理，也可直接点击「加载」导入数据到当前工作簿
• 后续数据更新时直接点击「数据」选项卡下的「全部刷新」即可，全程不需要打开数据库工作簿
  注意：需要确保数据库工作簿的存储路径没有变动，否则刷新会报错，路径变更后重新修改数据源路径即可

方案2：VBA脚本（适合自定义自动化流程场景） #

如果需要和其他业务逻辑联动，可使用ADO连接读取封闭工作簿数据：

• 按下Alt+F11打开VBA编辑器，插入新的模块，粘贴以下代码：

Sub 提取封闭工作簿数据()
    Dim conn As Object
    Dim rs As Object
    Dim strConn As String
    Dim strSQL As String
    Dim dbPath As String
    ' 替换为你的数据库工作簿实际绝对路径
    dbPath = "C:\你的文件存储路径\Database.xlsx"
    ' 建立数据库连接
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set conn = CreateObject("ADODB.Connection")
    conn.Open strConn
    ' 替换为你要提取的表名，*代表提取全部字段，也可自定义指定字段、加筛选条件
    strSQL = "SELECT * FROM [你的Data Table名称$]"
    Set rs = conn.Execute(strSQL)
    ' 数据写入当前工作簿指定位置，可按需修改工作表名和起始单元格
    Sheets("库存数据表").Range("A1").CopyFromRecordset rs
    ' 释放资源
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

• 修改代码中的数据库路径、表名、写入位置参数后运行即可，也可以绑定工作表按钮实现一键刷新

方案3：直接引用公式（适合小数据量临时查询场景） #

如果仅需要调取少量单元格数据，可直接用公式引用：

• 格式为：='数据库工作簿存储路径\[数据库工作簿名称.xlsx]工作表名'!单元格地址
• 示例：='C:\库存系统\[Database.xlsx]库存总表'!A2即可调取数据库工作簿中对应单元格的内容
  注意：该方式仅适合小数据量使用，数据量过大会严重卡顿，且路径变更后需要批量更新公式，维护成本较高

内容的提问来源于stack exchange，提问作者Ambet