Posted By

BlueCockatoo on 02/06/09


Tagged

excel vba automation vbscript OLE


Versions (?)

Exporting Excel cells as individual text files


 / Published in: VB.NET
 

This is modified from code found here: http://www.meadinkent.co.uk/XLexport-text1.htm and here: http://www.meadinkent.co.uk/XLexport-text2.htm

  1. Sub ExportToTextFiles()
  2. Dim FirstRow As Integer, LastRow As Integer, MyFileName As String, MyRow As Integer, MyStr As String
  3.  
  4. ' change these values to the start row and end row for your notes.
  5. ' if you have to use a range you'll need a double loop below
  6. FirstRow = 1
  7. LastRow = 4
  8.  
  9. For MyRow = FirstRow To LastRow
  10. MyFileName = "C:\formtest" & Str(MyRow) & ".txt"
  11.  
  12. If DoesFileExist(MyFileName) = 0 Then
  13. Open MyFileName For Output As #1 ' create a new file and record if file does not exist
  14. Else
  15. Open MyFileName For Append As #1 ' append a record if file does already exist
  16. End If
  17.  
  18. ' loop through each row of the table
  19. MyStr = Cells(MyRow, 1).Value
  20. Print #1, MyStr
  21. Close #1
  22. Next
  23.  
  24. MsgBox "Done"
  25. End Sub
  26.  
  27. Function DoesFileExist(FileName As String) As Byte
  28. ' returns 1 if FileName exists, otherwise 0
  29. If Dir(FileName, vbNormal) <> "" Then
  30. DoesFileExist = 1
  31. Else
  32. DoesFileExist = 0
  33. End If
  34. End Function

Report this snippet  

You need to login to post a comment.