+ Reply to Thread
Results 1 to 10 of 10

Batch creating multiple .txt files and with search and replace

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Batch creating multiple .txt files and with search and replace

    So I have a need to create 600 .txt files, each one a with a slight variation from the last one. For each .txt file I need to make 9 find and replace queries and then save the file with a specified file name. I am wondering if anyone has ever done anything like this in the past and is willing to share their code with me. I have a basic understanding of VBA and I would still consider myself a beginner. Any ideas or links to previous posts (I have tried searching already) would be greatly appreciated.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Batch creating multiple .txt files and with search and replace

    kevenson,

    I use this code to export all sheets in a workbook as individual text files. Hopefully that gives you a starting point. It sounds like you'll need to do your find/replaces within the loop. If this isn't what you're looking, can you provide more information so we can give you more accurate help?
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    08-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Batch creating multiple .txt files and with search and replace

    Tiger, that code will be extremely valuable to me! What I now need to do is find a way to open each .txt file into an individual sheet, the sheet name must be the same as the .txt file that gets imported.

    Here is what I have so far for batch find and replace

    Please Login or Register  to view this content.
    Last edited by Cutter; 08-27-2012 at 05:14 PM. Reason: Added code tags

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Batch creating multiple .txt files and with search and replace

    @ kevenson

    Welcome to the forum.

    Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Batch creating multiple .txt files and with search and replace

    kevenson,

    So you're importing text files into a single workbook, with each sheet being the name of a different text file? And after the import, run the find/replace on the data in each sheet? Is that right?

    (And thanks Cutter for adding the code tags )

  6. #6
    Registered User
    Join Date
    08-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Batch creating multiple .txt files and with search and replace

    Thank you Cutter, I'll make sure to do so in the future and yes to what Tiger is asking, I plan to import each .txt file into a separate sheet, with the sheet name the same as the imported text file. Then I plan to search through all of the sheets at once finding and replacing a list of words (mixed text and numbers). Once the replacements are made I want to be able to save each sheet individually based on its given sheet name.

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Batch creating multiple .txt files and with search and replace

    kevenson,

    Are all of the text files in the same folder? Are they tab delimited or comma delimited, etc? Do you want to save the sheets as excel files or as text files?
    Assuming yes, tab delimited, and excel files:
    Please Login or Register  to view this content.
    Last edited by tigeravatar; 08-27-2012 at 06:22 PM.

  8. #8
    Registered User
    Join Date
    08-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Batch creating multiple .txt files and with search and replace

    WOW! I never expected anyone on the forum to write the entire code for me. This is great TigerAvatar. I've now run the code but when i open one of the exported workbooks the original content has disappeared and been replaced by the text "ÿþ". I am uploading one of the .txt files under this post. What would really put the icing on the cake is if this code saved them in .txt format rather than .xls
    Attached Files Attached Files

  9. #9
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Batch creating multiple .txt files and with search and replace

    kevenson,

    Because the output is a text file, the VBA code doesn't need to import anything into Excel, which simplifies the code. Because of that strange "ÿþ" (I have no idea where that is coming from), I changed the method of reading the text file. This seems to have taken care of the issue. Give it a try and let me know
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Batch creating multiple .txt files and with search and replace

    Works perfectly, thank you so very much. I hope this helps someone else in the future as well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1