×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Pasted ID number from .txt data, find and paste relevant .pdf filepath

Pasted ID number from .txt data, find and paste relevant .pdf filepath

Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
Thanks to Skip and Andy for helping me construct a VBA which grabs .txt data and pastes it into a single spreadsheet.

Invoice File

There are three companies and all of which have different filename for their .pdf invoice file.

When pasting the .txt data:
D01CC96625668 +0000004632409201821172698 Payment to Ross & Robs

Macro splits up the info in the following:
CC96625668 463 24/09/2018 21172698 Payment to Ross & Robs BAILIFFROSSANDROB01102018.txt Link

Company One
Based on the CC number, is it possible to find a .pdf file with that number within the same months' folder that the .txt file is held?
Folder structure for that .pdf file:
2018 > 10 October > Ross and Robs > 02 10 2018 LBCSBI > Successful

The second company's .pdf filename does not include the CC identifier. An example of the filename is SuccessfulDetail_ followed by five numbers.
Folder structure for that .pdf file:
2018 > 07 July > JBW > 18 07 2018 > Successful


The third company's .pdf filename also does not include the CC identifier. An example of the file is London Borough of Croydon_RT Croydon_ followed by five-six numbers.
Folder structure for that .pdf file:
2018 > 03 March > Whyte > 09 03 2018 02 > Successful

Sometimes there can be zero .pdf files in that folder.

Remit Files
Alongside this, I wish to input the directory of a remit file which just sits within:
2018 > 10 October > Ross and Robs > 02 10 2018 LBCSBI
2018 > 07 July > JWB > 18 07 2018
2018 > 03 March > Whyte > 09 03 2018 02

First company's .pdf filename example:
ClientRequestDetail_ followed by five numbers.
Second company's .pdf filename example:
London Borough of Croydon_RT Croydon_Payment_Advice_ followed by five-six numbers.
Third company's .pdf filename example:
Remit- followed by five numbers.


I hope this makes sense. A lot of the .pdf files will be repeating themselves (for companies two and three).

Thank you in advance.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
Forgot to mention, within the pasted data in the spreadsheet is a link to the location of the .txt file. The link to the location runs parallel to the unique case number (CC number).
Maybe it is possible to open the folder through this way and go into Successful folder?

Throwing ideas.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

Quote:

There are three companies and all of which have different filename for their .pdf invoice file.

To clarify, out of all the companies’ file names, there are three companies that “have different filename for their .pdf invoice file“, that is the filename structure is different than all the other company filename sturctures; where those other filename structures all share a common structure while these three do not.

And those three company names are?

And the company names are found in what data: the third level of the folder structure or something else?

And when you say, “followed by five numbers” do you mean five numeric characters?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
Apologies,

There are only three companies.

The company names are in the spreadsheet where Andy and Skip's macro lies.

CC96625668 463 24/09/2018 21172698 Payment to Ross & Robs BAILIFFROSSANDROB01102018.txt Link

Payment to Ross & Robs - Ross & Robs.
JWB also is shown in the download however, Whyte is not. Unfortunately, their .txt invoice files do not hold their name.

Each company has their folder per month and against each row of data (above) is their .txt filename and a "Link" to the location of the .txt.
Three company names are
Whyte
Ross and Robs
JWB

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

So is the objective at hand to associate the .pdf filename for a given .txt filename? I don’t see a question stated above.

And I’d assume that the .txt and .pdf would be in the same folder, yes?

And what about the “five numbers” clarification asked above?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
Yes, five numerical digits.

The Remit file is held in the same location as the .txt file.

The Invoice files however, are not. They are held in a folder labelled Successful.

For the most part, (nearly) every .txt file has a Remit file and invoice file(s).

I guess the situation is split in two.

If I were to run the macro for one .txt file - I would like for the Remit filename to be paste alongside every entry from that single .txt file's location.

For every entry from the .txt file - to paste the filename (as a hyperlink) of the invoice file. (Successful files under Successful folder). Whyte and JWB only have one invoice file.
Ross & Robs have individual invoice files per .txt file entry so, if the .txt file shows three cases. Ross & Robs will (sometimes) have three invoice .pdf files within the Successful folder. The filename of the .pdf invoice files matches the case numbers of the .txt files.
CC10635919-20170821-20848987-RETLET
CR96800010-20180110-21172706-RETLET
To the best of my knowledge, all .pdf files for Ross & Robs follow the same structure.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

