Hi,
I need a macro which would copy data starting from MEAU*****(which would be numbers) to my excel file. Thanks
Hi,
I need a macro which would copy data starting from MEAU*****(which would be numbers) to my excel file. Thanks
Pls provide some sample data...
Click *, if my suggestion helps you. Have a good day!!
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
Could u convert this pdf file to excel?
I have attached the excel file. Kindly help with this please! thanks a lot
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!)
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.
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!
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
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.
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
If possible instead of txt file can you change this to a pdf file macro if possible!
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.
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.
In the above macro do I have to add anything in line : Filespec = IIf(Right(Filepath, 1) <> "\", Filepath & "\" & Filename, Filepath & Filename)Please Login or Register to view this content.
TextFile = FreeFile
Last edited by Leith Ross; 04-12-2012 at 05:38 PM. Reason: Added Code Tags
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
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.
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
Hello Raju Radhakrishnan ,
Copy this macro and run it and tell me the result.
Please Login or Register to view this content.
Its showing as file not found...........
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.
Absolutely correct. I'll try with different path. Will let you know thanks
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....
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.
- In the VB Editor scroll down to the Open Filespec line.
- Press the F9 key. This will add a break point to the macro.
- Press the F8 key to run the macro.
- When the macro stops at the Open line, move the cursor to Filespec. You will see the path.
- To remove the breakpoint, move back to Open statement and press F9 again.
When i did as per your instructions its showing as Filspec = """" instead of path. Any suggestions?
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.
I added this way :
Its showing an error - Bad file name or number and its highliting this line - Do While Not EOF(TextFile)Please Login or Register to view this content.
Last edited by Leith Ross; 04-12-2012 at 07:23 PM. Reason: Added Code Tags
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.
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.
I copied but its showing the same error and highlighting Do While Not EOF(TextFile)
Hello Raju,
Did you copy he code from post 27 or 28? Post 28 should not produce that error.
Post 28
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
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?
Yes I can.....Ok the path is set and finalized as C:\Documents and Settings\rra172\Desktop\test
Hello Raju,
So the path you posted is the same path Notepad used?
Yes sir...
Test is the file name of that notepad
Hello Raju,
Change the file path and file name in the macro to match and run it again.
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
Hello Raju,
Can you post a copy of your workbook?
sure....nothing written in the workpage...I have just started with the macro...
Hello Raju,
Can you post a copy of the text file? If it is too large to attach can you email it?
its too large! can you give me your email id please?
Hello Raju,
You can email to LeithRoss...gmail.com Replace the ... with @
I have sent you a mail. Kindly check & revert Thanks
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/
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
Hi again, I tested the file you sent but still its showing as path not found. Code is as below :
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??Please Login or Register to view this content.
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
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.
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!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks