vba - Importing text files to xlsm, then overwriting sheetname if already exsits -

after hours of failed googled vb scripts, thought i'd come here.

currently have modified vba script imports multiple txt files, , copies them new sheets in xlsm file, according txt file name.

i want 2 things, {solved} answers on google don't seem working me.

1) overwrite existing sheet if exists --- (nb: not delete it... linked sheet calculations), and

2) import text file in space delimited format --- again, solved answers not playing game.

thanks (ps -- there several similar questions here, have similar solved answers question, seem more convoluted... i'm after simple possible)

sub getsheets() path = "c:\test\" filename = dir(path & "*.txt") while filename <> "" workbooks.open filename:=path & filename, readonly:=true each sheet in activeworkbook.sheets sheet.copy after:=thisworkbook.sheets(1) next sheet workbooks(filename).close filename = dir() loop end sub 

i faced same problem while ago, similar constraint 1st : - keep sheet because of references pointing there

however didn't have 2nd constraint, therefore can't tell if matches 100% ; i'm pretty sure can work through. suggest perform import query temporary sheet, , use copy-paste macro operation move well-defined range final destination

i solved using query import. came using "macro recorder" "csv import" ; refactored code.

' @brief importfile : opens specified file , imports contents @ destination ' @param impfilename : path file import ' @param impdest : location of destination (must single cell range)  private sub importfile(impfilename string, impdest range)  impdest.worksheet.querytables.add(connection:= _   "text;" & impfilename, destination:=impdest)   .name = "import"   .fieldnames = true   .rownumbers = false   .filladjacentformulas = false   .preserveformatting = true   .refreshonfileopen = false   .refreshstyle = xloverwritecells   .savepassword = false   .savedata = true   .adjustcolumnwidth = true   .refreshperiod = 0   .textfilepromptonrefresh = false   .textfileplatform = 65001   .textfilestartrow = 1   .textfileparsetype = xldelimited   .textfiletextqualifier = xltextqualifierdoublequote   .textfileconsecutivedelimiter = false   .textfiletabdelimiter = false   .textfilesemicolondelimiter = false   .textfilecommadelimiter = true   .textfilespacedelimiter = false   .textfilecolumndatatypes = array(1, 1, 1, 1)   .textfiletrailingminusnumbers = true   .refresh backgroundquery:=false  end  ' query execution not trigger "content change event", force triggering  ' editing 1st cell's content.  dim myval variant  myval = impdest.cells(1, 1).value  impdest.cells(1, 1) = myval  end sub 

you might want change of query options fit need.

nb: 3 last lines there fix bug (or looked bug) : query execution not trigger "calculate" event on referees.