+ Reply to Thread
Results 1 to 26 of 26

Converting PDF to Excel issue (automation)

  1. #1
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Converting PDF to Excel issue (automation)

    First a big thank you to help provided to me a ways back on using visual basic to tackle data spillover issues while trying to convert a .pdf file into an Excel file. I am pushing my limits of using visual basic and have been trying to figure out a way to run two scripts together (one after the other).

    My goal is to simplify the number of times a user has to copy/paste code or touch the data. The .pdf comes out as one worksheet for each .pdf page. I am using a combine script to make one big worksheet.

    Data spills down over several rows in a column during the conversion and I needed each property item on one row each. Thankfully someone way smarter than me provided that script. Though for some weird reason the top 10-20 rows does not recognize that script.

    I am trying to figure out a way that someone can save the .pdf as an .xls and by clicking one button (minimizing issues) a script will combine everything onto one sheet and then combine any spill over so that each piece of property information runs across it's own row. The script would then auto sort by column A and maybe even auto delete all rows that do not contain numbers in column A, leaving only property on the sheet. If it then deleted any column A duplicates it would make life much easier for a lot of people!

    I have attached an edited version of a how to guide to describe the process- it includes the scripts mentioned above and shows to be a bit cumbersome.


    Any guidance or help as always appreciated...

    D
    Attached Files Attached Files
    Last edited by Biased Historian; 12-01-2010 at 09:12 AM.

  2. #2
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    The one thing that I am struggling with is I have located a macro for most of the things that I want to do yet I am unsure how to make it one big function (formula). Will a series of macro's work in in VB or vice versa?

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Converting PDF to Excel issue (automation)

    I don't think you will be able to convert a PDF file to Excel without other software and then the results aren't usually very good.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Converting PDF to Excel issue (automation)

    If you post an example of a PDF converted to Excel, we will be better able to help.



  5. #5
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Here is a sample of a fully converted file after all steps are taken- looking to minimize or automate the steps to get to here.


    Thanks-
    Attached Files Attached Files

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Converting PDF to Excel issue (automation)

    There's no code in the workbook

  7. #7
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Here is a made up version of the data showing one worksheet equaling one .pdf page.

    I first combine the sheets into one. Then run a script to put each piece of property info on the same row. Then sort off of the barcode numbers (column A). Then remove duplicates.

    Trying to put all of that into one action to minimize errors.


    Thanks again,

    D
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    The three scripts that I use are in this word doc-

    Still need a script for sorting via column A, deleting all rows that do not have numbers in column A, and removing duplicates.


    Thanks,

    D
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Just to clarify I can only have one code at a time correct? So to show you 3 codes I have to put them in a document format to cut and paste?

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Converting PDF to Excel issue (automation)

    No.
    You'd better integrate those macros into 1 and store that in personal.xls or in an AddIN (xla) (named e.g. 'finishing_touch')

    So after opening the Excel-file with the imported PFD-data, you only have to start this macro : Alt-F8 'finishing_touch'

  11. #11
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    It is the integrate all of the macros into one part that I am struggling with-


    Thanks,

    D

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Converting PDF to Excel issue (automation)

    Does the code work? For instance what is sq? It should be a Range but you don't set it

  13. #13
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    I have been pasting one code in at a time, hitting the green play arrow to edit the workbook, then deleting the script and pasting in a different script, hitting the green arrow, ect...

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Converting PDF to Excel issue (automation)

    Put all 3 codes into a standard module. Change the name so ther is no conflict with having two called Combine

    Then add another Procedure

    Please Login or Register  to view this content.
    That will call each procedure in that order. Just experiment

  15. #15
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Thank you- I am beginning to grasp it a bit...working on adding in all of the macros now and will go with your suggestion. One problem is that 2nd combine- it is only usable if you selct the multiple cells with data by hand and then execute the macro. So it is blocking my attempt at automation the process.

    As best that I can tell the below script is not converting the data from the first two worksheets/pages- thus the need to do it manually with the 2nd combine script. I am hoping someone might see a coding issue that is making it convert incompletely?

    Please Login or Register  to view this content.
    Last edited by royUK; 11-28-2010 at 02:25 PM.

  16. #16
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    After a few days of trial and error I managed to build a macro containing multiple tasks. Thus far I had to settle for making a duplicate deletion seperate because I am having an integer conflict. I am not sure how to combine that macro to my big one and have tried substituting the "x" with an "i" and a "w"...though clearly that only showed that I am not sure what I am doing!

    Thank you for the suggestions thus far. To make it easy for others to use I am storing the macros in a seperate work book and instructing the users to keep it open and minimized with instructions on which hot keys to use.

    Thank you again for suggestions and patience. Does anyone see a way to combine these two macros and if so how, so that I may be able to understand why it is not working?

    Here are the macros;

    #1

    Please Login or Register  to view this content.
    __________________________________________________________________________

    #2

    Please Login or Register  to view this content.
    Last edited by Biased Historian; 11-28-2010 at 05:49 PM.

  17. #17
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Converting PDF to Excel issue (automation)

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    I've added them to one post for you

  18. #18
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Sorry about that- I think that this is how you want it? #16

  19. #19
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    I have it converted from .pdf to Excel. I am trying to combine the two codes in #16. If that is possible then I can close this thread as solved.

    Thanks,


    D

  20. #20
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Could someone please assist me...is it possible to combine the two codes in #16? If so could someone please explain what was wrong and how to correct it so that I can try and avoid an issue in the future?

  21. #21
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Converting PDF to Excel issue (automation)

    replace:
    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.
    NB. replace 'data' with the sheet's name containing the fieldnames

  22. #22
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Getting runtime error 9, subscript out of range?

  23. #23
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    I replaced data with sheet1 per the instructions. I am getting an application error and the duplicates are not being deleted. What is NB?

  24. #24
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Converting PDF to Excel issue (automation)

    Please read my post carefully.

  25. #25
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    Here is the replaced code with Sheet1 taking the place of data. Am I missing something or was I too literal in following the instructions?

    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Converting PDF to Excel issue (automation)

    I am happy enough with the two step macro. I am trying to get a better understanding about integers, but clearly I am not grasping the above suggestion.

    Thank you to all for their guidance.


    D

+ 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