Excel VBA script to copy columns into separate text files under separate directories -

i have worksheet contains 27 columns, first row columns headers a-z , num totaling 27 cols. each column has long list of restricted urls sorted letter of column, , last (27th) column urls start number. columns' length between 300-600 thousand cells.

what after copy each column separate text file (*.file) under separate folders, ie column copied , saved c:/blacklist/a/a.file, , on, c:/blacklist/b/b.file way c:/blacklist/num/num.file.

i have been searching solution , found following vba script, close after, at: http://www.ozgrid.com/forum/showthread.php?t=142181

option explicit   public sub columns_2_textfile()   const my_path = "c:\textfiles\"  dim icol integer  dim lrow long  dim file_num long   on error resume next  if trim(dir(my_path, vbdirectory)) = ""      mkdir my_path  else      kill my_path & "*.txt"  end if  on error goto 0  file_num = freefile  activesheet      icol = 2 256          open my_path & trim(.cells(2, icol).value) & ".txt" output #file_num          lrow = 3 .cells(rows.count).end(xlup).row              print #file_num, .cells(lrow, icol).value          next          close #file_num      next  end   msgbox "all files created , saved : " & my_path   end sub  

there 2 issues script: first not create text files under separate folders, instead creates files under 1 folder. second when tried it, did not copy columns contents in created files, in other words files empty 0 contents.

i've not tested this, no guarantees. you'll need change "sheet1" name of sheet.

public sub main() dim path string: path = "c:\blacklist\" dim column integer dim row long dim name string dim file long dim sheet worksheet: set sheet = thisworkbook.worksheets("sheet1")  column = 1 27     name = sheet.cells(1,column).value2     on error resume next     if trim(dir(path & name & "\", vbdirectory)) = ""         mkdir path & name & "\"     else         kill path & name & "\*.file"     end if     on error goto 0     file = freefile     open path & name & "\" & name & ".file" output #file         row = 2 sheet.cells(sheet.rows.count, column).end(xlup).row ' fixed             print #file, sheet.cells(row, column).value2         next row     close #file next column  end sub 

update should work now.