+ Reply to Thread
Results 1 to 49 of 49

Macro to copy only specific data from a txt file to an excel file

  1. #1
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Macro to copy only specific data from a txt file to an excel file

    Hi,

    I need a macro which would copy data starting from MEAU*****(which would be numbers) to my excel file. Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Macro to copy only specific data from a txt file to an excel file

    Pls provide some sample data...
    Click *, if my suggestion helps you. Have a good day!!

  3. #3
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Actually the text I have added is from a pdf file. If those datas can be copied from pdf then that would be awesome!! if not pdf...txt would be also be done

    Actually in txt file you can notice its not proper. I need the number which is after the word i.e : MAEU555993022 In this i need 555993022.

    I have attached only pdf file as the txt file is as same as this pdf file Thanksss...Any help will be greatly appreciated :D :D :D
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Macro to copy only specific data from a txt file to an excel file

    Could u convert this pdf file to excel?

  5. #5
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I have attached the excel file. Kindly help with this please! thanks a lot
    Attached Files Attached Files

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju Radhakrishnan,

    After a quick glance at the workbook, the only 9 digit numbers I see are the Maersk container numbers. Are these the only 9 digit numbers you need? Which file will the data be extracted from to the worksheet:text or PDF?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju Radhakrishnan,

    This macro will extract all 9 digit numbers in the text file (.txt extension) and copy them to "Sheet2" starting in cell "A1". You can change this assignment to what you need. You will also need to change the file path and name to match the location of your text file.
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Preferable would be pdf dear!! I thought pdf would be tough so I told txt!! And yes I need those container numbers !!! Thanks a ton for the reply!! really appreciated!

  9. #9
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I tried using your code. Its showing an error as Run time error '76' Path not found. I entered the path and your code as below
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 04-12-2012 at 04:17 PM. Reason: Added Code Tags

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju Radhakrishnan ,

    Did you check that the file name is correct and had has the ".txt" extension?

    Please make the following change to the macro.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    It still showing as path now found. Il show your the macro now. Kindly change if possible. The path is C:\Documents and Settings\rra172\Desktop\pdf.txt. When i clicked on debug its highlighting this line Open Filespec For Input Access Read As #TextFile
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 04-12-2012 at 05:21 PM. Reason: Added Code Tags

  12. #12
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    If possible instead of txt file can you change this to a pdf file macro if possible!

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju Radhakrishnan,

    If you have verified that both the file path and name are correct, I don't know what else to tell you. The macro works on my computer with no problem using the text file you posted. As for the reading the PDF file, I have never done that using VBA.

  14. #14
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I tried just now..It shows as Object required and highlighting "Open Filespec For Input Access Read As pdf.txt". the file name is pdf. In properties I checked and its a text document format.
    Please Login or Register  to view this content.
    In the above macro do I have to add anything in line : Filespec = IIf(Right(Filepath, 1) <> "\", Filepath & "\" & Filename, Filepath & Filename)
    TextFile = FreeFile
    Last edited by Leith Ross; 04-12-2012 at 05:38 PM. Reason: Added Code Tags

  15. #15
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Now I changed the file name as Raju. Now when i tried still its highliting the same line and its showing as Bad file name/number Path is C:\Documents and Settings\rra172\Desktop\Raju. Can you fill the macro and let me know what the problem is. thanks a lot

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju Radhakrishnan ,

    I have made a couple of small changes to your macro. Try this version and let me know if it works or not.
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I tried. Its saying path not found and highlighting " Open Filespec For Input Access Read As #TextFile "......................
    The file name is Raju and the path is C:\Documents and Settings\rra172\Desktop....... Thanks

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju Radhakrishnan ,

    Copy this macro and run it and tell me the result.

    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Its showing as file not found...........

  20. #20
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    That means the macro is not the problem. Either the file path or the file name is not correct. Check the spelling, case is ignored. My guess is the path. Run the macro below to verify your "My Documents" path is correct.
    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Absolutely correct. I'll try with different path. Will let you know thanks

  22. #22
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I tried copying the text file into my documents then too it says the same error and highlighting Open Filespec For Input Access Read As #TextFile

    Can you tell me what should be selected from Tools/References in order to open a text file??? I think the problem may be due to this....

  23. #23
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    The file is opened using standard VBA routines. There are no additional libraries needed. The number parsing is done using Regular Expression but the library needed for this is loaded automatically with the CreateObject statement.

    Check if Filespec is correct.
    1. In the VB Editor scroll down to the Open Filespec line.
    2. Press the F9 key. This will add a break point to the macro.
    3. Press the F8 key to run the macro.
    4. When the macro stops at the Open line, move the cursor to Filespec. You will see the path.
    5. To remove the breakpoint, move back to Open statement and press F9 again.

  24. #24
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    When i did as per your instructions its showing as Filspec = """" instead of path. Any suggestions?

  25. #25
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    Delete the Filespec line and insert the lines of code below in its place. Run ExtractNumbers and let me know the results.
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I added this way :
    Please Login or Register  to view this content.
    Its showing an error - Bad file name or number and its highliting this line - Do While Not EOF(TextFile)
    Last edited by Leith Ross; 04-12-2012 at 07:23 PM. Reason: Added Code Tags

  27. #27
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    You didn't copy the new lines of code to the right area. I have made the change. Copy this version and try it again.
    Please Login or Register  to view this content.

  28. #28
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    Sorry, I missed adding a statement - Open statement in the previous post. This version should work.
    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I copied but its showing the same error and highlighting Do While Not EOF(TextFile)

  30. #30
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    Did you copy he code from post 27 or 28? Post 28 should not produce that error.

    Post 28

  31. #31
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I copied that too..Its showing the same error. Dear what i think is I found the path by pressing F9 and its showing some other path in FILEPATH code.
    Open Filespec For Input Access Read As #TextFile - In this code : path is showing as C:\Documents and Settings\rra172\My Documents\pdf.txt\DB_aabddea....?? How come! Now i just converted the pdf file to a text file and the path is C:\Documents and Settings\rra172\Desktop\test. Try changing the FILESPEC path

  32. #32
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    The file path and file name should not change from what they were set to initially. Can you open this text file using Notepad?

  33. #33
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Yes I can.....Ok the path is set and finalized as C:\Documents and Settings\rra172\Desktop\test

  34. #34
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    So the path you posted is the same path Notepad used?

  35. #35
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Yes sir...

  36. #36
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Test is the file name of that notepad

  37. #37
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    Change the file path and file name in the macro to match and run it again.

  38. #38
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I have a file in the path C:\Documents and Settings\rra172\My Documents\pdf.txt...I again tried but showing the same error. If you could check as per your post 23 you could see the FILESPAC path has something ahead of the actual path...i.e. pdf.txt then something written ahead due to which this problem is occuring i think

  39. #39
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    Can you post a copy of your workbook?

  40. #40
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    sure....nothing written in the workpage...I have just started with the macro...
    Attached Files Attached Files

  41. #41
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    Can you post a copy of the text file? If it is too large to attach can you email it?

  42. #42
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    its too large! can you give me your email id please?

  43. #43
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    You can email to LeithRoss...gmail.com Replace the ... with @

  44. #44
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    I have sent you a mail. Kindly check & revert Thanks

  45. #45
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    Thanks for sending the files. When I change the file path and file name and run the macro, I get 186 results from the text file.

    I did notice you have pdf.txt folder name. Is this correct/

  46. #46
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Yes the name is correct! Send me that macro file through mail again and il go home and check it out! Further details il tel you in 3-4 hours Thanks again

  47. #47
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Hi again, I tested the file you sent but still its showing as path not found. Code is as below :

    Please Login or Register  to view this content.
    1)Kindly tell me at the end of the path should i write the file name or folder name?? And if file name should i write .txt??

    2) Folder path is = C:\Documents and Settings\admin\Desktop & file path is C:\Documents and Settings\admin\Desktop\Test.txt

    Please help me out. I'll make this easy for you now. I'll always save the txt file in the desktop and always the file name would be Test (Or anything you select except my name)!!!

    Please help me out for this!!!!! Thanks again
    Last edited by Leith Ross; 04-13-2012 at 01:08 AM. Reason: Added Code Tags

  48. #48
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to copy only specific data from a txt file to an excel file

    Hello Raju,

    I have made a variation of this macro which allows you to choose the folder and file to open. You don't need to code them into the macro. Here is the updated macro.
    Please Login or Register  to view this content.

  49. #49
    Forum Contributor
    Join Date
    04-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Macro to copy only specific data from a txt file to an excel file

    Hey sorry!! I tried your last code and its working :D good news yea! thanks a lot and sorry for troubling you since hours!!! thanks a ton!!!

+ 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