Consulting

Results 1 to 14 of 14

Thread: Importing Project

  1. #1
    VBAX Regular
    Joined
    Jan 2024
    Posts
    7
    Location

    Importing Project

    I need help with an importing project.

    I have three files: a PDF and 2 Excel files


    Test 1 – Is my file
    PDF2Excel – is where the macro is
    Tickets – is where the information I would like to be imported is

    If you open PDF2Excel and click import, and choose the PDF “tickets” the macro will open up a new sheet and will successfully import the entire PDF content into the new sheet
    I would like to see happening is as follows:


    1. I would like this code to be copied into Test 1 – button – “Import”
    2. When the user clicks on import I would like the code to already “know” which file path the PDF is and automatically import that file in that specific location without the window poping and prompting the user for file location
    3. When the user clicks on import, I would like the information from the PDF to remain on the “SAME” worksheet and be presented exactly the way you see in front of you in the “desired result” (I copy pasted these tables for Illustration purposes, but this is how it should look)

    That is all. I would highly appreciate help with this project.


    Thank you
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    1. copied how - by code?

    2. how should code "know" where PDF is located?

    3. your code does not already import in this arrangment?

    Unfortunately, I don't have Adobe Acrobat and so apparently cannot run your code. Error "Class not registered" on Set AC_PD = New Acrobat.AcroPDDoc
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Regular
    Joined
    Jan 2024
    Posts
    7
    Location
    1. Yes, by code
    2. We set a predefined location
    3. No, This is just a code a found in this forum that mimick the basic function of importing

    The code may have compatability issues (like the one you have described "Class not registered" on Set AC_PD = New Acrobat.AcroPDDoc" e and it doesn't really do exactley what I need. It arranges the pdf content in a new sheet and paste everything on the left side
    of the sheet. I need someone who can please help me design a code that does exactley what I descripbed at the begening.

    Thanks

  4. #4
    VBAX Regular
    Joined
    Jan 2024
    Posts
    7
    Location
    "June7" - Do you understand the main idea of what I'm tryign to accomplish?

  5. #5
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    1. Why would you need to put code into Test1 workbook as opposed to modifying and running in the original workbook?

    2. Specifying a location is a simple edit, assuming PDF file name will never change. For starters, eliminate code that calls the form to open and just call Imp_Into_XL Sub. Next either modify the Imp_Into_XL Sub to eliminate arguments and hard-code path within the Sub or call the Sub and pass defined path via argument. If you want all data on one sheet, then either pass False as value for Each_Sheet argument or eliminate code for multiple sheets.

    3. If the single sheet code does not output in your desired arrangement, that will take a lot more rewriting of code. I might look at that tomorrow. If I could get data out of PDF, I would probably import to an Access database.
    Last edited by June7; 02-04-2024 at 12:22 AM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    VBAX Regular
    Joined
    Jan 2024
    Posts
    7
    Location
    "Specifying a location is a simple edit. For starters..." = xvbn48785nnxkf2j4s - This is how your response translates in my brain. But, I know you making sense!
    Can you please help me out?

    Also, because I copy paste this macro from somebody, now everytime I open my sheet the other sheet opens as well. Am not sure how to get rid of that. Here's the answer but I don't understand how to do it:

    https://answers.microsoft.com/en-us/msoffice/forum/all/running-excel-macro-opens-the-original-workbook/3e4c5cc5-16ff-4bcc-b676-398c80ad16eb?ranMID=46107&ranEAID=wizKxmN8no4&ranSiteID=wizKxmN8no4-9RMkT3iaHfgpJvD3nGkwyA&epi=wizKxmN8no4-9RMkT3iaHfgpJvD3nGkwyA&irgwc=1&clickid=_kmbqkl6uwkkfdmgnc9xi6km6qe2x9pmwgxw eb0a000&OCID=AIDcmmaqfwnksg_AFF_1243925_3327_wizKxmN8no4-9RMkT3iaHfgpJvD3nGkwyA_190407&tduid=%28ir__kmbqkl6uwkkfdmgnc9xi6km6qe2x9pmw gxweb0a000%29%283327%29%281243925%29%28wizKxmN8no4-9RMkT3iaHfgpJvD3nGkwyA%29%28%29&OWTGT=AFF_1243925

    If you can please help in any way I would really appreciate it. Willing to pay for your time. At least, with the basics.

    Sincerely


  7. #7
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    What do you mean by "open my sheet" - you open a workbook, not a sheet. That link refers to opening a workbook file. I can open your file without any issue.

    Outputting data into structure you want won't be simple because each ticket has different length. Some are more than the 21 rows you show in your mockup, some are much shorter. I saw at least 1 ticket with text way down at bottom of mostly blank page.

    How do you intend to use Modify/Delete/Sort By/Filter features with this data?
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    VBAX Regular
    Joined
    Jan 2024
    Posts
    7
    Location
    Thank you for your reply,

    Well... AFTER I overcome the importing challenge, I would like to start using the sheet as a "ticket manager"
    I would like to be able to "search" tickets based on name, room number, ticket number etc.
    I would like to modify certain tickets by adding or omitting information.
    I would like to print 1,2 or ALL tickets if I wanted to
    I would like to send 1,2 or ALL tickets to a different station (which is basically sending them to another sheet)

    I would like it to be interactive, as if user only uses a touch screen to manipulate these tickets.

    Thus, I would like wach ticket (each table) to be able to be toggled like a huge button, and then user can choose to do things with it like mentioned above.

    Am I asking too much? Not realistic?

  9. #9
    VBAX Regular
    Joined
    Jan 2024
    Posts
    7
    Location
    Basically, I'm trying to create a ticket managment system that starts with importing the data from the PDF.
    Indeed, every ticket is different in length.

    Elad

  10. #10
    VBAX Regular
    Joined
    Jan 2024
    Posts
    7
    Location
    "open my sheet" - When I try to copy the code into the button "Import" in sheet named "Desired result" - it opens up a 2nd workbook where the original code is

  11. #11
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    "Ticket management system" - to me that sounds like a database.

    The more I look at your worksheet, the more troubling I find it. Code to import PDF data would not be so terrible but then there is all the cell formatting you show, especially the merging.

    If you want to do search and sort and filter, I don't see how this worksheet structure can facilitate that.

    My advice is to abandon Excel for this purpose and build an Access database.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    I don't know why code in Test1 would open existing workbook that has code. The code is designed to create a new workbook with a sheet named PDF2Text.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Banned VBAX Newbie
    Joined
    Feb 2024
    Posts
    3
    Location
    This is a big and very interesting project, I hope the project will be implemented successfully.
    Last edited by Aussiebear; 03-05-2024 at 07:55 PM. Reason: Removed spam link

  14. #14
    In the copied code, you can replace any references to file selection with the actual path to your PDF file, use double backslashes (\\) to escape backslashes within the string representing the file path.
    Dim filePath As StringfilePath = "C:\\Users\\YourUserName\\Documents\\Tickets.pdf"
    
    
    ' ... rest of the code using filePath


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •