如何改善Excel数据连接的刷新？ [英] How to improve refresh for Excel Data Connections?
问题描述
需要在不同的系统上复制和运行数据源文件和电子表格。这是Excel非常难以支持从电子表格文件夹（或相对路径）导入数据的一个重大问题。 此问题提及该问题，但没有解决方案。
我为Excel（IMHO）的基本缺陷开发了一个粗暴的解决方法。将您的电子表格文件夹映射到带有SUBST的驱动器号。然后从SUBST驱动器盘符导入数据。驱动器号和路径将成为电子表格的一部分，埋在对话框中，非常不方便更新。因此，每当您复制或移动电子表格时，请将SUBST重新创建到当前文件夹。丑陋但有效。
新问题使用此技术，当我打开电子表格并单击刷新以从数据源刷新时，我必须单击导入超过40个对话框 - 每个文件一个。我如何自动化这个过程？
我发现在数据范围属性下，有一个提示文件刷新名称。通过取消选中，不再需要单击导入每个链接的文件。每个链接数据源的属性必须单独调整。似乎没有任何能力多选择数据源。
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.
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.
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.
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屋！
