November 17, 2004

• Just so you know, I checked googlegroups first and all the listings I saw either didn't do what I want or were impenetrable masses of VB programming, which I understand pitifully little of.
• I can't get

   to work so bear with me.
  
  Some macro code (change A1 to suit your needs):
  
      Range("A1", Range("A1").End(xlDown)).Select
  
      Dim cell As Object
      Dim counter, i As Integer
      Dim cellArray() As Variant
      
      'generate cell array
      
      counter = 1
     
      For Each cell In Range(ActiveCell, ActiveCell.End(xlDown))
          ReDim Preserve cellArray(counter)
          cellArray(counter) = cell.Value
          counter = counter + 1
      Next cell
  
      'copy array to another worksheet
  
      ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
      ActiveSheet.Name = "Destination"
  
      i = 1
  
      For i = 1 To counter - 1
          Worksheets("Destination").Cells(i, 1).Value = cellArray(i)
      Next
  
  From there you can add a command for exporting the Destination worksheet to a text file. One such function here for automating the process:
  
  • http://www.vba-programmer.com/Excel_Code/Creating_Text_File.txt
  
  
  

• Alex: <code> works here.
• Fes: in your 'whole column' text file, what separates the rows?
• Alex: so, if I'm reading that macro right, you make it take each cell and convert it into a separate worksheet? and then, with the linked code, I make it take each worksheet and covnert it to text - that correct?
• Should be working on my presentation for tomorrow, but distractions are nice... Macro code:  Filepath = InputBox("Enter a filename, including full path (row number will be appended)", "", "C:\xceldata")  For i = 1 To Selection.Rows.Count   Filename = Filepath & i & ".txt"   Open Filename For Output As #1 ' Open file for output.   Print #1, Selection.Cells(i, 1).Value ' Write data.   Close #1 ' Close file.  Next i Or download it on an excel sheet from here. Now it's back to work.
• Richer - let me rephrase: I have a whole column, around 1500 rows long, of cells that each have a line of text (they're document titles and brief descriptions, if that makes a difference) in them. The rows themselves are discrete; I just need to take the text from each cell and somehow put it into a separate text file, the ultimate result being 1500 or so very small individual text files.
• Fes, yes. The macro takes each cell from a column that has a value of some kind in it, and copies those cells to a column in a second worksheet called "Destination". You can then do a File -> Save As... to save the worksheet to text. Or you can add that function I linked to and then call that function in the macro, to automatically save the worksheet to a text file. I like doing that part manually, myself, but if you need to automate, that's how you would do it.
• (Assuming it's \n -- a carriage return, and you have Python (the .exe, not win32all) installed..) # open the .txt file containing the column input = open(r'c:\path\to\colums.txt','r') i = 0 # write the content of each cell N in c:\path\to\cellN.txt for cell in input:   fname = r'c:\path\to\cell' + i + '.txt'   output = open(fname,'w')   output.write(cell)   output.close()   i += 1 input.close() In Python, the indenting is mandatory and significant. -- this code does not check for exceptions. Lines starting with # indicate comments.
• Thanks much! I think this will work. You all likely just saved me a bunch of scut work, and I am very much obliged.
• Ahah, I wrote exactly the same program as wanderingstan, but in a different language. (I don't know VB)
• Destination will have the entire column in one text file. What Richer and Wanderingstan wrote will split up each row cell value from Destination into its own uniquely numbered text file. I didn't read that part of your spec, sorry!