Importing Project

[elad770]

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

[June7]

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

[elad770]

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

[elad770]

"June7" - Do you understand the main idea of what I'm tryign to accomplish?

[June7]

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.

[elad770]

"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=_kmbqkl6uwkkfdmgnc9xi6km6qe2x9pmwgxweb0a000&OCID=AIDcmmaqfwnksg_AFF_1243925_3327_wizKxmN8no4-9RMkT3iaHfgpJvD3nGkwyA_190407&tduid=%28ir__kmbqkl6uwkkfdmgnc9xi6km6qe2x9pmwgxweb0a000%29%283327%29%281243 925%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

[June7]

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?

[elad770]

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?

[elad770]

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

[elad770]

"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

[June7]

"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.

[June7]

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.

[maruusa0106]

This is a big and very interesting project, I hope the project will be implemented successfully.

[tonyadams]

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.
Buckshot Roulette (https://buckshotroulette.com)


Dim filePath As StringfilePath = "C:\\Users\\YourUserName\\Documents\\Tickets.pdf"


' ... rest of the code using filePath

[Charlize]

I don't have Acrobat so I used the free Foxit Reader to create a txt file of all those tickets.
- Each ticket is luckily divided (when exported to txt) with ------ Page x ------- , so each page is a ticket
- Each ticket is a worksheet in the workbook
- Problem is the spaces in between food and quantity
- Problem are special characters to get rid of importing
- Problem are inconsistencies
- Another problem ...

I came up with a somewhat clean import for each ticket for your bulk file (12 tickets will be imported for now)
There is a limit on the sheets in a workbook (ticketnumber = 434)


Sub GET_TXT_INFO()'strFilename = name of the file
'strTextLine is value of a readline from textfile
Dim strFilename As String, strTextLine As String
'strTextPartial is used to loop strTextLine when split with 3 spaces
Dim strTextPartial As Long
'fileno to handle your file, we do not know the no
Dim iFile As Long
Dim mywb As Workbook
Dim myws As Worksheet
'current row
Dim myrow As Long
'the workbook
Set mywb = ActiveWorkbook
'the sheet where you start this macro
'sheets will be added after last added sheet
Set myws = mywb.ActiveSheet


'the ticketnumber
Dim myticketno As Long
'number of tickets to be processed : 12 tickets
Dim mycounter As Long


'get free fileno
iFile = FreeFile
'choose the file to open ie. txt file
strFilename = Application.GetOpenFilename
Open strFilename For Input As #iFile
'loop until end of file
Do Until EOF(iFile)
'read line
Line Input #iFile, strTextLine
'search for text in line to determine if new record
'if the length is > 2 And not 30 spaces
If Len(strTextLine) > 2 And _
InStr(1, strTextLine, Space(30), vbTextCompare) <> 1 Then
'if readline contains page = new ticketnumber
If InStr(1, strTextLine, "Page ", vbTextCompare) > 1 Then
'Debug.Print "--- new ticket"
mycounter = mycounter + 1
myticketno = myticketno + 1
'add a worsheet for new ticket as last sheet
Set myws = mywb.Sheets.Add(after:=mywb.Sheets(Sheets.Count))
'rename the worksheettab to ticket + no
myws.Name = "Ticket " & myticketno
'first row to use for ticket = 1
myrow = 1
'column = 1
mycolumn = 1
'put -- Ticket & no in cell
myws.Cells(myrow, mycolumn).Value = "-- Ticket " & myticketno
'row = +1
myrow = myrow + 1
Else
'Debug.Print strTextLine
'no new ticket, just write readvalue in new row
'added a subcheck for lots of spaces between food and quantity

For strTextPartial = LBound(Split(strTextLine, " ")) To _
UBound(Split(strTextLine, " "))
'if length of partial string that is trimmed from spaces > 1 then use it
If Len(WorksheetFunction.Trim(Split(strTextLine, " ")(strTextPartial))) > 1 Then
myws.Cells(myrow, mycolumn).Value = WorksheetFunction.Trim(Split(strTextLine, " ")(strTextPartial))
'name of food next to quantity with blank column in between
mycolumn = mycolumn + 2
End If
'End If
Next strTextPartial
'myws.Cells(myrow, mycolumn).Value = strTextLine
'setting column back to one for next food or next ticket
mycolumn = 1
'myrow one higher for next food, will be set to 1 if new ticket occurs
myrow = myrow + 1
End If
End If
'Stop '--- used for printing to debug window
'Only process 13 tickets for now
If mycounter = 13 Then Exit Do
Loop
'close the handler of your file
Close #iFile
End Sub



Charlize