vba 从不同文件中提取数据的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17614859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
macro pulling data from different file
提问by user2576348
I have a daily dashboard file in which I'm pulling data on a daily basis from 5 different files with a macro. The macro works fine but it is asking which file I should choose for specific steps: Workbooks.Open vFile(a window appears and I just select the file). Those files are being changed on a weekly or monthly basis so what I want to do is to insert the source in a specific cell and the macro won't ask me for the file but will take the source there.
我有一个每日仪表板文件，我每天从 5 个不同文件中使用宏提取数据。宏工作正常，但它询问我应该为特定步骤选择哪个文件：（Workbooks.Open vFile出现一个窗口，我只选择文件）。这些文件每周或每月更改一次，所以我想要做的是将源插入特定单元格中，宏不会要求我提供文件，而是将源放在那里。
Is this possible?
这可能吗？
Code (I'm not putting the entire code but just the part I want to change)
代码（我没有把整个代码，只是我想改变的部分）
vFile = Application.GetOpenFilename("Excel Files (.xl)," & ".xl", 1, "select WF Report", "Open", False)
Workbooks.Open vFile
And then the macro is refering to the active file & sheet. Once I have all the data copied, another window prompts and I select the 2nd file etc.
然后宏是指活动文件和工作表。一旦我复制了所有数据，另一个窗口会提示我选择第二个文件等。
vFile = Application.GetOpenFilename("Excel Files (.xl)," & ".xl", 1, "select Front End Tracker ", "Open", False)
Workbooks.Open vFile
What I want to change is that, instead of having the window for file selection, I want the macro to take the source and name of the file from a specific cell in the Excel sheet. Any ideas on how to do so?
我想要改变的是，我希望宏从 Excel 工作表中的特定单元格中获取文件的来源和名称，而不是使用文件选择窗口。关于如何这样做的任何想法？
回答by Kazimierz Jawor
The answer is quite easy but I'll try to provide you some options.
答案很简单，但我会尽力为您提供一些选择。
A) if you put in cell A1 complete path name and file name, like:
A) 如果您在单元格 A1 中输入完整的路径名和文件名，例如：
c:\users\user_name\Documents\files\moj plik.xlsx
then you only need to change your code as follows:
那么你只需要改变你的代码如下：
'comment this line by adding ' at the beginning
'vFile = Application.GetOpenFilename("Excel Files (.xl)," & ".xl", 1, "select WF Report", "Open", False)
'change next line by adding reference to the cell
Workbooks.Open Range("A1")
'or with reference to sheet name and range A1- change according to your situation
Workbooks.Open Sheets("put here sheet name").Range("A1")
B) if you want to refer to file names only you need to define in some way path to the file. If your cell A1 has only file name like:
B) 如果你只想引用文件名，你需要以某种方式定义文件的路径。如果您的单元格 A1 只有文件名，例如：
moj plik.xlsx
then your code will be changed accordingly:
那么您的代码将相应更改：
'comment this line by adding ' at the beginning
'vFile = Application.GetOpenFilename("Excel Files (.xl)," & ".xl", 1, "select WF Report", "Open", False)
'change by adding reference to the cell and path constant
Dim xlsPath as String
xlsPath = "c:\users\user_name\Documents\files\"
'change above accordingly to what you have
Workbooks.Open xlsPath & Range("A1")
'or with reference to sheet name and range A1- change according to your situation
Workbooks.Open xlsPath & Sheets("put here sheet name").Range("A1")
