如何改进Excel数据连接的刷新？ [英] How to improve refresh for Excel Data Connections?

问题描述

像许多，我有电子表格，从超过40个文本文件作为数据源绘制数据。文本文件来自另一个应用程序，需要定期更新到Excel。

Like many, I have spreadsheet that draws data from over 40 text files as data sources. The text files are from another app, and need to be periodically updated into Excel.

这组数据源文件和电子表格需要能够复制并在不同的系统上运行。这就是Excel令人惊讶的无法支持从电子表格文件夹（或相对路径）导入数据成为一个大问题。 此问题提及了问题，但没有解决方案。

The set of data source files and spreadsheet need to be able to be duplicated and run on different systems. This is where the astonishing inability of Excel to support data import from the spreadsheet folder (or relative paths at all) becomes a big problem. This question mentions the issue but has no solution.

我为Excel中的这个（IMHO）基本缺陷开发了一个原始的解决方法。将电子表格文件夹映射到带有SUBST的驱动器号。然后从SUBST驱动器号导入数据。该驱动器盘符和路径将成为电子表格的一部分，埋在对话框中，并且非常不方便更新。因此，无论何时复制或移动电子表格，请将SUBST重新创建到当前文件夹。丑陋，但有效。

I developed a crude workaround for this (IMHO) fundamental flaw in Excel. Map your spreadsheet folder to a drive letter with SUBST. Then import the data from the SUBST drive letter. That drive letter and path will become part of the spreadsheet, buried deep in dialogs, and very inconvenient to update. So instead, whenever you copy or move the spreadsheet, re-create the SUBST to the current folder. Ugly, but effective.

新问题：使用此技术，当我打开电子表格并单击刷新以从数据源刷新时，我必须单击导入超过40个对话框 - 每个文件一个。

New Question: Using this technique, when I open the spreadsheet and click Refresh to refresh from the data sources, I have to click "Import" on over 40 dialogs - one for each file. How can I automate that process?

推荐答案

我发现在数据范围属性下，有一个设置为提示文件刷新名称。通过取消选中，不再需要为每个链接的文件单击导入。每个链接的数据源的属性必须单独调整。似乎没有任何能力来多选择数据源。

I discovered that under a data range properties, there is a setting for "Prompt for file name on refresh". By unchecking that, it is no longer necessary to click import for every linked file. The properties for each linked data source must be adjusted individually. There doesn't seem to be any ability to multi-select data sources.

这篇关于如何改进Excel数据连接的刷新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！