+ Reply to Thread
Results 1 to 16 of 16

Merge Txt file with condition

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Merge Txt file with condition

    Hello:
    Please refer to attached file.

    I am using below code to merge txt files located @ C:\Users\Riz\Desktop\Temp\*.txt.
    All txt files are just 1 line with continuous number example as below:

    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA0713BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
    Character 59 is 0
    Character 60 is 7

    Merging works great without any problem.
    However, i need to add code within so that it needs to check char 59 and 60 (above example : 07).
    If this is NOT equal to the value in cell L36 ( in attached file it is 05) then Message "The TXT File Name " _____ " will be skipped".
    and Skip this file in the merge process.

    Let me know if you have any questions.
    Thanks.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by rizmomin; 06-29-2015 at 10:42 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hello:

    Please refer to attached files.
    I am attaching 3 txt files and 1 merged file.
    As you can see File name 12.txt has character 59 as 0 and character 60 as 6 and hence this should be skipped since in the attached excel file cell L36 is 07

    Let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files

  3. #3
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Merge Txt file with condition

    Hi,

    Probably this :

    Please Login or Register  to view this content.


    Regards

  4. #4
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hi karedog:

    Seems to work.
    Can we send message which file are we skipping :
    "If this is NOT equal to the value in cell L36 ( in attached file it is 05) then Message "The TXT File Name " _____ " will be skipped".
    and Skip this file in the merge process."

    Please let me know if you have any questions.
    Thanks.

    Riz

  5. #5
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Merge Txt file with condition

    You are welcome.
    Sure, just change this part of code :
    Please Login or Register  to view this content.
    to this :
    Please Login or Register  to view this content.

    Regards

  6. #6
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hi Karedog:

    Superb, Superb....Thanks a lot.
    Will test further and will let you know if any problem.
    Once again thanks for great help.
    Riz

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Merge Txt file with condition

    You are welcome Riz, and many thanks for the reps.


    Regards

  8. #8
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hi Karedog:

    May i ask you a favor to add some code to do the following.
    The files which are NOT Skipped, please copy from each text file : Character # 15 to 20 in cell A36 and down and character # 36-40 in cell B36 and down.
    So assume you have 2 files which are not skipped then i would have data filled in cell A36:B37.

    Please let me know if you have any questions.
    Thanks.

    Riz

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hi :

    I think i got it working.

    Thanks

    Riz
    Last edited by rizmomin; 06-30-2015 at 10:45 AM. Reason: Added Code Tags.

  10. #10
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hi karedog and others:

    I am seeing something strange.
    I am using the below exact code in 2 seperate workbooks.
    Works without any problem in one and in other one it only reads first text file and then exit out the sub.
    Please guide me if any thing is wrong so that i can correct and use the code in my actual workbook.

    Please Login or Register  to view this content.
    Please let me know if you have any questions.
    Thanks.

    Riz

  11. #11
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Merge Txt file with condition

    Your code seemed fine to me.
    I suspect the two workbooks have different value of cell L36 in sheet "DQ_Daily_Reports"

    Let me review your current code :
    - Your code doesn't have error trapping (On Error ....), means no suddenly exit because an error is happened
    - Both of your workbooks try to find all *.txt file in folder "C:\Users\Riz\Desktop\Temp\TextFiles\"
    - Both of your workbooks try to output to file "C:\Users\Riz\Desktop\Temp\Merge941\MergedEFTPS.txt"
    - Both of your workbooks has the "DQ_Daily_Reports" sheet

    And since they produce different result (without any error pop up), I think it is caused by different value of cell L36 in sheet "DQ_Daily_Reports".


    Regards

  12. #12
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hi kardog:

    I have checked all of the above and all seems to be ok but still the code only opens 1st txt file.
    I will send you pics little later to show you the result.
    Thanks
    Riz

  13. #13
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hi karedog:

    Please refer to attached pic.
    This shows the code I am using for the problem worksheet with folder where txt files will be read and folder where it will be merged.
    Also if you red circle data, it shows that it only read 1st txt file although the rest of the file has "07" as in the condition.
    The only differance between the 2 workbook is that this problem workbook has lots of other data/macro but the other one does not.
    However, i am not seeing any thing wrong with that.
    Please let me know if you have any questions.
    Thanks.

    Riz
    Attached Images Attached Images

  14. #14
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Merge Txt file with condition

    How if use this "primitive but powerfull" debugging method, what does it show ?
    Does it miss the second line message below for qualified files ?

    Now processing file : something.txt
    File : something.txt , i = 37, L36 = 07

    Please Login or Register  to view this content.

  15. #15
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Merge Txt file with condition

    Hello karedog:

    the above code displays:

    Now processing file : 6.txt
    File : 6.txt , i = 37, L36 = 07
    and then ends.

    How about i change Do while File_To_Open <> "" to 1 to 11 as i have 11 files in the folder.
    What code changes would i neeed to do for this so that i can try.

    Also please refer to attached pic which shows that the same files are processed with the same code in other workbook.

    Please let me know if you have any questions.
    Thanks.

    Riz
    Attached Images Attached Images
    Last edited by rizmomin; 07-01-2015 at 09:34 AM.

  16. #16
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Merge Txt file with condition

    This is strange.

    You start with initial value i = 35
    Please Login or Register  to view this content.
    and for the first found file :
    Please Login or Register  to view this content.
    the value of i is increased by one, so now i = 36

    But from your post, you say i = 37, I really don't know how.
    Now processing file : 6.txt
    File : 6.txt , i = 37, L36 = 07



    Just curious, how if you delete this line (all the line, including trailing spaces) :
    Please Login or Register  to view this content.
    and then retype it manually by hand (not by copy paste).

    Also, do you have event code ? (Worksheet_Change, etc)
    And how do you call this sub, from a button, or launch from other sub ?


    Quote Originally Posted by rizmomin View Post
    How about i change Do while File_To_Open <> "" to 1 to 11 as i have 11 files in the folder.
    No, that is unwise, if the number of found files is less than 11, it will produce error, the current code is already good.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Email merge from excel merge file using pre-saved word template
    By d_max_c in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2014, 12:33 PM
  2. [SOLVED] Merge cell with condition using MACRO for 2007
    By rpriyadharsini1987 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 07-26-2013, 06:28 AM
  3. Replies: 2
    Last Post: 01-22-2013, 11:37 AM
  4. Merge rows by condition from one workbook to another
    By WilliamV in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-09-2012, 11:31 AM
  5. Merge ranges based on condition
    By BrianDP1977 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-08-2005, 09:20 PM

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