Excel汇总了许多excel文件数据转化成一个报表excel [英] Excel summary of many excel files data into one report excel

问题描述

我有一些excel文件包含填充，现在我想要
有一个主文档将得到每个的总结。

因此，我成像为每个文件一行输入：
名称 - 地址 - 一些数据...

我想打开每个文件，并将所选单元格中的数据复制到我的主文件中。

我已经知道我可以创建不可见的Excel实例，因此它不会显示
给用户

如何将A1中的数据复制/粘贴到表格中？

  Sub Combine（）
 Fpath =c：\test\
 Fname = Dir（Fpath&* .xls）
 Dim xl作为Excel.Application 
设置xl = CreateObject（Excel.Application）
 xl.Visible = false 
 Dim w As Workbook 
 Dim remoteBook As Workbook 
设置remoteBook = xl.Workbooks 。开（Fpath& Fname）
 xl.Quit 
 End Sub 
  

我在VBA中是新的，访问方式似乎相当复杂，有没有更简单的方法
从这些excel文件中获取值？我真的希望有简单的解决方案。

更多烦人的是，调查文件中的VBA宏可以禁用那些打开
，因此用户没有提示？ / p>

谢谢！

解决方案

这些至少有4个问题，让我们看看我们可以做些什么： - ）

首先，没有必要创建另一个Excel实例，只需将你的代码添加到一个空的工作簿（或你的主文档，如果可以的话）

 公共功能OpenWorkbook（mypath As String）作为工作簿
 Workbooks.Open文件名：= mypath 
设置OpenWorkbook = ActiveWorkbook 
结束功能
  

用法： / p>

  Dim wb as workbook，ws as workheet 
 set wb = OpenWorkbook（your path）
 set ws = wb.Sheets（1）
  

避免第二个Excel实例自动解决您的任何VBA宏的问题在你的sur vey文档 - 当您从正在运行的宏中打开另一个工作簿时，用户不会得到任何安全警告。 （顺便说一句，调查文件必须包含任何宏？）

将数据从该工作表复制到工作簿中如下所示：

  ThisWorkbook.Sheets（您的工作表名称）。范围（A1）= ws.Range（A1）
  

描述了一个文件夹中的一堆Excel文件，例如 here

希望有所帮助。 / p>

I have a number of excel files containing filled survery, now I would like to have one master document that would have summary result of each.

Thus I imaging to have for each file a row input: name - address - some data...

I would like to open each of the files, and copy the data from selected cells into my master file.

I have figured out that I can create invisible instance of Excel, thus it will not be shown to the user.

How can I copy/paste the data assume from A1 into my sheet?

Sub Combine()
  Fpath = "c:\test\" 
  Fname = Dir(Fpath & "*.xls")
  Dim xl As Excel.Application
  Set xl = CreateObject("Excel.Application")
  xl.Visible = false
  Dim w As Workbook
  Dim remoteBook As Workbook
  Set remoteBook = xl.Workbooks.Open(Fpath & Fname)
  xl.Quit
 End Sub

I am new in VBA, the access way seems to be quite complicated, is there easier way to get values from those excel files? I realy wish to have simple solution.

What is more annoying are VBA macros in survey files can I disable those on openning so user is not prompted?

thanks!

解决方案

Those are at least 4 questions in one, let's see what we can do about it :-)

First, there is no need to create another Excel instance, just add your code into an empty workbook (or into your master document, if that's ok for you)

 Public Function OpenWorkbook(mypath As String) As Workbook
     Workbooks.Open Filename:=mypath
     Set OpenWorkbook = ActiveWorkbook
 End Function

Usage:

 Dim wb as workbook, ws as worksheet
 set wb = OpenWorkbook("your path")
 set ws = wb.Sheets(1)

Avoiding the 2nd Excel instance automatically solves your problem with any VBA macros in your survey documents - when you open another workbook from within a running macro like the way above, the user won't get any security warning. (By the way, do the survey documents have to contain any macros?)

Copying data from that sheet into your workbook goes like this:

 ThisWorkbook.Sheets("Name of your sheet").Range("A1") = ws.Range("A1")

Iterating through a bunch of Excel files in a folder is described, for example, here

Hope that helps.

这篇关于Excel汇总了许多excel文件数据转化成一个报表excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！