vba 使用 Excel 宏更改数据透视表外部数据源路径

Changing pivot table external data source path with Excel macro

提问by scaevity

I'm working on a project in MS Excel where I have several Pivot Tables that read data from a tab-delimited text file with column names in the first row (in the same directory as the Excel file) using the Microsoft Text Driver. I've run into a problem where when I copy the text and Excel files to a new directory and try to refresh the data it says it can't find the text files. Unfortunately there seems to be no way to tell Excel I want the paths to the text files to be relative, not absolute. All of the pivot tables use the same data connection, so I figured it shouldn't be too challenging to write a macro that would update the data connection to refer to the correct text file and have a button linked to the macro that would update the file paths and refresh the data for me.

我正在 MS Excel 中处理一个项目，其中我有几个数据透视表，这些数据透视表使用 Microsoft 文本驱动程序从第一行（与 Excel 文件在同一目录中）列名的制表符分隔文本文件中读取数据。我遇到了一个问题，当我将文本和 Excel 文件复制到新目录并尝试刷新数据时，它说找不到文本文件。不幸的是，似乎无法告诉 Excel 我希望文本文件的路径是相对的，而不是绝对的。所有数据透视表都使用相同的数据连接，所以我认为编写一个宏来更新数据连接以引用正确的文本文件并有一个链接到宏的按钮应该不会太具有挑战性文件路径并为我刷新数据。

I'm not overly familiar with VBA and the online documentation seem to be pretty bad, so I haven't been able to get this working -- I can create the correct file path and can refresh the data, but I haven't been able to figure out how to update the connection to use the new file path but retain all its old import/file parsing settings. I have also tried recording a macro while manually updating the data source, but for some reason that always gives me errors which interrupt the recording, so that hasn't helped.

我对 VBA 并不太熟悉，而且在线文档似乎很糟糕，所以我无法让它工作——我可以创建正确的文件路径并可以刷新数据，但我还没有能够弄清楚如何更新连接以使用新的文件路径，但保留其所有旧的导入/文件解析设置。我还尝试在手动更新数据源时录制宏，但由于某种原因，它总是给我带来中断录制的错误，因此没有帮助。

The following is the connection string and command text currently used by the connection, but there's nothing about how to parse/import the data (the file being tab-delimited or having headers in the first column, etc), so I'm not sure how to make sure the connection keeps that data.

以下是连接当前使用的连接字符串和命令文本，但没有关于如何解析/导入数据（文件以制表符分隔或在第一列中有标题等），所以我不确定如何确保连接保留该数据。

Connection String:

连接字符串：

DefaultDir=C:/directoryPath;Driver={Microsoft Text Driver (*.txt; *.csv)};
  DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;
  SafeTransactions=0;Threads=3;UserCommitSync=Yes;

Command Text:

命令文本：

SELECT *
FROM tableName.txt tableName

If anyone knows how to write a macro that will update the path in the connection to the text file please share the code, or if you know how to just make the path relative that'd be great too. Any help would be greatly appreciated!

如果有人知道如何编写一个宏来更新文本文件连接中的路径，请分享代码，或者如果您知道如何使路径相对也很棒。任何帮助将不胜感激！

EDIT:

编辑：

I've been messing around with it a bit more and I was able to change the connection strings to use the new path. However, when I go to refresh the pivot table it imports all the data as text instead of guessing whether it should be numeric, etc, (although it does get the column headers from the first line of the text file, at least). Any ideas on how to tell it to guess the data types (or just keep the old data types)? The code I'm using right now is:

我一直在弄乱它，我能够更改连接字符串以使用新路径。但是，当我刷新数据透视表时，它将所有数据作为文本导入，而不是猜测它是否应该是数字等（尽管它至少从文本文件的第一行获取列标题）。关于如何告诉它猜测数据类型（或只保留旧数据类型）的任何想法？我现在使用的代码是：

Public Sub Test()
    Dim wb As Excel.Workbook
    Dim pc As PivotCache
    Dim path As String

    Set wb = ActiveWorkbook
    path = wb.path

    For Each pc In wb.PivotCaches
        'Debug.Print pc.Connection
        pc.Connection = "ODBC;DBQ=" & path & ";DefaultDir=" & path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes"

    Next
End Sub

回答by scaevity

Ok, so I got it working and thought I'd share. I cycled through each connection in the workbook and changed its path to the new path to the textfiles (created by getting the path of the active workbook and appending the name of the directory of the textfiles). Also, in order to make sure it imported the text file correctly every time I needed to include a 'schema.ini' file with the import information (in the same directory as the text file).

好的，所以我让它工作并认为我会分享。我遍历工作簿中的每个连接并将其路径更改为文本文件的新路径（通过获取活动工作簿的路径并附加文本文件目录的名称来创建）。此外，为了确保每次我需要包含一个带有导入信息的“schema.ini”文件（与文本文件在同一目录中）时，它都能正确导入文本文件。

回答by Joseph

I'm not completely familiar with creating pivot tables in that way exactly, but usually you can get to the information of the pivot table like Connection by looking at the QueryTable object. Check out this example:

我并不完全熟悉以这种方式创建数据透视表，但通常您可以通过查看 QueryTable 对象来获取数据透视表的信息，例如 Connection。看看这个例子：

Option Explicit

Public Sub UpdatePivotTableConnections()
    Dim ws As Excel.Worksheet
    Dim qt As Excel.QueryTable
    Dim fileName As String

    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            fileName = GetFileName(qt)
            MsgBox "The file name for PivotTable '" & qt.Name & "' is: " & fileName
        Next
    Next
End Sub

Public Function GetFileName(ByRef qt As QueryTable) As String
    Dim s() As String
    s = Split(qt.Connection, "\")
    GetFileName = s(UBound(s))
End Function

It's not a complete answer, but it's a start (I don't like posting incomplete answers, but it was the only way to show you a code example.) See what you get with that info, if you can access the information from there, try looking at the QueryTable.Connection string and see how you can parse it and replace it for each PivotTable.

这不是一个完整的答案，但它是一个开始（我不喜欢发布不完整的答案，但这是向您展示代码示例的唯一方法。）如果您可以从那里访问信息，请查看您从该信息中获得的信息，尝试查看 QueryTable.Connection 字符串，看看如何解析它并为每个数据透视表替换它。

回答by Kamal Bharakhda

I have found this, and there are many properties followed for the same..

我发现了这一点，并且有许多相同的属性。

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;\Path\To\CSV\Folder\CSV_Data.csv" _
    , Destination:=Range("$A"))
    .CommandType = 0
    .Name = "Book1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub