Advanced Search

Exporting Excel cells as individual text files

/ Published in: VB.NET
Save to your folder(s)
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

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.