VBA Excel copy text file to worksheet -

i'm trying take statistics of specific column in text file , thought best way might copy contents text file excel worksheet , count there (otherwise need try , read 1 line directly excel file). here's code of have far:

dim filepath string dim currentvalue string dim irow long dim icol long dim badaddress long dim coveragenolisting long dim activelisting long dim nocoveragenolisting long dim inactivelisting long dim fso object dim f object  '' filepath include entire file name (picked browser button) filepath = activesheet.range("b2").text  '' makes sure there isn't sheet named "temp_text_file" each testsheet in activeworkbook.worksheets     if testsheet.name "temp_text_file" flag = true: exit next  '' if there sheet named "temp_text_file" deleted if flag = true     application.displayalerts = false     activeworkbook.sheets("temp_text_file").delete     application.displayalerts = true end if  '' recreate sheet sheets.add.name = "temp_text_file" '' here want copy (similar manually doing "ctrl+a" "ctrl+c") text file  '' paste worksheet (similar manually doing "ctrl+v") within created worksheet range("a1")  '' delete @ end (user has no need it) application.displayalerts = false activeworkbook.sheets("temp_text_file").delete application.displayalerts = true 

thank you,

jesse smothermon

i doing similar thing, here sub this:

i open txt file | separator. copy content of sheet destination workbook (global variable). close first workbook contains original txt file without saving.

the code workbooks.opentext recording macro , adapting needs.

sub importtextfile(path string)     dim sheetname string     dim tmpworkbook workbook     dim filepath string     dim txtfilepath string     dim txtfilename string      set wb = thisworkbook      sheetname = "test_result"     txtfilename = path & "file.txt"      workbooks.opentext filename:= _         txtfilename _         , origin:=437, startrow:=1, datatype:=xldelimited, textqualifier:= _         xldoublequote, consecutivedelimiter:=false, tab:=false, semicolon:=false, _         comma:=false, space:=false, other:=true, otherchar:="|", fieldinfo:=array(array(1, 1), _         array(2, 1)), decimalseparator:=".", thousandsseparator:=",", _         trailingminusnumbers:=true      set tmpworkbook = activeworkbook     tmpworkbook.sheets("file").select     cells.select     selection.copy      resultwb.activate      resultwb.sheets(sheetname).select     range("a1").select     activesheet.paste     application.cutcopymode = false     cells.select     cells.entirecolumn.autofit     activesheet.range("a1").select     tmpworkbook.close savechanges:=false  end sub