You’ve written this thread assuming information that has been stated in a previous thread.

Please restate your question with all the related information necessary to address this issue.

Please include example raw data, like filename of each type: .txt, .pdf, other and, of course, how they are specifically related.

Explain what relevant issues have been resolved and what issues have yet to be resolved in this thread.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

A good start - IMHO - would be to present your folder structure, something like this (where [FF] stands for File Folder):

[FF] 2018
  [FF] 03 March
    [FF] Company One
    [FF] Company Two
      [FF] 12 03 2018 LBCSBI
        [FF] Emails
        [FF] Successful
        [FF] Unsuccessful
        BAILIFFROSSANDROB12032018.txt
    [FF] Company Three
 
That's where you can include where your PDF file(s) go and what are their actual names.
So this way I can see that a folder: 12 03 2018 LBCSBI contains 3 folders (Emails, Successful, Unsuccessful) and one text file (BAILIFFROSSANDROB12032018.txt)

Then you may state: From BAILIFFROSSANDROB12032018.txt file I can get the Company name that relates to the PDF file XYZ.PDF, etc.

Use either [PRE] or [CODE} TGML Tags to format your post.
Preview before Posting.

Don't assume any knowledge of your issues from any previous threads.

---- Andy

There is a great need for a sarcasm font.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
[FF] 2018
[FF] 03 March
[FF] JWB
[FF] 07 03 2018
[FF] Emails
BAILIFFJWBRT-69553.txt
London Borough of Croydon_RT Croydon_69553.pdf
London Borough of Croydon_RT Croydon_Payment_Advice_66355.pdf
[FF] Ross & Robs
[FF] 12 03 2018 LBCSBI
BAILIFFROSSANDROB12032018.txt
Remit-25684.pdf
[FF] Emails
[FF] Successful
CC10705752-20170721-20751604-RETLET.pdf
[FF] Unsuccessful
CC90790537-20170721-20751458-RETLET.pdf
[FF] 12 03 2018 LBCSBN
[FF] Whyte
[FF] 09 03 2018 01
BAILIFFWH62057P090318.txt
ClientInvoice58595_45-AgeDebtAnalysisReport.PDF
ClientInvoice58595_45-ClientBatchPerformanceReport1.PDF
ClientInvoice58595_45-ClientBatchPerformanceReport2.PDF
ClientInvoice58595_45-StatAnalysisReport.PDF
ClientInvoice58595_ClientPaymentDetailReport.PDF
[FF] Emails
[FF] Successful
SuccessfulDetail_58595.PDF
SuccessfulSummary_58595.PDF
WH62057R.txt
[FF] Unsuccessful
UnsuccessfulDetail_58595.PDF
UnsuccessfulSummary_58595.PDF

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
Remit files:
Remit-25684.pdf
SuccessfulSummary_58595.PDF
London Borough of Croydon_RT Croydon_Payment_Advice_66355.pdf

Invoice files:

SuccessfulDetail_58595.PDF
London Borough of Croydon_RT Croydon_69553.pdf
CC10705752-20170721-20751604-RETLET.pdf

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

Based on your previous thread707-1790225: Loop through all Sub Folders & copy data in .txt files into individual tabs, your info from txt files is on Sheet1 and looks something like this:

    A             B         C           D          E               F                         G
Case Number Client Paid	Invoice Date                           Filename                   Directory
CR90804777	7439	2/24/2018    e72d8d94  Payment to JBW  WBBAILIFFJBWRT-69553R.txt  LINK
CR96725800	5300	2/24/2018    a4c444b6  Payment to JBW  WBBAILIFFJBWRT-69553R.txt  LINK
CR96757432	17800	2/24/2018    1f292821  Payment to JBW  WBBAILIFFJBWRT-69553R.txt  LINK
 
(Columns D and E do not have headers?)

