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

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.

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

There's no code in the workbook

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'

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

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

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.

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.

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

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

Please read my post carefully.