Loop through all Sub Folders & copy data in .txt files into individual tabs 1

[Nick V]

Hi all,

Quickly : There are folders for each month of the calendar year and in those month's folders are sub-folders which belong to various companies. In the company's folder are more folders which holds .txt files that I need the the contents to be extracted from.

Essentially, in folder "03 March" there is a folder for company "Company Excel" and inside that company folder is the Workbook "03 March 2018 Company Excel.xlsm". This .xlsm file will look at all sub-folders within "Company Excel" folder (in "03 March") and copy the contents of all .txt files and paste it in their own individual tabs (with the tabs being labelled with the .txt filename). Within "03 March 2018 Company Excel" there is a tab calld "Master" which will remain blank.

Could the macro possibly not be directory specific? As going forward, I believe it would be best to copy the previous months' .xlsm file for the next month and would like to avoid having to rename the filepath in the code.

When copying the contents of the .txt files, could the name of the .txt files be put against the pasted contents?
Just as a rough idea, some .txt files contents could range from 0 - over 1,000 lines.



As a side note: there are folders called "Unsuccessful" which has .pdf files. I would like for their filenames to be pasted in a new tab with the same name as the folder. There are also .pdf files in the date folders (mentioned above) which I would like their filenames to be pasted in a tab called "Successful". (Maybe it would be easier to have those .pdf files in a folder called "Successful"?)

I think this is a big ask so thank you in advance!!!

 

[Andrzejek]

