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

[Nick V]

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.

 

[Nick V]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Nick V]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Nick V]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

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

[pre]
[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
[/pre]
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.

 

[Nick V]

[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​

 

[Nick V]

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

 

[Andrzejek]

Based on your previous thread707-1790225, your info from txt files is on Sheet1 and looks something like this:

[pre]
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
[/pre]
(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.

 

[Nick V]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Based on the example you provided:

[pre]
[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
...
[/pre]
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 [tt]Payment to JBW[/tt], 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.

 

[Nick V]

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?

 

[Andrzejek]

Well, example is nice....

You show that the txt file [tt]BAILIFFJBWRT-68519.txt[/tt] would return 2 records:

[pre]
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
[/pre]
And you have 2 pdf file that would go to additional columns:

[pre]
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
[/pre]
But how would you tell to the program that the [tt]London Borough of Croydon_RT Croydon_68519.pdf[/tt] goes to [tt]Invoice[/tt] column, but the [tt]London Borough of Croydon_RT Croydon_Payment_Advice_65321.pdf[/tt] goes to [tt]Remit[/tt] 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.

 

[Nick V]

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.