将MS查询嵌入到Excel中

作家

我制作了一个由几个复杂的工作表组成的excel工作簿。

现在，我已经使用MS查询创建了查询，以允许我从这些不同的工作表中收集计算所需的信息。一切正常，直到我将工作簿移动到新位置。

因为这种情况经常发生，并且我不想每次都在想知道是否有一种方法可以将查询链接到工作簿本身而不是特定文件位置时重新构建查询。

有谁能够帮助我？

这是查询的de SQL构建

SELECT `'Production Plan$'`.`Scheduled Operation`, `Tests$`.`Test Name`, `Tests$`.`Tests per batch`, `Tests$`.`Ops PER BATCH or PER YR:`, `Tests$`.`Total Locations (MULTIPLIER):`, `Tests$`.`Retest Rate`, `'Production Plan$'`.`Batches for 2015`, `'Production Plan$'`.`Batches for 2016`, `'Production Plan$'`.`Batches for 2017`, `'Production Plan$'`.`Batches for 2018`
FROM `C:\Users\UGent\Desktop\XXX\XXX\Models\new model SV 0106.xlsx`.`'Production Plan$'` `'Production Plan$'`, `C:\Users\UGent\Desktop\XXX\XXX\Models\new model SV 0106.xlsx`.`Tests$` `Tests$`
WHERE `'Production Plan$'`.`Scheduled Operation` = `Tests$`.`Scheduled Operation:`

这是连接字符串

DSN=Excel Files;DBQ=C:\Users\UGent\Desktop\BQG\Genzyme\Models\new model SV 0106.xlsx;DefaultDir=C:\Users\UGent\Desktop\BQG\Genzyme\Models;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

AnalystCave.com

我想知道是否有一种方法可以将我的查询链接到工作簿本身，而不是特定的文件位置

否-您无法将查询“链接”到Excel工作簿，因为（据我所知）没有任何机制可以跟踪您如何移动源Excel工作簿。

你能做什么？

选项1-手动重新连接

编写一个VBA宏，它将重新创建SQL查询。它可以像这样工作：

打开工作簿时，将在事件中运行VBA宏Workbook.Open
事件宏检查源工作表是否位于正确的文件路径中。如果不是，那么（3）
如果文件不存在，则重新创建，然后显示一个弹出窗口，询问新的文件路径。当用户单击“确定”时，通过VBA宏重新创建SQL查询。您可以使用宏录制来提取必要的VBA或参见此处。

有关如何在VBA中创建MS查询的一些示例代码：

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\test.xlsx;DefaultDir=C:\;DriverId=1046;Ma" _
        ), Array("xBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")). _
        QueryTable
        .CommandType = 0
        .CommandText = Array( _
        "SELECT `Sheet1$`.A, `Sheet1$`.B" & Chr(13) & "" & Chr(10) & "FROM `C:\test.xlsx`.`Sheet1$` `Sheet1$`" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With

选项2-将Excel推送到网络位置

尝试将工作簿定位到不需要经常询问源文件路径的网络位置。您甚至可以将其放置为链接。它可以像这样工作：