vba - Excel summary of many excel files data into one report excel -

i have number of excel files containing filled survery, have 1 master document have summary result of each.

thus imaging have each file row input: name - address - data...

i open each of files, , copy data selected cells master file.

i have figured out can create invisible instance of excel, not shown user.

how can copy/paste data assume a1 sheet?

sub combine()   fpath = "c:\test\"    fname = dir(fpath & "*.xls")   dim xl excel.application   set xl = createobject("excel.application")   xl.visible = false   dim w workbook   dim remotebook workbook   set remotebook = xl.workbooks.open(fpath & fname)   xl.quit  end sub 

i new in vba, access way seems quite complicated, there easier way values excel files? realy wish have simple solution.

what more annoying vba macros in survey files can disable on openning user not prompted?

thanks!

those @ least 4 questions in one, let's see can :-)

first, there no need create excel instance, add code empty workbook (or master document, if that's ok you)

 public function openworkbook(mypath string) workbook      workbooks.open filename:=mypath      set openworkbook = activeworkbook  end function 

usage:

 dim wb workbook, ws worksheet  set wb = openworkbook("your path")  set ws = wb.sheets(1) 

avoiding 2nd excel instance automatically solves problem vba macros in survey documents - when open workbook within running macro way above, user won't security warning. (by way, survey documents have contain macros?)

copying data sheet workbook goes this:

 thisworkbook.sheets("name of sheet").range("a1") = ws.range("a1") 

iterating through bunch of excel files in folder described, example, here

hope helps.