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
Post a Comment