VBA extract sheet data from multiple subfolders into master

[terriertrip]

Hello all. I've been searching for several days and trying different techniques with no apparent success, so I'm hoping someone can see what I have and have a better solution. The most comprehensive, succinct, and successful code I've been able to construct with the help of many, is the example below, however, the code doesn't look in subfolders and there are a couple of tweaks that I haven't been able to configure.

I've attached an example data set. The data sets are ASCII coded (basically you can see all data is read within column 1). All data are space delimited. The header information can remain intact, but once transferred it needs to autofill to lastrow of each relative data set. I've got some code that can delimit out data sets and remove the ASCII headers, so I think I can deal with delimiting it after all the files are transferred, but why isn't it looping through the sub-directories?

Please Login or Register  to view this content.

[terriertrip]

a humble 'bump'

[Leith Ross]

Hello terriertrip,

The problem was not the subfolders. It was the data was overwriting itself.

This macro solves the problem and works much faster since no workbook is being opened to parse the data.

Please Login or Register  to view this content.

[terriertrip]

Thank you so much Leith for your reply. I'm sorry I haven't been able to test the code yet. I got pulled to another direction yesterday. I will give it a go before I leave work today. If it does work **fingers crossed**, I'll let you know. I'm not sure how long it would take once it executes, so I'll let it run overnight. There's probably several hundred files it has to pull with sets of 15k x 110 data. It's all going into an ADB after compilation. I'll keep you posted.

[terriertrip]

Hi Leith. Code doesn't error, but nothing happens. I tested it on several different paths that contained sample files and nothing happens. If I give a bogus path, it errors there but otherwise doa. The code is in a module and I'm running it from there. Any suggestions?

[Leith Ross]

Hello terriertrip,

One 2 reasons I can think of that would cause that behaviour. Either there are no subfolders in the folder "Z:\Operations\Chupacabra\Data\" or there are no files in the subfolders whose names match "*dq1000d.las*". I tested the macro by creating a couple of subfolders and adding in your file and modifying the copies to tell them apart. everything worked on my end. I did the test on my "C" drive and one of my network folders. It was all good.

[terriertrip]

Hmmm. There are definitely subfolders and there are 2-3 files within different files named dq1000d.las. Like I said, I tried on both my local drive and our network drive to test it before attempting the entire directory. I have other routines that call files up on the network drive, so it isn't an issue of security. Since it's working on your end, the only thing I can think of is, is there a reference that I may not have enabled within my vba tools menu? Is there some sort of error handling that I could implement to step through the code? Microsoft Scripting Runtime enabled. I'm running Excel 2016 on windows 10. Code looks good. Totally stumped.

[terriertrip]

One thing that occured to me, the file I attached was a text file - only because EF wouldn't allow the attachment of the file in it's native format. I changed the extention to (*.txt) to upload. The actual file is an industry specific Log ASCII Standard format. (*.las). Could there be a conflict? Although, I changed the files to (*.txt) along with the IfThen code, but still just a flicker and a blank ws. sigh...