Hi all,
I have managed to make a loop which looks through all tabs in all files (both tabs and files are unspecified). The code copies a set of data and pastes into a Data Dump file.
There is a problem when there is only ONE entry in any tab due to the AutoFill section of the macro. Would...
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...
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...
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.
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
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...
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...
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.
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...
Brilliant!
Works a charm. No error. Ignores files starting with WH and ending with R.
Absolutely wonderful.
Thank you for your time, effort and patience.
I will now work on trying to pair the Case Numbers with their relevant .pdf files.
Thank you both.
Thank you very much Andy,
I will try those codes out.
At the moment, the .txt files that I do not want to copy contents from start with "WH".
Invoice file:
BAILIFFWH62057P090318.txt
Other .txt file:
WH62057R.txt
WH62057 is in both filenames. The invoice file finishes that 7 digit sequence...
It works perfectly in that controlled folder structure I sent you but, the same code still errors in the main folder structure.
I ran the same code for months that do not have company Whyte (this company holds the additional .txt file - sensitive information) and it works perfectly fine...
There were a lot of files I had to delete due to sensitive info but, since this only looks at .txt files that should be okay (I also deleted the other .txt file which I previously mentioned - that holds names, I suppose this is still an issue but, even if the macro grabs that .txt file I can...
Thanks Andy,
Yes they do make sense - essentially =MID.
I have changed the code to the one you provided and still getting error with the same file - or at least in the same position. Is this due to duplication of filename? As this can occur a few times within a month.
Thank you.
Hi Andy,
Sorry - there is another .txt file which is situated within the same folder as .txt filename bailiffWH62058P090318.txt. For this specific company, there seems to always be a second .txt file however, I don't need that file yet, because the code grabs all .txt files, I can easily...
Hi Andy,
Thanks for the response. I have attached a zip file which holds three .txt invoice files as well as the .txt file which it errors upon. Would it having the same filename be the cause of the issue?
I ended the filename with _error for...
Hi Andy,
You are right. Based upon the .txt file I provided it works but where I previously mentioned there being a variable in terms of the different company names.
Its the JWB line when collating all the .txt files which holds some of the reference (8fb782d1-4ecf-888e-d068-7ab31afd51f0)...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.