使用 Excel 宏更改数据透视表外部数据源路径 [英] Changing pivot table external data source path with Excel macro

问题描述

我正在处理 MS Excel 中的一个项目，其中我有几个数据透视表，这些数据透视表使用 Microsoft Text 从制表符分隔的文本文件中读取数据，第一行中的列名(与 Excel 文件位于同一目录中)司机.我遇到了一个问题，当我将文本和 Excel 文件复制到新目录并尝试刷新数据时，它说找不到文本文件.不幸的是，似乎没有办法告诉 Excel 我希望文本文件的路径是相对的，而不是绝对的.所有的数据透视表都使用相同的数据连接，所以我认为编写一个宏来更新数据连接以引用正确的文本文件并有一个链接到宏的按钮来更新文件路径并为我刷新数据.

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.

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

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.

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

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.

连接字符串:

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;

命令文本:

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!

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

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

推荐答案

好的，所以我得到它的工作，并认为我会分享.我循环浏览了工作簿中的每个连接，并将其路径更改为文本文件的新路径(通过获取活动工作簿的路径并附加文本文件目录的名称来创建).此外，为了确保每次我需要在导入信息中包含schema.ini"文件时它正确导入文本文件(与文本文件位于同一目录中).

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).

这篇关于使用 Excel 宏更改数据透视表外部数据源路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！