excel - Open Text File and Split() with exclamation mark as delimiter -


should simple think, can't seem work. tried 2 methods opening text file (.txt) excel , splitting other option: "!"

set workbookbk = workbooks.opentext(fullfilepath, xlmsdos, _ xldelimited, xldoublequote, false, false, false, false, false, true, "!") 

this syntax fine says "compile error: expected function or variable" missing?!

i used

set workbk = workbooks.open filename:= fullfilepath, delimiter:= "!" 

this opens file ok, doesn't split file @ all

help appreciated

if you're going skip arguments method, need explicitly specify arguments are supplying:


workbook.opentext syntax (from msdn)

expression.opentext(filename, origin, startrow, datatype, textqualifier, consecutivedelimiter, tab, semicolon, comma, space, other, otherchar, fieldinfo, textvisuallayout, decimalseparator, thousandsseparator, trailingminusnumbers, local)

expression variable represents workbooks object.


in code, have missed out arguments, compiler assume have supplied arguments in expected order method unless specify which arguments passing. example:

workbooks.opentext filename:=fuullfilepath, origin:=xlmsdos, datatype:=xldelimited, other:=true, otherchar:="!" 

also, might not answer hoping for, wrote udf accomplish similar thing without using .opentext() method while ago:

function mm_opentextfile(vpath string, delim string) variant  dim ff integer dim linearray variant dim temp string dim arraylist object  set arraylist = createobject("system.collections.arraylist")  ff = freefile  open vpath input #ff     while not eof(ff)         line input #ff, temp         linearray = split(temp, delim)         arraylist.add linearray         erase linearray     wend close #ff  mm_opentextfile = worksheetfunction.transpose(worksheetfunction.transpose(arraylist.toarray()))  arraylist.clear  set arraylist = nothing  end function 

here's example of how use it:

sub foo()  dim ar variant '// must variant work     '// change file , delimiter of choosing... ar = mm_opentextfile("c:\users\macro man\somefile.txt", "!")  range("a1").resize(ubound(ar, 1), ubound(ar, 2))     .numberformat = "@" '// change format "text"     .value = ar '// insert array values end  end sub 

Comments

Popular posts from this blog

sublimetext3 - what keyboard shortcut is to comment/uncomment for this script tag in sublime -

java - No use of nillable="0" in SOAP Webservice -

ubuntu - Laravel 5.2 quickstart guide gives Not Found Error -