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!!!

 

[Nick V]

Yes. The workbook could reside at the top of the folder structure. The code could search that folder for sub-folders, drilling down to the .txt files and sub-folders within sub-folders.

What would be done with the .pdf files?

The .pdf file are full invoices. They are labelled like "Remit-#####.pdf".
When the company sends us this information there are
.txt file which is the simplified invoices
Remit file which is a breakdown of the invoices which has a list of case files.
A full list of more .pdf files which are separate invoices (some are rejected and some are not - this is identified through the Remit file).

I was going to just leave the .pdf files as they are. Filed away in their respective folders.
If someone wanted to, based upon the informatoin pulled in from the numerous .txt files into the one workbook, look where they could find the .pdf file and open it up (either trawl through the directory or somehow set up a hyperlink).

 

[Andrzejek]

So what Skip is suggesting, and what I was leaning to, is one place to have all the information available. Either one Excel file (I see you made some progress with it) or a data base. It does not matter, as long as you have one place with the information scattered right now all over the folders.
And it looks like you will have very few columns of data, which is great.
As far as PDFs, “somehow set up a hyperlink” in Excel is the way to go, in my opinion. Hyperlinks are easy in Excel, and since you will know the location of your PDFs, this will be a snap.



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

So lets look at this string...
[tt]
[D01CC345678 +0000011590105201820512123 Payment to Whoever & Whomever ]
[/tt]
...broken down to 2 SPACE delimiters
[tt]
[D01[highlight #FCE94F]CC345678[/highlight]
[highlight #FCAF3E]+000001159[/highlight][highlight #E9B96E]01052018[/highlight][highlight #8AE234]20512123[/highlight]
[highlight #729FCF]Payment to Whoever & Whomever[/highlight] ]
[/tt]
[highlight #FCE94F]Case Number[/highlight] CC########
[highlight #FCAF3E]Debit Amount[/highlight] 10 characters
[highlight #E9B96E]Date as ddmmyyyy[/highlight] 8 characters
[highlight #8AE234]Invoice Number[/highlight] 8 characters
[highlight #729FCF]Payment To[/highlight] to end of string

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

 

[Nick V]

RE: Skip

Yes that's correct.
This is going into completely new territory for me. Never had to deal with .txt files within Excel beore.

 

[Nick V]

RE: Andy

My problem is merging coding to coincide with the already placed code (if that makes sense?)
So I have a code which looks at a folder and inserts all hyperlinks for all .txt files. But not sure how to make it run parallel to the funtioning code.

 

[SkipVought]

Please show us several representative instances of the Path and Filename from various folders.

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

 

[SkipVought]

Here's a sub-folder drill-down technique.

You'll see that GetSubFolder() calls itself recursively.

Notice the Microsoft Scripting Runtime Library you must set a reference to in Tools > References.

Modify the folderspec assignment to suite your application

Sub FolderDrillDown()
'set a reference to the [b]Microsoft Scripting Runtime Library[/b]
    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim oFLDR  As Folder
    Dim oFile As File
    Dim s As String, folderspec As String
    
    [b]folderspec[/b] = "C:\Users\Skip\Documents\TT"     '[b]<<<MODIFY THIS PATH[/b]
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    Set oFolder = oFSO.GetFolder(folderspec)
    
    For Each oFile In oFolder.Files
        Select Case Split(oFile.Name, ".")(1)
            Case "txt"
                Debug.Print oFolder.Name, oFile.Name
            Case "pdf"
                Debug.Print oFolder.Name, oFile.Name, "<<"
        End Select
    Next
    
    GetSubFolder oFolder
End Sub

Sub GetSubFolder(oFLDR As Folder)
    Dim oFolder  As Folder
    Dim oFile As File
        
    For Each oFolder In oFLDR.SubFolders
        For Each oFile In oFolder.Files
            Select Case Split(oFile.Name, ".")(1)
                Case "txt"
                    Debug.Print oFolder.Name, oFile.Name
                Case "pdf"
                    Debug.Print oFolder.Name, oFile.Name, "<<"
            End Select
        Next
        
        GetSubFolder oFolder
    Next
     
End Sub

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

 

[Nick V]

Please show us several representative instances of the Path and Filename from various folders.
Skip,

Remittance .txt Invoice .pdf [folder]

2018 [folder]​

01 January​

02 February​

03 March (etc - to 12 December)​

JWB​

Ross & Robs​

Whyte​

Within each folder are different dated folders based upon when those companies have created the invoice file.

.txt files are labelled uniquely also, for example:
bailiffWH62058P090318
BAILIFFWH62057P090318
BAILIFFROSSANDROB08052018
BAILIFFROSSANDROB21052018
(some .txt files are labelled the same as the companies could have missed off some invoice details from the previous file)
BAILIFFJBW-100310


------

Thank you for the code, will try it out shortly.

 

[Nick V]

Hi Skip,

I have modified the folderspec and added a line of code for the Microsoft Scripting Runtime Library - but despite physically be able to identify its location an error keeps popping up with Run-time error '32813' Name conflicts with existing module, project, or librar - When I click on Debug it highlights :

Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\System32\scrrun.dll"

As mentioned, I able to go through this directory and find scrrun.dll.

Thanks.

 

[SkipVought]

and added a line of code for the Microsoft Scripting Runtime Library

No,no,no code!

Tools > References.... scroll down to Microsoft Scripting Runtime and check the box.


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

 

[Nick V]

In which case I have already done that.

So I have copied the code you kindly set up. Changed the filepath / folderspec.

It seems to be doing something in the background and when i switch back to my workbook, nothing has happened.

Am I still doing something wrong?

 

[SkipVought]

Check your Immediate Window for the results.

BTW, where does your code reside?

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

 

[Nick V]

Code is in a module within the workbook that I wish for the results to populate in.

Created a button and assigned the macro to that button to see if there is any data being populated and at the moment, nothing. Cursor looks to be thinking. Workbook goes all white like its going a proceedure. But no results sadly.

 

[SkipVought]

Nothing in the Immediate Window?

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

 

[Nick V]

Sorry Skip,

In my haste to reply I did not check the Immediate Window.

Yes there are results there

Extract:
02 10 2018 LBCRT BAILIFFROSSANDROB01102018.txt
02 10 2018 LBCRT Remit-42474.pdf <<
Successful CR10938783-20171020-20984547-RETLET.pdf <<

 

[SkipVought]

I’d assume that there are many more than that.

Here’s the approch I’d suggest.

Create a sheet named IMPORT which will have a Text File Import Query.

You already have a sheet named Master, I believe. I assume that this is where ALL the text file data will be accumulated. It will have the column headings we have talked about.

In the code I posted, in the places where it has [tt]DebugPrint...[/tt] you will code the IMPORT of the txt data or the linking of pdf files.

So here’s what to do to begin the next step. In the IMPORT sheet, Data > Get External Data > Text Files... and drill down to ANY ONE of your .txt files. Go through the entire process of selecting the DELIMITER and specifying the Data Type of each column and return the data to the sheet.

When you’ve accomplished that then post back.

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

 

[Nick V]

Hi Skip,

I believe I have done as you have asked.

Created tab Import.
I recorded myself getting the .txt file through Data Ribbon > From Text button. Through this I selected the Delimiter of the data I wished to be separated.


Attached is a .zip folder which holds a replica of files and folder structure of existing one.

Thanks in advance.

 

[SkipVought]

Got your upload.

Appreciate your anticipation of the next step. HOWEVER, the code you incorporated is not what we need to do, and here's why. You recorded ADDing ([tt]ActiveSheet.QueryTables.Add(…[/tt]) a QueryTable to you sheet. We only need ONE QueryTable. Each time you might run that QueryTables.Add, you get another extraneous and unnecessary QueryTable that amounts to junk, which at some point may become counterproductive. BTW, you can and should save the code, as it is instructive and can be made useful.

Here's what we do need to do. Now that this ONE TIME task has been accomplished in your development of your workbook application, the repetitive task is to REFRESH the QueryTable with a new text file, the next text file that the FolderDrillDown/GetSubFolder process finds.

Turn on your recorder
Right-Click in the table and select Refresh. Complete the steps to parse the data and return contents to the sheet.
Turn off your recorded.

Copy 'n' Paste the recorded code here when you're done.



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

 

[Nick V]

Sub Refresh_FAO_SKIP()
'

'
    Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

Thank you.

 

[SkipVought]

Sorry, I meant select Edit Text Import... and complete the steps to parse the data.

PLEASE NOTE:
For your Date field, specify Date: DMY if you want to convert your ddmmyyyy text to actual dates in Step 3 of 3. Otherwise in Step 3, for the Date field, Select the TEXT option to preserve the leading zeroes.

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