×
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

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

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

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

(OP)
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!!!

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

Hi,

It is a big task. What have you done?

Please post any code you are using and where you are having specific issues.

Skip,

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

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

(OP)
Hi SkipVought,

I have some coding which works for the .txt files within the same location as my master workbook however, upon my travels of searching for something similar I believe I have over encumbered myself with too much coding. Really, I have confused myself.

Sub TxtImporter()
Dim f As String, flPath As String
Dim i As Long, j As Long
Dim ws As Worksheet
Dim PathName As String

Application.DisplayAlerts = False

Application.ScreenUpdating = False

Invoice .pdf\2018\03 March\BJW\_All .txt\"
flPath = ThisWorkbook.Path & "\"
flPath = ThisWorkbook.Path & Application.PathSeparator

i = ThisWorkbook.Worksheets.Count
j = Application.Workbooks.Count
f = Dir(flPath & "*.txt")

Do Until f = ""
'Workbooks.OpenText flPath & f, _

Workbooks.OpenText PathName & f, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, TrailingMinusNumbers:=True
Workbooks(j + 1).Worksheets(1).Copy After:=ThisWorkbook.Worksheets(i)
ThisWorkbook.Worksheets(i + 1).Name = Left(f, Len(f) - 4)
Workbooks(j + 1).Close SaveChanges:=False
i = i + 1
f = Dir

Loop

Application.DisplayAlerts = True

Worksheets("Master").Activate

End Sub

-----

I am finding it difficult to to implement the searching through subfolders and the consequent loop within this coding. Really annoying.
The above code works wonderfully however, with the amount of different .txt files that come in weekly I needed to file them away better.
I have coding for the .pdf part of my query but again, similar problem.

Many thanks in advance.

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

I’ve re-read your original post after briefly perusing your posted code.

You seem to be creating multiple workbooks along with multiple worksheets. What a mess!

You need a solution for drilling down through all folders to get to the text files of interest. That can be accomplished using File System Object objects in a recursive procedure (a procedurevthat calls itself to go to the next sub-folder.)

However, your solution for storing the data is absolutely horrible! It violates the best and accepted rules for data storage. You need one database/workbook and as few worksheets/tables as is reasonable. If you pursue your current objective, you will have a virtually useless and confusing storage system.

What do you intend to do with the data you plan to store in this multiple workbook “system”?

Skip,

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

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

(OP)
Thank you for your reply.

The coding grabs all .txt files that are stored in the same location as the database/workbook and puts them in their own tab. The code doesn't create any more workbooks, just worksheets.

The reason for there being a tab per .txt file is that in parallel to the .txt files are .pdf files that serve as invoices. So where the .txt files holds basic information (date/amount/invoice reference/more data), the .pdf shows that information in greater detail. This is a way of checking the data of the .txt files without having to open every single one. I then compile all tabs into the "Master" tab in one easy to view way.

Going forward, I thought it best having a workbook per month, per company. Potentially three-four workbooks within a month. In the host folder/main folder that sits outside the months' folders would be another workbook which would grab all that data from the "Master" tabs and put them into one summary sheet (and excluding all the additional tabs).

I don't know the coding that loops through all sub folders to look at the .txt files nor where to put it. Originally I had a folder labelled "_All" where I would dump all .txt files and then I'd add a line of code which would look at that folder. But that was just getting tiresome and I felt another folder wasn't neccessary (but due to my lack of knowledge, it seems I'm having to use this way).

Thanks again.

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

(OP)
To add to my previous post is a look at the director string.

I've moved it localled away from the network so I can fiddle around with the structure without getting prompted when someone has a file open.

I've uploaded the picture via Engineering - hopefully its viewable.

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


"I don't know the coding that loops through all sub folders" - here is one link and another that should give you an idea of how to do it. smile

May come handy in other coding, but I am with Skip - your approach is a bad idea. sad


---- Andy

There is a great need for a sarcasm font.

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

(OP)
Thanks Andy - would you be able to suggest an alternative approach?

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

Quote:

Going forward, I thought it best having a workbook per month, per company. Potentially three-four workbooks within a month.
WHAT???? THAT is a disasterous design strategy.

Quote:

