vba - Import text files to excel and create master workbook -


i grad student , collect lot of data stored in txt files. want import text files fixed width, columns a, b , c 12, save files excel files , move them master workbook. found following code worked making master workbook not import them in numerical order.

i using microsoft 2010.

sub merge2multisheets() dim wbdst workbook dim wbsrc workbook dim wssrc worksheet dim mypath string dim strfilename string application.displayalerts = false application.enableevents = false application.screenupdating = false mypath = "c:\users\kyle\desktop\scan rate study 1-14-16" set wbdst = workbooks.add(xlwbatworksheet) strfilename = dir(mypath & "\*.xls", vbnormal) if len(strfilename) = 0 exit sub until strfilename = "" set wbsrc = workbooks.open(filename:=mypath & "\" & strfilename) set wssrc = wbsrc.worksheets(1) wssrc.copy after:=wbdst.worksheets(wbdst.worksheets.count) wbsrc.close false strfilename = dir() loop wbdst.worksheets(1).delete application.displayalerts = true application.enableevents = true application.screenupdating = true end sub 

consider using fso object traverse mypath directory , copy on .xls files. currently, set importing excel files located in directory:

sub merge2multisheets()     dim mypath string     dim wbdst workbook, wbsrc workbook, wssrc worksheet     dim fso object, olfolder object, olfile object      application.displayalerts = false     application.enableevents = false     application.screenupdating = false     mypath = "c:\users\kyle\desktop\scan rate study 1-14-16"      set wbdst = workbooks.add(xlwbatworksheet)      set fso = createobject("scripting.filesystemobject")     set olfolder = fso.getfolder(mypath)      each olfile in olfolder.files         if right(olfile.name, 4) = ".xls"              set wbsrc = workbooks.open(olfile.name)             set wssrc = wbsrc.worksheets(1)             wssrc.copy after:=wbdst.worksheets(wbdst.worksheets.count)             wbsrc.close false          end if     next olfile      wbdst.worksheets(1).delete     application.displayalerts = true     application.enableevents = true     application.screenupdating = true      set olfile = nothing     set olfolder = nothing     set fso = nothing end sub 

Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -