Text file in VBA Open Find Replace SaveAs Close File

Question

Here is pseudocode for what I am hoping to do   Open text File  Find  XXXXX  and Replace with  YYYY   Save text File As  Close text file   This is what I have so far  Private Sub CommandButton1 Click    Dim sBuf As String Dim sTemp As String Dim iFileNum As Integer Dim sFileName As String    Edit as needed sFileName    C  filelocation   iFileNum   FreeFile Open sFileName For Input As iFileNum  Do Until EOF iFileNum  Line Input  iFileNum  sBuf sTemp   sTemp  amp  sBuf  amp  vbCrLf Loop Close iFileNum  sTemp   Replace sTemp   DIM A    1 75   sTemp   Replace sTemp   DIM B    2 00   sTemp   Replace sTemp   DIM C    3 00   sTemp   Replace sTemp   DIM D    4 00     Save txt file as  if possible   iFileNum   FreeFile Open sFileName For Output As iFileNum  Print  iFileNum  sTemp  Close iFileNum   Close Userform Unload UserForm1  End Sub   But instead of overwriting the original text file  I want to  save as  to a new file

User · Accepted Answer

Just add this line  sFileName    C  someotherfilelocation    right before this line  Open sFileName For Output As iFileNum   The idea is to open and write to a different file than the one you read earlier  C  filelocation     If you want to get fancy and show a real  Save As  dialog box  you could do this instead   sFileName   Application GetSaveAsFilename

User · Answer

Why involve Notepad   Sub ReplaceStringInFile    Dim sBuf As String Dim sTemp As String Dim iFileNum As Integer Dim sFileName As String    Edit as needed sFileName    C  Temp test txt   iFileNum   FreeFile Open sFileName For Input As iFileNum  Do Until EOF iFileNum      Line Input  iFileNum  sBuf     sTemp   sTemp  amp  sBuf  amp  vbCrLf Loop Close iFileNum  sTemp   Replace sTemp   THIS    THAT    iFileNum   FreeFile Open sFileName For Output As iFileNum Print  iFileNum  sTemp Close iFileNum  End Sub

User · Answer

This code will open and read lines of complete text file That variable  ReadedData  Holds the text line in memory  Open  C  satheesh myfile Hello txt  For Input As  1  do until EOF 1             Input  1  ReadedData loop

User · Answer

Guess I m too late      Came across the same problem today  here is my solution using FileSystemObject   Dim objFSO Const ForReading   1 Const ForWriting   2 Dim objTS  define a TextStream object Dim strContents As String Dim fileSpec As String  fileSpec    C  Temp test txt  Set objFSO   CreateObject  Scripting FileSystemObject   Set objTS   objFSO OpenTextFile fileSpec  ForReading  strContents   objTS ReadAll strContents   Replace strContents   XXXXX    YYYY   objTS Close  Set objTS   objFSO OpenTextFile fileSpec  ForWriting  objTS Write strContents objTS Close

User · Answer

I have had the same problem and came acrosse this site   the solution to just set another  filename  in the      for output as     command was very simple and useful   in addition  beyond the Application GetSaveAsFilename   Dialog   it is very simple to set a   new filename    just using   the replace command  so you may change the filename extension  eg   as from the first post   sFileName    C  filelocation  iFileNum   FreeFile  Open sFileName For Input As iFileNum content       edit the content    Close iFileNum   now just set   newFilename   replace sFilename    txt     csv      to change the extension  or  newFilename   replace sFilename         edit      for a differrent filename  and then just as before  iFileNum   FreeFile Open newFileName For Output As iFileNum  Print  iFileNum  content Close iFileNum    I surfed over an hour to find out how to rename a txt-file   with many different solutions  but it could be sooo easy

[vba] Text file in VBA: Open/Find Replace/SaveAs/Close File

Examples related to vba

Examples related to text-files