I then compile all tabs into the "Master" tab in one easy to view way.
YES!!! THAT is a useable design strategy!!! You need ONE workbook with a sheet/table that you can use to query, analyze or answer any questions regarding these invoices. I’d dispense with all the other tabs and just assemble ALL the invoices into the Master table as they are encountered.

And certainly, NOT a workbook per month! The invoice date must be a field in the table.

Skip,

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

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

That all depends on the business rules and requirements of what you are trying to accomplish. What information someone is trying to access / analyze? All (most?) of your data is in txt and pdf format scattered all over some folder structure – is that the best way to have it? Maybe a little simple data base would do the trick instead?

Sometimes you need to step back and ask yourself – is there a better way to do it?


---- Andy

There is a great need for a sarcasm font.

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

(OP)
RE: Skip
I would usually just compile all the information into one summary tab, throw in some formulas and that'll be it - I would have just summarised the data. The problem I find is within a single month under a single company, (under the current folder structure), there could be 11 subfolders :
(08 05 2018 LBCSBI / 08 05 2018 LBCSBN / 14 05 2018 LBCRT / 14 05 2018 LBCSBN / and so on) - I thought it best to lay it out this way.(?)

And also, the original .txt filename isn't specified by the LBCSBI/LBCSBN, therefore having them all in one folder would mean having to rename them. I do have a copy of the emails sent from the companies saved in their folders (08 05 2018 LBCSBI/Emails) to easily refer back to the communcation trail and attachments.

RE: Andy
The information needed within the .txt file would be the company name, date, amount, and a 10 character reference number.
The information needed from the .pdf file is in the filename by default as it includes the 10 character reference number. But it is within this .pdf file that hold specific information. (Also, there could be more than one .pdf with the same filename because it could be a recurring case, it is only identifiable by the month and week it is sent in via the company.

So essentially, for the month of May in the week of 14 05 2018 LBCRT there is a case XX12345678. I know to look within that same month and sub folder 14 05 2018 LBCRT for the .pdf file that relates to that case number.

Am I over complicating things? I thought I could just make do with folder for month, folder per week the invoice is sent in.

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

Don’t care about the FOLDER configuration.

I have not referred to changing any FOLDER configuration.

How you configure your folders is your business and concerns me not.

I am referring to many workbook with scads and scads of sheets.

I am suggesting that a much MUCH better Excel configuration would be ONE workbook with ONE worksheet containing ALL your invoices in ONE table. And this would not be a summary. You may have other sheets containing other data like references to your pdf files, which are basically like invoice images, if I understand you correctly.

We will need to know what your folder structure is in order that the essential data contained within the structure can be mapoedvtonthe file structure for your invoices, ie company name, date and sub date, etc

Skip,

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

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

(OP)
RE: Skip

Apologies for the miscommuncation.

You are saying that for the entirety of the year which invoices comes in to just have one Workbook with one Worksheet that holds all invoices. I suppose I coud have the end folder directoy against each invoice...
Invoice:
[D01CC345678 +0000011590105201820512123 Payment to Whoever & Whomever ] then have "05 May\Whoever & Whomever\08 05 2018 LBCRT"

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


“You are saying that for the entirety of the year now forward which invoices comes in to just have one Workbook with one Worksheet that holds all invoices.”

YES! In this way, there is ALWAYS one place for everything.

When your boss or the VP asks a question regarding first quarter or last year, the answer is there! This is how databases work. Professionals do not chop up data into arbitrary locations that makes such research or analysis much more difficult.

Skip,

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

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

(OP)
So my method of having an _All folder which holds all the .txt files would be a valid way of storing the data which in turn would make it easier for the macro to pull the data together?

But then I am in a situation where the .txt files that have the same names would need to be relabelled...although...we do use SharePoint which allows the same name filenames as they are differentiated via metadata. I could always transfer it over there which would side step the conflicting filenames when storing the .txt files.

With over hundreds of .txt files to copy the data from and paste into one workbook (and into one worksheet), would that in itself be a burden? Is there a way to avoid having to start from the very beginning (or I suppose it would be safer to capture everything?).

Thanks.

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

You don’t need to change your folder structure. We just need to know how to map the data in your folder structure to the table in order to store the data to uniquely identify each invoice in the table.

Skip,

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

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

...so lets suppose your invoice file has this structure...
date/amount/invoice reference/more data
4 fields. But that’s not enough data to identify this invoice uniquely from any other invoice. You need fields for...

Invoice Nbr
Invoice Date (assuming that the date field is not the invoice date)
Company Name

...and maybe other fields as well.

This additional data will no doubt need to be extracted from the folder path structure.

Skip,

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

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

(OP)
In which case, having the main (or host) Workbook sitting outside the Months' folder would be ideal. If possible, that Workbok would look through all subfolders from the main/host Workbooks location and compile .txt data into one sheet.

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

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?

Skip,

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

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

(OP)

Quote (Skip)

...so lets suppose your invoice file has this structure...
date/amount/invoice reference/more data
4 fields. But that’s not enough data to identify this invoice uniquely from any other invoice. You need fields for...

Invoice Nbr
Invoice Date (assuming that the date field is not the invoice date)
Company Name

...and maybe other fields as well.

This additional data will no doubt need to be extracted from the folder path structure.


True - details of the .txt are just simplified invoices:
[D01CC345678 +0000011590105201820512123 Payment to Whoever & Whomever ]
The CC######## is the case number.
The date is within the numeric segment [01052018]
The number at the end [20512123] is the number for the invoice/individual.
The numbers before the date [+000001159] is a debit amount £11.59 (credit would be a minus at the start.

I would then have the folder path structure assigned next to this information.
The filename of the .txt file has the date which differs from the date within said file.
.txt filename date = week of batch invoices
Probably 7 fields.

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

(OP)

Quote (Skip)

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

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

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.

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

So lets look at this string...

[D01CC345678 +0000011590105201820512123 Payment to Whoever & Whomever ]

...broken down to 2 SPACE delimiters

[D01CC345678
+0000011590105201820512123
Payment to Whoever & Whomever ]

Case Number CC########
Debit Amount 10 characters
Date as ddmmyyyy 8 characters
Invoice Number 8 characters
Payment To to end of string

Skip,

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

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

(OP)
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.

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

(OP)
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.

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

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

Skip,

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

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

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

CODE

Sub FolderDrillDown()
'set a reference to the Microsoft Scripting Runtime Library
    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim oFLDR  As Folder
    Dim oFile As File
    Dim s As String, folderspec As String
    
    folderspec = "C:\Users\Skip\Documents\TT"     '<<<MODIFY THIS PATH
    
    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,

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

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

(OP)

Quote (Skip)

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.

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

(OP)
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.

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

Quote:

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,

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

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

(OP)
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?

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

Check your Immediate Window for the results.

BTW, where does your code reside?

Skip,

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

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

(OP)
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.

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

Nothing in the Immediate Window?

Skip,

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

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

(OP)
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 <<

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

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 DebugPrint... 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,

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

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

(OP)
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.

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

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 (ActiveSheet.QueryTables.Add(…) 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,

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

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

(OP)

CODE -->

Sub Refresh_FAO_SKIP()
'

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

Thank you.

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

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,

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

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

(OP)

CODE -->

Sub EditTextImport_FAO_SKIP()
'

'

'
    Range("A6:H13").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Park\Remittance .txt Invoice .pdf\2018\09 September\Ross and Roberts\11 09 2018 LBCRT\BAILIFFROSSANDROB10092018.txt"
        .CommandType = 0
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 2, 4, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(3, 10, 10, 10, 8, 8, 42)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub 

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

This will be a new procedure along with the other two.

CODE

Sub GetIMPORT(oFile As File)
    
    With [BAILIFFROSSANDROB10092018_1].QueryTable
        .Connection = oFile.Value
        .CommandType = 0
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 2, 4, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(3, 10, 10, 10, 8, 8, 42)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub 
In addition make these changes in your first two procedures in the Case "txt"

CODE

'...
        Select Case Split(oFile.Name, ".")(1)
            Case "txt"
                GetIMPORT oFile
            Case "pdf"
                'Debug.Print oFolder.Name & "__", ">>"; oFile.Name, "<<"
        End Select
'... 

Skip,

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

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

At this point you ought to be able to run the FolderDrillDown and get all your text files into the Master, except for a few tweeks. I think this will work for you, to modify my previous post...

CODE

'...
        Select Case Split(oFile.Name, ".")(1)
            Case "txt"
                GetIMPORT oFile
                
                [BAILIFFROSSANDROB10092018_1].Copy
                With Sheets("Master")
                    lRow = .UsedRange.Row + .UsedRange.Rows.Count
                    .Cells(lRow, "A").PasteSpecial xlPasteValues
                End With
            Case "pdf"
                'Debug.Print oFolder.Name & "__", ">>"; oFile.Name, "<<"
        End Select
'... 



Skip,

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

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

(OP)
There is an error against:

CODE -->

.Connection = oFile.Value 
With .Value highlighted.

Error Message : Compile error: Method or data member not found.

Thereafter, Sub GetIMPORT(oFile As File) is highlighted in yellow.

Also, why does your query table include "_1"?

Quote:

With [BAILIFFROSSANDROB10092018_1].QueryTable

Thanks.

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

Sorry. blush

.Connection = oFile.Name

Skip,

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

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

Quote:

Also, why does your query table include "_1"?

Select your IMPORT sheet.

Select the Drop Down button in the Name Box and then the BAILIFF.... name.

Select the BAILIFF.... name again. The SECOND select will display the LAST portion of the name.

Mine shows SANDROB10092018_1.

Skip,

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

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

(OP)
I see.

Data > Name Manager > (Only one there) BAILIFF...name > Edit > BAILIFFROSSANDROB10092018_1.

After amending oFile.Name an error pops up : Run-time error '1004': Application-defined or object-defined error.
Selecting Debug highlights .Connection = oFile.Name

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

Didn’t refer to the Name Manager. Nothing should be changed in the Name Manager!

The Name Box is just above column A, usually displaying the Active Cell.

Please go back an go thru the process previously outlined to determine for certain what YOUR data range is currently named AND that it references the proper range on the IMPORT sheet.

Skip,

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

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

(OP)
Apologies for the confusion - when I select the Delimited range, the name box does come up with BAILIFFRO....

I have not changed any ranges or names.

[quote ][/Run-time error '1004': Application-defined or object-defined error.
Selecting Debug highlights .Connection = oFile.Namequote]

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

Plz COPY everything in the module that you’re using and PASTE here. That should include all three procedures we’ve been referring to.

Skip,

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

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

(OP)

CODE -->

Sub FolderDrillDown()
        
    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim oFLDR  As Folder
    Dim oFile As File
    Dim s As String, folderspec As String
    
    
    Worksheets("Import").Activate
    
    folderspec = "C:\Park\Remittance .txt Invoice .pdf\2018\\"     '<<<MODIFY THIS PATH
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    Set oFolder = oFSO.GetFolder(folderspec)
    
    For Each oFile In oFolder.Files
        Select Case Split(oFile.Name, ".")(1)
            Case "txt"
                GetIMPORT oFile
                
                [BAILIFFROSSANDROB10092018_1].Copy
                With Sheets("Master")
                    lRow = .UsedRange.Row + .UsedRange.Rows.Count
                    .Cells(lRow, "A").PasteSpecial xlPasteValues
                End With
            Case "pdf"
                'Debug.Print oFolder.Name & "__", ">>"; oFile.Name, "<<"
        End Select
'        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"
                GetIMPORT oFile
                
                [BAILIFFROSSANDROB10092018_1].Copy
                With Sheets("Master")
                    lRow = .UsedRange.Row + .UsedRange.Rows.Count
                    .Cells(lRow, "A").PasteSpecial xlPasteValues
                End With
            Case "pdf"
                'Debug.Print oFolder.Name & "__", ">>"; oFile.Name, "<<"
        End Select
        Next
        
        GetSubFolder oFolder
    Next
     
End Sub



Sub GetIMPORT(oFile As File)
    
    With [BAILIFFROSSANDROB10092018_1].QueryTable
        .Connection = oFile.Name
        .CommandType = 0
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 2, 4, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(3, 10, 10, 10, 8, 8, 42)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub 

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

Sorry, been out and about running errands. I’ll be out again today. I’ll be checking periodically.

Please read the FAQ linked below. It’s a way to observe what’s happening in your code when it runs. At the point of either a planned BREAK or when the running code stops, you can hit the DeBug button and then use the Watch Window to see what values are held by variables and Objects.

FAQ707-4594: How to use the Watch Window as a Power Programming Tool

My code ran, drilling down into two levels and accumulating data from those files into the Master table. So far I can’t see what’s wrong with what you posted. But, sadly, I haven’t had time to do justice to that analysis.

Skip,

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

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

Nick V,
You can also try this approach. Have a new Excel file with just one worksheet.
Add the reference to Microsoft Scripting Runtime Library

Based on your sample of the txt file, I assume what you want is: ignore first line of text, get the pieces from the rest of the txt file.

For some reason you had a header row in row 5 and your data started in row 6 (?) that's why I have intRow = 6 in this code. You would have to provide your own header row (5), and possibly clean the data before grabbing new data. But that's easy. As well as setting some hyperlinks to locations of your files. Or formatting the dates as date, not just text.

Also, I don't know what you want to do with PDF files, how to deal with /display them in Excel

And try this code:

CODE

Option Explicit
Dim intR As Integer

Sub FolderDrillDown()
        
    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim oFLDR  As Folder
    Dim oFile As File
    Dim s As String, folderspec As String
    
    intR = 6

    'Worksheets("Import").Activate
    
    folderspec = "E:\Andrzej\TT\Sample_Files"  'CHANGE THIS
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    Set oFolder = oFSO.GetFolder(folderspec)
    
    For Each oFile In oFolder.Files
        Select Case Split(oFile.Name, ".")(1)
            Case "txt"
                Call GrabDataFromTXT(oFolder.Path, 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"
                Call GrabDataFromTXT(oFolder.Path, oFile.Name)
            Case "pdf"
                'Debug.Print oFolder.Name & "__", ">>"; oFile.Name, "<<"
        End Select
        Next
        
        GetSubFolder oFolder
    Next
     
End Sub

Public Sub GrabDataFromTXT(ByRef strFPath As String, ByRef strFName As String)
Dim strTextLine As String
Dim strCase As String
Dim lngAmt As Long
Dim strInvDate As String
Dim strRef As String
Dim strCompany As String
Dim blnOkToProcess As Boolean

Open strFPath & "\" & strFName For Input As #1
Do While Not EOF(1)
    strCase = ""
    lngAmt = 0
    strInvDate = ""
    strRef = ""
    strCompany = ""
    
    Line Input #1, strTextLine
    If blnOkToProcess Then
        strCase = Mid(strTextLine, 4, 10)
        lngAmt = Val(Mid(strTextLine, 25, 9))
        strInvDate = Mid(strTextLine, 34, 8)
        strRef = Mid(strTextLine, 42, 8)
        strCompany = Trim(Mid(strTextLine, 51))
        
        Range("A" & intR & ":G" & intR).Value = Array( _
            strCase, lngAmt, strInvDate, strRef, strCompany, _
            strFName, strFPath)
        intR = intR + 1
    End If
    blnOkToProcess = True
Loop
Close #1

End Sub 

I talked to Skip about my code and in his opinion: "It’s just another method in the toolbox." smile

---- Andy

There is a great need for a sarcasm font.

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

(OP)
Hi Andy,

Firstly, thank you for pasting your code. It worked! I used a month with only two .txt files to test.

I will need to eventually, have the '+' in front of the amount (as rarely, there can be a '-'). Is there a way this can be implemented within the coding?

Quote:

As well as setting some hyperlinks to locations of your files. Or formatting the dates as date, not just text.

I'll do some research on how to set up hyperlinks to the location of the files.
Assuming I can format the dates after the code has brought in the data? Or would it be easier to do it in one go?

Quote:

Also, I don't know what you want to do with PDF files, how to deal with /display them in Excel

In a separate tab, I would have the filename of the PDF (as it starts with the unique case number).
The directory of the PDF file as a hyperlink for quick access.

The problem with the PDF files is that the companies don't send them in the same format.


:::

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

As you can see from the two different companies invoice samples, they differ. Starts off lovely and then in the middle it goes slightly redundant I suppose.
The first three columns are important. The fourth for Ross & Robs is their reference number for that case. You think, when cleaning the data into another tab (if that is the correct method?) to put a formula which would extract the text of the company which would then leave the reference number?

Thanks so very much the both of you. Apologies for the difficulties I may have caused.

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

"have the '+' in front of the amount (as rarely, there can be a '-'). "

Modify one line of code to:
lngAmt = Val(Mid(strTextLine, 24, 10))
You will not get the +, but you should get the '-' which should be ok since Excel does not have + in front of positive numbers, right?

"Assuming I can format the dates after the code has brought in the data? Or would it be easier to do it in one go?"

You can do it in one go -

CODE

....
Dim datDate As Date
...
        lngAmt = Val(Mid(strTextLine, 25, 9))
        datDate = DateSerial(Mid(strTextLine, 38, 4), Mid(strTextLine, 36, 2), Mid(strTextLine, 34, 2))
        strRef = Mid(strTextLine, 42, 8)
        strCompany = Trim(Mid(strTextLine, 51))
        
        Range("A" & intR & ":G" & intR).Value = Array( _
            strCase, lngAmt, datDate, strRef, strCompany, _
            strFName, strFPath)
        intR = intR + 1 


---- Andy

There is a great need for a sarcasm font.

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

(OP)
Thanks Andy,

Changed the code and when running it errors: Run-time error '13': Type mismatch.
And the following is highlighted in yellow:

datDate = DateSerial(Mid(strTextLine, 38, 4), Mid(strTextLine, 36, 2), Mid(strTextLine, 34, 2))

I will fiddle with extracting the company names from the string of text.

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

DateSerial wants NUMBERS as arguments...

CODE

datDate = DateSerial(Cint(Mid(strTextLine, 38, 4)), cint(Mid(strTextLine, 36, 2)), cint(Mid(strTextLine, 34, 2))) 

Skip,

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

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

(OP)
Hi Skip

Same error type and the new line of code is highlighted in yellow.

Is there something I am doing incorrectly?

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

Put a BREAK on that line. Run to the BREAK.

SELECT each MID()...

CODE

datDate = DateSerial(Cint(Mid(strTextLine, 38, 4)), cint(Mid(strTextLine, 36, 2)), cint(Mid(strTextLine, 34, 2))) 
..and observe what is returned in the Watch Window.

Should be the YEAR, MONTH & DAY values.

Skip,

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

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

What Skip is saying, you should get the valid data this way:

CODE

Debug.Print "The Year  is " & Cint(Mid(strTextLine, 38, 4))
Debug.Print "The Month is " & Cint(Mid(strTextLine, 36, 2))
Debug.Print "The Day   is " & cint(Mid(strTextLine, 34, 2))

datDate = DateSerial(Cint(Mid(strTextLine, 38, 4)), cint(Mid(strTextLine, 36, 2)), cint(Mid(strTextLine, 34, 2))) 

Based on the txt file you have provided with the date in it here:

HROSSANDROB19022018000000000001+000060900                                                                                                     
D01CC90779398          +0000203001302201820751445                                          Payment to Ross & Robs                         
D01CC90779312          +0000203001302201820751125                                          Payment to Ross & Robs                         
D01CC90779337          +0000203001302201820751778                                          Payment to Ross & Robs                         

 


---- Andy

There is a great need for a sarcasm font.

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

"I will fiddle with extracting the company names from the string of text." - so this line of code:
strCompany = Trim(Mid(strTextLine, 51))

does NOT give you the Company name? (Again, based on the txt file you've provided)
ponder


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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.

Quote:

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.

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

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

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


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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.

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

(OP)
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.

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

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:
strCompany = Trim(Mid(strTextLine, 51))
to
strCompany = Trim(Mid(strTextLine, 90))

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):
strCase = Mid(strTextLine, 4, 10)
says:
Start at the 4th position and give me next 10 characters. Keep it in the variable called strCase

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.

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

(OP)
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.

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

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.

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

(OP)
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).

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

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.

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

(OP)
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?

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

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:

CODE

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

You may try this:

CODE

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

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:

CODE


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

---- Andy

There is a great need for a sarcasm font.

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

(OP)
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.

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

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)

CODE

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


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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.

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

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

I see you already did that in your example!

Good job, Andy!

Skip,

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

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

(OP)
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?

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

Best to post a new thread.

Skip,

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

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!

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