Where and how do you want to incorporate the information about your PDF files?
On the same Sheet1? On other worksheet? In what format?


---- Andy

There is a great need for a sarcasm font.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
Thank you Andy,
Columns D and E do have headers now - P-E # and Company.

Would like for the .pdf filename (as a link to the file) to be in Column H and the location of the .pdf in column I - all within Sheet1.

Based on some .pdf files, I would expect recurring links to appear.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

Quote:

Based on some .pdf files, I would expect recurring links to appear.

By “recurring links ” do you mean MULTIPLE links? I assume that you do.

Your choices are to either string them out on the same row, each link into an adjacent column (a really bad idea IMNSHO), or put the links into a separate sheet/table, where SELECTing the LINK cell 1) Activates that separate sheet and 2) Filters that table to display the related .pdf links.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

Based on the example you provided:

[FF] 2018
  [FF] 03 March
    [FF] JWB
      [FF] 07 03 2018
      [FF] Emails
      BAILIFFJWBRT-69553.txt
      London Borough of Croydon_RT Croydon_69553.pdf
      London Borough of Croydon_RT Croydon_Payment_Advice_66355.pdf
      ...
 
The code loping thru your folder structure and retrieve the files in alphabetical order, so not ALWAYS the txt file(s) and the info in it will be processed first. There is a possibility that the PDF(s) will be before you hit the txt file(s).
That can be accounted for in the code, thou.

But even if we first process txt file, let's take an example I have above, that gives us 3 rows of data for Payment to JBW, how do you relate the next 2 PDF files to the 3 (or possibly more) rows of data from txt file?


---- Andy

There is a great need for a sarcasm font.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)

Quote:

Would like for the .pdf filename (as a link to the file) to be in Column H and the location of the .pdf in column I

Correction:
Column H would hold the Invoice .pdf filename (as a link)
Column I would hold the Remit .pdf filename (as a link)


Would there be a way that, while the code looks at [FF] 07 03 2018 and copies info from BAILIFFJWBRT-69553.txt to open the .pdf London Borough of Croydon_RT Croydon_Payment_Advice_ within the same folder and put that in its own column under Invoice and in the same instance, any .pdf file with London Borough of Croydon_RT Croydon_#####.pdf to put that filename in its own column - Remit?

However, that would only work for that specific company.

Tried to relicate what the columns would look like here but I couldn't quite get the indent right so I have attached the file. Not sure it'll be of any use?

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

Well, example is nice....

You show that the txt file BAILIFFJBWRT-68519.txt would return 2 records:

Case Number	Amount	Date	        ~P-E	        Company
CC96814558	20300	1/20/2018	0064d2f9	Payment to JBW
CC96818490	12800	1/20/2018	60046d9c	Payment to JBW
 
And you have 2 pdf file that would go to additional columns:

Invoice	                                        Remit
London Borough of Croydon_RT Croydon_68519.pdf	London Borough of Croydon_RT Croydon_Payment_Advice_65321.pdf
London Borough of Croydon_RT Croydon_68519.pdf	London Borough of Croydon_RT Croydon_Payment_Advice_65321.pdf
 
But how would you tell to the program that the London Borough of Croydon_RT Croydon_68519.pdf goes to Invoice column, but the London Borough of Croydon_RT Croydon_Payment_Advice_65321.pdf goes to Remit column? What's the rule? And if that rule applies ALWAYS to all other Companies?


---- Andy

There is a great need for a sarcasm font.

RE: Pasted ID number from .txt data, find and paste relevant .pdf filepath

(OP)
Sorry for late reply.

I was thinking it would be company specific coding.

So for the example in your post for company JBW:

Code to find "Payment_Advice" within the filename of both London Borough of Croydon_RT Croydon_Payment_Advice_65321.pdf and London Borough of Croydon_RT Croydon_68519.pdf.

There is only one fiename which includes Payment_Advice therefore, it puts the filename in Remit column (as a link).

Perhaps a variable within the code to say after Croydon_RT Croydon_ there will be 5 or 6 digits that will be ever changing. That file is to go in Invoice.

Does that make sense? That's what I'm thinking at the moment but saying it out loud it doesn't sound all too convincing.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close