Excel summary of many excel files data into one report excel

Question

Hi

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!

Answer 1

You are on the right track. It is not difficult and I don't think there is an easier way to get data from another workbook. Write a function and you can reuse the code easily.

If the macros are only needed to compile the survey data, you can remove the macros from the xls file that is filled out. Otherwise you will need to modify the security permissions on each computer or digitally sign the project.

Answer 2

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.