Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jul 29th, 2012  |  syntax: None  |  size: 1.39 KB  |  hits: 10  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Text file in VBA: Open/Find Replace/SaveAs/Close File
  2. Open text File
  3.  
  4. Find "XXXXX" and Replace with "YYYY"
  5.  
  6. Save text File As
  7.  
  8. Close text file
  9.        
  10. Private Sub CommandButton1_Click()
  11.  
  12. Dim sBuf As String
  13. Dim sTemp As String
  14. Dim iFileNum As Integer
  15. Dim sFileName As String
  16.  
  17. ' Edit as needed
  18. sFileName = "C:filelocation"
  19.  
  20. iFileNum = FreeFile
  21. Open sFileName For Input As iFileNum
  22.  
  23. Do Until EOF(iFileNum)
  24. Line Input #iFileNum, sBuf
  25. sTemp = sTemp & sBuf & vbCrLf
  26. Loop
  27. Close iFileNum
  28.  
  29. sTemp = Replace(sTemp, "DIM A", "1.75")
  30. sTemp = Replace(sTemp, "DIM B", "2.00")
  31. sTemp = Replace(sTemp, "DIM C", "3.00")
  32. sTemp = Replace(sTemp, "DIM D", "4.00")
  33.  
  34. 'Save txt file as (if possible)
  35.  
  36. iFileNum = FreeFile
  37. Open sFileName For Output As iFileNum
  38.  
  39. Print #iFileNum, sTemp
  40.  
  41. Close iFileNum
  42.  
  43. 'Close Userform
  44. Unload UserForm1
  45.  
  46. End Sub
  47.        
  48. sFileName = "C:someotherfilelocation"
  49.        
  50. Open sFileName For Output As iFileNum
  51.        
  52. sFileName = Application.GetSaveAsFilename()
  53.        
  54. Sub ReplaceStringInFile()
  55.  
  56. Dim sBuf As String
  57. Dim sTemp As String
  58. Dim iFileNum As Integer
  59. Dim sFileName As String
  60.  
  61. ' Edit as needed
  62. sFileName = "C:Temptest.txt"
  63.  
  64. iFileNum = FreeFile
  65. Open sFileName For Input As iFileNum
  66.  
  67. Do Until EOF(iFileNum)
  68.     Line Input #iFileNum, sBuf
  69.     sTemp = sTemp & sBuf & vbCrLf
  70. Loop
  71. Close iFileNum
  72.  
  73. sTemp = Replace(sTemp, "THIS", "THAT")
  74.  
  75. iFileNum = FreeFile
  76. Open sFileName For Output As iFileNum
  77. Print #iFileNum, sTemp
  78. Close iFileNum
  79.  
  80. End Sub
create a new version of this paste RAW Paste Data