+ Reply to Thread
Results 1 to 24 of 24

Slight Modification Request VBA Code - Text File to Excel

  1. #1
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Slight Modification Request VBA Code - Text File to Excel

    Hi All,

    Request for some help on modification of the code (below). It is working fine at times and not on others.

    The problem that I’ve been able to identify is that at times the format of the data in the Text document used varies, for which the logics haven’t been incorporated. My challenge here is to do with the identification of the issues. There is tremendous amount of data in the text files I receive, and since the current code reads the complete file at one go rather than line by line, nothing actually happens when the macro is run, and I’m unable to determine the reason which may be causing this…

    Could there be a way to make customize it in a way where it helps me to spot the line in each of the notepad where there is a disconnect with the existing logic. I’ve attached my logic Text file on which the logics are based and this solution works accurately if the data is within those parameters

    I would really appreciate if you could help me customize or make this more dynamic. I’ve been stuck on this for a while now.

    Thanks you in advance!

    Please Login or Register  to view this content.
    Regards,
    Spi
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    Different method
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Dear Jindon,

    This seems to be working almost per my request. A few things that still need your expertise are mentioned in the attached Macro file.

    Iíve attached the notepad with the some cases as well for ease of understanding of my problem, with the expected output (highlighted in yellow) in Macro Sheet itself Row 16 - 25.

    Request you to assist me on these. It is otherwise working like a charm!

    Regards,
    Spi
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    See if this is what you wanted
    Please Login or Register  to view this content.
    Last edited by jindon; 03-14-2013 at 09:08 AM.

  5. #5
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    This is almost it from whatever I've tested uptil now

    A very small thing that was happening in the previous version but not on this one:

    • Case 1: 06:00 - 07:00 Ibiz - Style: K Kallis (Advanced) – in this case the word (Advanced) should get updated in Column G
    • Case 2: 06:30 - 07:00 Mandis: B Brown
    Jackie Call list 2-20.xlsx
    JP
    Revisit time line

    The result should get updated as JP, Revisit time line instead of JP Revisit time i.e. each line should be seprated via a comma as in (,)

    Thanks a bunch on this. It is working phenomenally!

    Best regards,
    Spi

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Superb! This is working like a miracle

    Only one thing, the previous version wasn't affecting my headers in Row 4, but when I run this macro it gets deleted. I really need to have it all the time. Rest everything is simply "PERFECT"

    Thanks,
    Spi

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Simply mindblowing!

    I'm closing the thread as "Solved" Thanks a ton for all this help. It had been pending for a while.

    I'll be testing it over the next few days on al sorts of files I get. Hope it will be alright to comeback if there are any challanges that I may encounter!

    You guys rock out here. It is a supreb place.

    Thanks much:-)

  10. #10
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Dear Jindon,

    I'm sorry, but truly should have mentioned this in the first place. There are several text files with different dates. In my initial test I discovered that I can't determine which data is coming from which file. I'm unsure, how hard would it be to add the file name in the first column from all the text files, but I'm sure it would be doable. The remaining headers remain as they are, but are moved to the right. Column A needs to have the file name (Example: 03-14-2013).

    Please advise.

    Best reagrds,
    Spi

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    Change to
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    You are really as quick as lightning!

    Just a little discrepancy:

    Column B is getting updated with 1/1/1900 12:00:00 AM as the default value, which shouldn't be the case. It should read S.No 1,2, 3 etc..
    Column I which was previously Column H is turning out to be blank. It should have notes after (....)

    These were absolutely right in the previous versions. Looks like the shift of the column may have resulted in it.

    Please advise.

    Thanks,
    Spi

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    1) It's just a matter of cell formatting.
    You need to change them.

    2) missed to change
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Fantastic!

    Yes, I realized point 1 immediately after I posted. It is working right now. I'll continue with my testing.

    Thanks for this awesome help!

  15. #15
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Dear Jindon,

    I've been running the solution and it is an awesome piece of work. You've helped me save a lot of time. The accuracy is also excellent.

    Only one thing that I noticed is that in some instances like Nat - West the "-" gets converted into a special character like ‚€“, and the result I get is Nat ‚€“ West, as against Nat - West.

    Could you please advise as to why this may be happening.

    Thanks a lot for this awesome solution!

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    Can't replicate it here.
    Need to see the txt file and the result that you get from the code.

  17. #17
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Dear Jindon,

    I think Iíve spotted the error. The difference is between the signs Ė is lengthier as Ė which is causing a problem. Other than that there are a few others like youíll, the apostrophe sign which gets converted into a special character.

    Example: Nat - West is okay, but Nat Ė West isnít. Attached is the Text and a XL file with the result on running the code.

    Please advise.

    Lastly, I'm assuming that the code will read and copy all the text to my excel. I've run the tests and it is doing so. Just wanted to reconfirm though. Thanks a lot!

    Regards,
    Spi
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    What you have is non ascii characters.
    try this
    Please Login or Register  to view this content.

  19. #19
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Fantastic! I'll try this out first thing in the morning tomorrow.

    Just to be absolutely sure, though I do see the read all function in the code, the code will ensure that it reads every thing and captures it on the excel sheet. Please correct me if I'm wrong. Sorry to be repetitive but just want to be absoultely certain.

    Thank you! You are a rock star!!!

  20. #20
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    I'm still getting this as the result Nat ‚- West and You‚ instead of Nat - West and You'll.

    Please advise.

    Thanks!

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    Then it will be not possible to convert each non ascii/unicode characters individually.

  22. #22
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Got it, thanks.

    However, is it possible to get only these 2 converted? Iíve checked thoroughly and these are the only 2 posing an issue. The code is otherwise working beautifully.

    Lastly, I just wanted to reconfirm. The code will read all the text in the files and append it to the xl file. Right? Iím sure it is doing so, but just wanted to confirm with you.

    Apologies for all the trouble! Youíve been extremely helpful.

    Regards,
    Spi

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,881

    Re: Slight Modification Request VBA Code - Text File to Excel

    Try this and it is working (not a proper use of object though) here at least.
    You need to set the reference to "Microsoft ActiveX Data Objects x.x Library", where x.x is the largest.

    Please Login or Register  to view this content.

  24. #24
    Forum Contributor spiwere's Avatar
    Join Date
    01-11-2013
    Location
    IN
    MS-Off Ver
    Excel 2007
    Posts
    410

    Re: Slight Modification Request VBA Code - Text File to Excel

    Beautiful. Thanks a lot!

    You are a rock star

    Best regards,
    Spi

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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