excel - VBA Copy used range to text file -

i want run code in vba following:

• copies used range of sheet called "kommentar"
• creates ".txt" file ("kommentar.txt") in same directory thisworkbook
• pastes copied used range
• saves ".txt" file

what have until is:

sub createafile()  dim pth string pth = thisworkbook.path dim fs object set fs = createobject("scripting.filesystemobject") dim object set = fs.createtextfile(pth & "\kommentar.txt", true) dim sh worksheet set sh = thisworkbook.sheets("kommentar")  dim rng range set rng = sh.usedrange a.writeline (rng) a.close  end sub 

i run-time error '13' mismatch, in line "a.writeline (rng)", function doesn't accept range written.

any ideas? thanks!

since range made of several cells, have loop through them text string variable. if use variant variable can copy values , automatically array correct dimensions of data in cells, loop , copy text:

function gettextfromrangetext(byval porange range) string     dim vrange variant     dim sret string     dim integer     dim j integer      if not porange nothing          vrange = porange          = lbound(vrange) ubound(vrange)             j = lbound(vrange, 2) ubound(vrange, 2)                 sret = sret & vrange(i, j)             next j             sret = sret & vbcrlf         next     end if      gettextfromrangetext = sret end function 

call function in code replacing a.writeline (rng) line following:

dim srange string srange = gettextfromrangetext(rng) call a.writeline(srange)