I will fiddle with extracting the company names from the string of text." - so this line of code:[tt][blue]
strCompany = Trim(Mid(strTextLine, 51))[/blue]
[/tt]does NOT give you the Company name? (Again, based on the txt file you've provided)



---- Andy

There is a great need for a sarcasm font.

 

[Nick V]

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.

There is another variable problem but not all that fussed however:

CC12345678 | 20300 | 25012018 | 8fb782d1 | 4ecf-888e-d068-7ab31afd51f0 Payment to JWB | .txt name | directory
CC34567890 | 9266 | 23012018 | 20441887 | Payment to Ross & Robs | .txt name | directory

Its the JWB line when collating all the .txt files which holds some of the reference (8fb782d1-4ecf-888e-d068-7ab31afd51f0). There is another company which does not even include their company name in their .txt file.

D01CC9066728A +000002210230220185272906

----------------

In terms of the Immediate Window test. When running with the Debug.Print coding you provided, plenty of Days, Months and Years appear but again, stops and errors. With Debug.Print "The Year is " & CInt(Mid(strTextLine, 38, 4)) highlighted yellow.
The last entry in the Immediate Window is The Day is 23 but I would have assumed the last entry to be Year?

Thanks.

 

[Andrzejek]

Hi Nick,

In your quote you have provided, you show how the data looks in Excel while processed in the previous way. It would be much more helpful if you would provide the actual txt file that causes the issue, like - for example, the file where "There is another company which does not even include their company name in their .txt file."

So if you have txt file(s) that have different format, we need to know about them. As far as I know at this time, the only txt file format you have is the one I mentioned in my post above. So all of my code is based on this one example. The code WILL NOT WORK if you have other formats of txt files. :-(

As far as the error you are getting, you can easily determine which txt file is the cause of that issue/error. Copy this txt file and paste it in here, let us look at it.
Otherwise, all what we can do is guess...


---- Andy

There is a great need for a sarcasm font.

 

[Nick V]

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

 

[Nick V]

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 counter capturing these .txt files unless there is another way within the current code?

The filename tends to start with WH and then the remit number 62058 (the same within the filename above) followed by R.

 

[Andrzejek]

OK, based on your new samples of the txt files - there is some garbage before the name of the Company that was not in the files I had access to - which you want to ignore.

So change:
[tt]strCompany = Trim(Mid(strTextLine, 51))[/tt]
to
[tt]strCompany = Trim(Mid(strTextLine, [blue]90[/blue]))[/tt]

BTW - do you know what these lines of code do?
They pretty much say: take the line of the text from this file, start at the 90th position and give me the rest of that line of text. And Trim it (remove any spaces at the front and at the end)

By the same logic, this line (for example):
[tt]strCase = Mid(strTextLine, 4, 10)[/tt]
says:
Start at the 4th position and give me next 10 characters. Keep it in the variable called [tt]strCase[/tt]

Discloser - I don't want to insult anybody here, I just don't know how much knowledge people have.


---- Andy

There is a great need for a sarcasm font.

 

[Nick V]

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.

 

[Andrzejek]

I don't think so.
I did run the code against the folder / files you had attached and I did not have any issues.

It may be helpful if you would provide another zip file of what Folders / files you have.
Include the Excel with your up-to-date code as well.
So I can run it and - hopefully - recreate your issue.

Make sure you do NOT include any sensitive/private data - if you have any.

---- Andy

There is a great need for a sarcasm font.

 

[Nick V]

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 always filter it out from the main tab).

 

[Andrzejek]

I've got your zip, extracted all, and I just run your code in Compile.xlsm
I have data in rows 6 to 215
Rows 6 - 174 have Company names (Column E)
rows 175 - 215, no Company names in column E

No errors.


---- Andy

There is a great need for a sarcasm font.

 

[Nick V]

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. Perhaps its an error whereby there is not enough text within a single row in those .txt files? Does that make sense?

 

[Andrzejek]

Yes, it makes sense.
So, what do you want to happen when you come across file like this?
Do you want to detect the length of the text line and if is other than certain number of characters, skip/ignore the file?
Can you reliably check the name of the file and skip it?
Do you want to detect the error and based on that ignore the file?

By the way, if you want to have a hyperlink to the location of the file in column G,
instead of this code:

Range("A" & intR & ":G" & intR).Value = Array( _
    strCase, lngAmt, datDate, strRef, strCompany, _
    strFName, strFPath)

You may try this:

Range("A" & intR & ":[blue]F[/blue]" & intR).Value = Array( _
    strCase, lngAmt, datDate, strRef, strCompany, strFName)[blue]
ActiveSheet.Hyperlinks.Add ActiveSheet.Range("G" & intR), strFPath, , , "Link"[/blue]

And if your Compile.xlsm file is always at the root of your folder structure, instead of hard-coding the Path, you may do this:

[green]
'folderspec = "C:\Users\040428\OneDrive - London Borough of Croydon\Documents\Desktop\Parking\Bailiffs\Remittance .txt Invoice .pdf\2018\03 March"  'CHANGE THIS[/green]
folderspec = Application.ActiveWorkbook.Path

---- Andy

There is a great need for a sarcasm font.

 

[Nick V]

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 with P whereas the other .txt file finishes with R.

Just to give an idea as to what contents are in WH62057R:
5321305,CC10536203,,BY22CXT,"Andy Rzejek",16,203.00,0.00,203.00,,203.00
5323316,CC89963784,,LB663CXP,"MR NICK VARGAS 033331",36,0.00,0.00,0.00,,127.00
5321362,CC90082118,,DE22DOR,"Skip Vought",36,0.00,0.00,0.00,,128.00

I believe either scenario would work (length of text? and the filename). So far, the filename has been consistent, perhaps that is the better solution? If another error occurs, is there a way to skip it? Or is that not good practice?

Thanks.

 

[Andrzejek]

If you want to by-pass files that start with WH and end with R, you may try this (in 2 places in your code)

Select Case Split(oFile.Name, ".")(1)
    Case "txt"[blue]
        If Left(Split(oFile.Name, ".")(0), 2) <> "WH" And _
            Right(Split(oFile.Name, ".")(0), 1) <> "R" Then
                [/blue]
            Call GrabDataFromTXT(oFolder.Path, oFile.Name)[blue]
        End If[/blue]
    Case "pdf"[green]
        'Debug.Print oFolder.Name & "__", ">>"; oFile.Name, "<<"[/green]
End Select

---- Andy

There is a great need for a sarcasm font.

 

[Nick V]

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.

 

[SkipVought]

“...is there a way to skip it?”

I see you already did that in your example!

Good job, Andy!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Nick V]

In the same scenario I have a query regarding .pdf files and their locations.
Should I start a new thread or may I ask within this one?

 

[SkipVought]

Best to post a new thread.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]