+ Reply to Thread
Results 1 to 102 of 102

After converting PDF to excel, all data paste in column A only

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    After converting PDF to excel, all data paste in column A only

    I have a code which converts PDF file to text, but in text file it is pasted in one row only. as shown in below picture:

    text file.PNG

    I have another code which converts this text file to excel file and it is also pasted in column A only, as shown in below picture:

    excel file.PNG

    Please help me so that it Paste properly into text and excel file as well as shown picture of PDF document from which i am copying data:

    pdf.PNG

    CODE TO COPY TO TEXT FILE:
    Please Login or Register  to view this content.
    CODE TO COPY FROM TEXT FILE TO EXCEL FILE:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Zahid0111; 03-26-2020 at 05:21 AM.

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hello Zahid0111,
    how about importing your data like this:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Last edited by Eastw00d; 03-26-2020 at 09:48 AM.
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Hi Eastw00d,
    Great, this works fine, but if i want to import this data to some specific sheet "RawDataBher"?

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hi Zahid,
    then change this line into:
    Please Login or Register  to view this content.
    ,
    assuming that this sheet is in the actual workbook, as well as this code.

    Cheers
    Erwin
    Last edited by Eastw00d; 03-26-2020 at 02:12 PM. Reason: typo

  5. #5
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    It gives the following error, can you please check
    Attachment 669177

    One thing more please, if i want to paste it on column Z.
    Last edited by Zahid0111; 03-26-2020 at 11:25 AM.

  6. #6
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ahh..i figured it out.There was an extra " ( " in the line which i removed.
    and to be pasted on Z column i changed it to Cells(i, j+25).
    It,s working like charm, thank you Eastw00d, much appreciated..

  7. #7
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Sorry, it seemed that my mouse slipped away
    Cheers
    Erwin

  8. #8
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    @Eastw00d ..hi i need your kind help in copying data from another text file by using the code you provided. i have attached the text file from which i want to copy data to excel sheet.
    PDF file from which i copied the data to text file snapshot is: Attachment 670074
    Only data which i want to copy to excel sheet snapshot is: Attachment 670075

    Code is:
    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Zahid0111; 04-01-2020 at 08:49 AM.

  9. #9
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Sorry we cannot see your attachment, please upload them via the manager

    Cheers
    Erwin

  10. #10
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Done attached...please check them: 1st picture is complete snapshot of pdf and second is the data which i only want to copy to excel as it is please
    Last edited by Zahid0111; 04-01-2020 at 08:56 AM.

  11. #11
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    I will look into this matter later, I have some tasks at home to do

    Cheers
    Erwin

  12. #12
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    No worries..at your ease please but kindly don,t forget to check it.

  13. #13
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    I see only 1 picture of "Process Code:" , no other pictures

    why not upload also a non-personalised workbook, that's much easier to work on.

    Cheers
    Erwin

  14. #14
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Hi ...i m uploading all the files..kindly check
    Attached Images Attached Images
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    NBP.txt is the file from which i want to extract data
    farmers history gradewise.xlsx is the file to which data will be copied
    pdf working.xlsm has the code
    here are the pictures:
    Inkedpdf snapshot_LI.jpg


    and

    I need only this data
    this data only.PNG
    Last edited by Zahid0111; 04-01-2020 at 01:37 PM.

  16. #16
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    ah, I saw that as 1 picture, my bad
    Cheers
    Erwin

  17. #17
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Hi Eastw00d , any luck please?

  18. #18
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    I am working on it, patience please
    Cheers
    Erwin

  19. #19
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Here you go:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Great Erwin..thank you so much..this works great..
    what if i want to skip the headings only? as highlighted in picture below
    Attachment 670271

    and does the code will work if i have more rows in my source data(PDF)?
    Attached Images Attached Images
    Last edited by Zahid0111; 04-02-2020 at 03:49 AM.

  21. #21
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    I cannot see your picture, upload them via manager please
    Erwin

  22. #22
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    done..uploaded in above post..

  23. #23
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    No problem,
    just delete this line:
    Please Login or Register  to view this content.
    Cheers
    Erwin

  24. #24
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    yes it works..great..
    Erwin if i have hundreds of rows(data), will it work as it is working now...i mean is it dynamic? as shown in below picture, i have two rows only, what if i have hundreds??
    Attached Images Attached Images
    Last edited by Zahid0111; 04-02-2020 at 04:04 AM.

  25. #25
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hi Zahid, that depends a little how the next group of lines will is set in the textfile. I have created this for only one line, but if you want more i need an example of a txt file with more lines.

    Cheers
    Erwin

  26. #26
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ok Erwin, that,s fine...currently i don,t have complete file as nowadays working from home due to pandemic covid 19 all over the world.It,s a lock down every where...therefore, once offices are resumed i will get the file and will contact you, your kind help will be highly appreciated...i will post it here, kindly do reply.. and thank you so much for your kind time and help--stay home-stay safe
    Last edited by Zahid0111; 04-02-2020 at 05:51 AM.

  27. #27
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    I am working on a solution, but the final touch depends on the format of the txt file. Also I want to know if there must be a number of blank lines between the two groups of lines and if you need totals under each two lines.
    Cheers
    Erwin

  28. #28
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Erwin, i have edited PDF file and copied the lines as shown below picture. then i converted PDF to text file, it is also attached, so it will be in almost this structure.
    total will be at the end, but if it is not included doesn't matter
    Attached Images Attached Images
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Some more data added to PDF and converted to text for your kind understanding of structure...
    Attached Images Attached Images
    Attached Files Attached Files

  30. #30
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hi Zahid, not to criticize your method, but are these statements only available by pdf which you have to convert to txt? The ones who are making those pdf's surely must have the data available in a normal format...

    Cheers
    Erwin

  31. #31
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ahh no...i m directly editing PDF..
    check picture, just copy pasting the values
    Attached Images Attached Images
    Last edited by Zahid0111; 04-02-2020 at 08:11 AM.

  32. #32
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    OK, then make sure that every export to the txt file has the word "NBPB" and "Total" in it, because that determines the number of lines.
    I can work with this....
    Cheers
    Erwin

  33. #33
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    OK Erwin...fine..
    But when lockdown is finished i will upload here original file too..for our discussion and if any help is required please...
    thank you

  34. #34
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hi so I could work with this, the result is:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files

  35. #35
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Wow Erwin..that,s fantastic...Great job done..i m really thankful for your hardwork and time...GOD BLESS YOU..

  36. #36
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Hi Erwin, Sorry to bother you one last time please...I have got original PDF file. My data will be in this form as attached in pdf. After converting pdf to text, i have attached the text file. As shown in snapshot i need data to be copied in this sorted form to excel, kindly need your help please.
    Attached Images Attached Images
    Attached Files Attached Files

  37. #37
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Zahid, please can you upload the txt file again, but then in this format:
    Please Login or Register  to view this content.
    Because the way this is now, it's not workable.
    Cheers
    Erwin

  38. #38
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Sure, i have replaced the code as you suggested, kindly find the attached text file..
    Attached Files Attached Files

  39. #39
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    This thread is marked as SOLVED. If it isn't, then the solved tag needs removing.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  40. #40
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    The problem with this is, also in the new format (I had hoped this would be a comma-separated format), that
    A) I have no possibillity to determine where starts the Father Name, and
    B) how to solve the fields where the value = empty, for example on the line of 080018000267, where the field of Cumulative Bales Rejected = empty. The same is for where the field is empty for the Cumulative Loan Issued.
    I can only solve this when there would be a value of 0.

    Again I think it is very strange to make report on basis of a pdf, where the data to put this in a pdf is available somewhere.

    Cheers
    Erwin

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

    Re: After converting PDF to excel, all data paste in column A only

    Zahid0111,

    Although I can not say it will be possible.
    If you can simply upload a TEXT file that is extracted from PDF and the FINAL result that you are expecting in EXCEL would be helpful.

  42. #42
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Have you checked the PDF file for this text, so that you can presume data flow?
    and what if we copy along with headings? as shown in below picture?

    Actually PDF data is coming from external software, in order to ensure integrity of data(not to be manipulated) data is saved id pdf format, thn it is sent to someone for analysis and other purposes. Now that is why we need to copy the data from pdf to excel...
    Attached Images Attached Images

  43. #43
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    Please provide the requested .txt and Excel files. Thanks.

  44. #44
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    @jindon attached are the files:
    PDF file file from which i am extracting data.
    Text file, first data extracted from pdf to TEXT file.
    Picture: data i want to be copied to excel.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Zahid0111; 04-10-2020 at 04:15 AM.

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

    Re: After converting PDF to excel, all data paste in column A only

    It is simply impossible.

    This code will separate the record by row, but nothing other is possible,
    Please Login or Register  to view this content.

  46. #46
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Thank you @Jindon but it pastes all data in one column...as shown in attachment..Can you please check
    @Erwin Can you please work with this now?
    Attached Images Attached Images

  47. #47
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    No, for the reasons I said in post #40.

    Sorry
    Erwin

  48. #48
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    Erwin - what are you responding to? When posts are out of sequence, please make this clear with a short quotation.

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

    Re: After converting PDF to excel, all data paste in column A only

    Quote Originally Posted by Zahid0111 View Post
    Thank you @Jindon but it pastes all data in one column...as shown in attachment..Can you please check
    That's what I said.

    Impossible to separate the fields due to no particular column separator.

  50. #50
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Quote Originally Posted by Eastw00d View Post
    No, for the reasons I said in post #40.
    I said:
    The problem with this is, also in the new format (I had hoped this would be a comma-separated format), that
    A) I have no possibillity to determine where starts the Father Name, and
    B) how to solve the fields where the value = empty, for example on the line of 080018000267, where the field of Cumulative Bales Rejected = empty. The same is for where the field is empty for the Cumulative Loan Issued.
    I can only solve this when there would be a value of 0.
    So I agree with Jindon.

    cheers
    Erwin

  51. #51
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    @Erwin...i agree with you... can,t we just insert a separator in our program, as we already know the flow of data from PDF file and according to our requirement. Is it possible please?

  52. #52
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    @Erwin & @Jindon I have came up with an idea, if text file is,nt working for us, can we do pasting from pdf to word file and then from word it is pasted in excel...what do you say? any suggestion please..

    @AliGW what is your take on my suggestion? can you dig out something for me please?

    I HAVE CONVERTED PDF TO WORD THROUGH VBA, FILE IS ATTACHED, CAN YOU PLEASE WORK WITH THIS NOW??
    Attached Files Attached Files
    Last edited by Zahid0111; 04-10-2020 at 08:41 AM.

  53. #53
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    @AliGW what is your take on my suggestion? can you dig out something for me please?
    Dig what out?

  54. #54
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ah i mean to say..that can you please came up with some solution? do you have any idea? can you please help in this?

  55. #55
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    No, sorry - I don't do VBA.

  56. #56
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    @ Zahid, I have some ideas, I am working on this. Please be patient, as it is not easy what you ask.

    Cheers
    Erwin

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

    Re: After converting PDF to excel, all data paste in column A only

    Try
    Please Login or Register  to view this content.

  58. #58
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Thank you Jindon...how can i skip the first and last row as shown in picture...
    Also if i want to paste the data in some specific sheet named "Farmer History" and to last blank row?
    Attached Images Attached Images
    Last edited by Zahid0111; 04-11-2020 at 03:04 AM.

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

    Re: After converting PDF to excel, all data paste in column A only

    Change to
    Please Login or Register  to view this content.

  60. #60
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    After running the code it takes too long and remains busy...after some time it continuously starts giving the attached message...
    and after pressing Cancel it lands me on the Yellow marked code..
    Attached Images Attached Images
    Last edited by Zahid0111; 04-11-2020 at 04:38 AM.

  61. #61
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    Is Word already open in the background?

  62. #62
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Quote Originally Posted by AliGW View Post
    Is Word already open in the background?
    NOPS...I just have Excel open...

  63. #63
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    Do you have the Microsoft Word Object Library 16.0 ticked under Tools | References in the Visual Basic window?

  64. #64
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Quote Originally Posted by AliGW View Post
    Do you have the Microsoft Word Object Library 16.0 ticked under Tools | References in the Visual Basic window?
    Yes ticked
    Attached Images Attached Images

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

    Re: After converting PDF to excel, all data paste in column A only

    No need to set the extra reference to the dll, its all done by CreateObject.
    Try add 2 lines in bold
    Please Login or Register  to view this content.
    Last edited by AliGW; 04-11-2020 at 05:12 AM. Reason: Red colour added for easier visibility.

  66. #66
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Great now it,s fine...Thank you Jindon...really appreciate your hardwork and time

  67. #67
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  68. #68
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Quote Originally Posted by AliGW View Post
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Yeah almost it,s done...Actually this is alternate method to my original question. Basically i am waiting for Erwin, he has got some idea on which he is working as soon as i get reply from him, i will do the needful.
    Thank you

  69. #69
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    patience please, it is holidays now, for us HOLY DAYS, we wish you all blessings from the Lord!
    Cheers
    Erwin

  70. #70
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Thank you Erwin...Please Enjoy your Holidays..
    I warmly reciprocate the same to you..Stay Blessed..
    Best wishes for Easter and I hope that everything will turn out wonderful and great for you and yours
    Last edited by AliGW; 04-11-2020 at 09:14 AM. Reason: Please don't quote unnecessarily!

  71. #71
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hi Zahid,
    Below code can be used for this file only, what you have uploaded earlier, (FHB.TXT) as there are many exceptions and missing data.
    The output of the pdf in textformat is very bad. It is easier and less time-consuming to copy the columns manually to Excel, than what I have done: making a script for this.
    The most time I have spent because with checking the totals: I found that some of the totals in the .pdf, and therefore in the .txt, are wrong.....

    But as promised:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files

  72. #72
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Dear Erwin, Thank you so much for your kind time..and i m sorry for putting you in trouble...
    I tried, it is giving me below error, can you please check...
    Attached Images Attached Images

  73. #73
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Quote Originally Posted by Zahid0111 View Post
    Dear Erwin, Thank you so much for your kind time..and i m sorry for putting you in trouble...
    I tried, it is giving me below error, can you please check...
    Ah tried it with other FHB file(First version)...it,s working for it...

  74. #74
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Erwin, One question please, will it work if number of pages or items are increased or decreased?

  75. #75
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hi Zahid, as I do not have the same problem, you must give me a bit more information:
    what value is "i" and "pos" and the second thing is, did you use the file as attached:
    Cheers
    Erwin
    Attached Files Attached Files

  76. #76
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Quote Originally Posted by Eastw00d View Post
    Hi Zahid, as I do not have the same problem, you must give me a bit more information:
    what value is "i" and "pos" and the second thing is, did you use the file as attached:
    Cheers
    Erwin
    Please check my post # 73 and 74...

  77. #77
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    The problem Zahid, is that the output of the pdf is not regular, there are gaps in the data, missing values in columns/rows.
    Every row that is not complete, i.e. 13 values, has some issues.
    That's why I ask you to get to the source of the pdf's. The basic data.
    Only then, when all the rows are completely filled, I, we, can make a macro that is suitable for all these "pages".
    I know Adobe has some possibilities to make a genuine .CSV file, where those blanks/missing values too are separated, with a comma.
    This would be a solution.
    Unfortunately I do not know how to do this in VBA.
    Cheers
    Erwin

  78. #78
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Erwin, i think you are talking a about the column Cumulative Loan Issued..Which has several values missing as shown in Picture..
    What if we can skip this row during copying? or
    What if i ask my team to skip this column during exporting it from software to PDF?
    Attached Images Attached Images

  79. #79
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Erwin, Tomorrow i will send you another PDF file along with text file(converted from PDF). In which, the attached two columns will not be exported as they have some values missing, basically they are not missing, they are actually ZERO.
    Thanks
    Attached Images Attached Images

  80. #80
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Yes, but the bigger problems are the columns with "Cumulative Bales rejected", "crop area" and in 1 case the "cumulative yield" too: 080019001046.
    Crop area can be calculated, or cumulative yield can be calculated.
    The problem is that there is no way to determine what column is blank. For example at line 080019000879 , the 1 for "crop area", in the output in the textfile it will be read as "cumulative bales rejected"
    If your team can manage to fill these blanks with a zero, so that all the rows are equally filled, then it would be a lot easier.
    I give you the file when I assume all data is filled, for a programmer this file is garbage.
    Cheers
    Erwin
    Attached Files Attached Files

  81. #81
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Ah, my friend, I wish you would have done this in the first place .
    What would be helpful too, just for extra checking, is the value of the crop area to be rounded everywhere at 1 decimal, i.e 2 would become 2.0

    Cheers
    Erwin

  82. #82
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Hahaha...i really didnt know..these gaps are troubling you...sorry sorry sorry...
    2nd question answer...yes no problem if it is 2 or 2.0...

  83. #83
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    in that case (all blanks filled) this should do the trick:
    Please Login or Register  to view this content.
    Cheers
    Erwin

  84. #84
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ahan great...But tomorrow i am gonna send you more simplified PDF file along with text file, kindly do check it...i have decided to skip those columns which have spaces/blanks..so that it will be more easy for you to code universally and dynamically.

  85. #85
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Hi Erwin, As discussed yesterday, Kindly find the attached documents. Hope this time it would help. I have skipped a lot of stuff, attached is converted text file too
    Attached Files Attached Files

  86. #86
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    No, sorry, but I cannot work like this, you keep changing the format, I don't mean the numbers of columns that is not the problem, but the number of rows.
    In the first file every row started with a number, now all the rows are written behind each other; it is like one bulk of text.
    Only the last 10 rows are in the same format.
    In this way I have to start over all again.

    Sorry
    Erwin

  87. #87
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ah Dear Erwin, wait please...
    Actually as i told yesterday that i will eliminate some columns which have some blanks, which are causing problem to you in coding, that,s why i skipped some columns. Let,s discuss the old PDF file..and get some solution to it please..if you don,t mind...

  88. #88
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    If you maintain the number of columns just like it was, but only add zero's in the places where now are blanks, most of the problems are solved.

    Cheers
    Erwin

  89. #89
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    yeah sure, i will maintain number of columns..no worries..
    But for ZERO,s and blank spaces, i have asked the concerned to put zero in blank spaces if possible...they are checking it..let,s hope they find some solution to it..
    Meanwhile, if they don,t..how can we tackle this issue,please help me...just for my understanding...isn,t it possible, that we put ZERO through our code? please don,t laugh on my questions..

  90. #90
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    The problem is, I have told you this before, there is no way, Excel can determine where to put the 0. We can do this visually, but Excel is a stupid program. It has to be told for every time that this is not standard. In the original file there were 58 lines that needed to be adjusted of the 519. That is almost 10 % of the total. That is too much.

    for example: at line 080018000095 we can figure that is must be in the second last column, but in the line 080018000267 is in the 8th column (and all the subsequent columns have to shift 1 to the left)
    and than there is line 080018000320... two places to fill, which ones? I know it, but the problem is that it has to be told to Excel, and therefore this is an exception just like the other ones. I build this in my programming, with the very extreme exception to be filled separately. It would cost me, or you, about a half an hour to do this manually/visually, and much more to do this with a program. (See post #80, the outputfile)

    Cheers
    Erwin

  91. #91
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ahan GOT YOU...
    Then we have one choice left, to check if it is possible to put zero in blank..okay..i m trying my level best to find a way out for this...lets see...
    Well thank you Erwin for your kind support..

  92. #92
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Hi Erwin...I have found a solution to the problem and made some modification to the Code which converts PDF to Text. Now It will be directly converted to excel file as attached. Now i need your kind help here to copy the data from this converted excel file to main file as we did with the text file..Now we are very close to the solution of this problem.
    Attached are Converted Excel file(FHB2), PDF working file2(this has the code).
    Please help me, i think finally we are almost there...Now we need to copy only data from FHB2 file to farmers history gradewise.xlsx as we were doing with text file, Kindly let,s have it dynamic so if number of pages are increased/decreased it should do the job
    Attached Files Attached Files

  93. #93
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    I will do this in the evening
    Cheers
    Erwin

  94. #94
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Hi Zahid,
    as promised, the final solution:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files

  95. #95
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Thank you Erwin...This cleans and copy the data like Charm...GOD BLESS YOU..AMEN
    One thing please, I want to run this code from attached file, it should fetch the data from FHB2 excel file and paste in farmer history gradewise excel file. The same we did with the text file as shown in attached image.

    Erwin, is it dynamic please? i mean if data is reduced/increased in source file, will it work?
    Attached Images Attached Images
    Attached Files Attached Files

  96. #96
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    Yes the macro is dynamic, no matter how many columns or rows.
    But now you ask to do this from the text file. That is a whole different chapter. I worked this out for the attached xlsx file and that is the last I will do.
    With this minor change is opens the Zahid_FHB2.xlsx file from the "sample" directory and saves the output as "farmers history gradewise.xlsx".
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files

  97. #97
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Ah wait..you misunderstood me..i don,t want to do it for text file...i just gave you reference of text file that i want the same with this as we did with text file..no need to do this for text please

  98. #98
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    No worries, I don't do that.
    Cheers
    Erwin

  99. #99
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    I tried the above code...Thank you so much Erwin my friend..lov u so much...and GOD Bless You...I m very thankful to you for your kind help and support, i really appreciate your hard work..
    The issue is now resolved...

  100. #100
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: After converting PDF to excel, all data paste in column A only

    You're welcome and thank you for the rep. points.
    I liked the challenges in this thread, but I am also glad that this chapter is closed now.

    Cheers
    Erwin

  101. #101
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: After converting PDF to excel, all data paste in column A only

    Zahid - it might be worth bearing in mind that we have the Commercial Services section. If you are going to set a brief and expect someone else to realise it in full for you, then you ought to consider posting there so that you can offer some recompense for the significant investment of time involved. This thread has not really been a help process, it's been a task realisation from start to finish. I hope you understand what I am saying.

  102. #102
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: After converting PDF to excel, all data paste in column A only

    Yeah Sure..
    Last edited by AliGW; 04-16-2020 at 04:18 AM. Reason: Please don't quote unnecessarily!

+ 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. Replies: 1
    Last Post: 02-02-2015, 11:05 AM
  2. [SOLVED] Converting data general column to UK Date format in Excel 2007 - vba
    By Sailaja A in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-18-2013, 06:59 AM
  3. Replies: 1
    Last Post: 02-18-2013, 11:36 AM
  4. copy data from excel and paste the data to word document in speciofic column.
    By vsabap in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2012, 07:12 AM
  5. Replies: 3
    Last Post: 08-28-2012, 10:09 AM
  6. Replies: 5
    Last Post: 08-20-2011, 04:14 PM
  7. Excel keeps converting numbers to dates on copy/paste
    By zazu88hike in forum Excel General
    Replies: 3
    Last Post: 01-27-2009, 02:36 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