importing text file from multiple folders

[jlamela]

I have a flow meter that outputs each day as a folder (ie: 20170101) in each folder there is a text file R1-800 I would like excel to look in each folder and import the text file to a column (ie: text from 20170101 on A, text from 20170102 on B and so on. I have some other VBA code to pick out the numbers that i need already and some other analytics. can anyone help?

[Alf]

As your information is rather sketchy regarding the folder structure I'v assumed that there is a master folder where all the date folder is stored. In my macro I'v called this
"D:\a_abc\" so you will have to change that.

As I also don't know how your text file look like I've not added any code for extracting that data so you will need to add that as well. Macro assumes the start date is the first of the month. Macro loops through sub-folders "20170101" if start month is January to the stop date you have given. Max stop date for January is of course "20170131"

OOOOOOps I really messed up the code, my mind was thinking excel but you are importing txt files so I've modifies the last part of the code

Please Login or Register  to view this content.

This is what a code for importing txt files to excel should look like but as I said before as I'v not seen your txt file and don't know what you wish to import you will also need to modify the some of the settings in this part of the macro.

Please Login or Register  to view this content.

Alf

[jlamela]

Alf not sure if you got the first two replies I don't see them on the thread? should the fname go at the end of the fPath = "D:\a_abc\" & Mid(StartDate, 1, 6) & "0" & i & "\" & fname (like this)?
it is not working it gives me a runtime error 1004 cannot find the text file

[Alf]

Alf not sure if you got the first two replies I don't see them on the thread?

Neither do I.

fname go at the end of the fPath = "D:\a_abc\" & Mid(StartDate, 1, 6) & "0" & i & "\" & fname (like this)?

First fName is defind as a String, then fName is defined as "fName = "R1-800.txt" and finally fPath and fName is combined in the line

Please Login or Register  to view this content.

As for the runtime error you need to check if the text file is really named "R1-800.txt" and you could step through the macro to see if the string fPath (hover with the mouse pointer) over fPath after the macro has passed on to the next line. See image

macro.jpg

Alf

[Alf]

As this macro works for me without any problem it must be a problem with the file name or you have not modified the folder string to your environment. I setup a master folder "D:\a_abc\" and you must modify it to your folder settings.

The easy way to find you folder settings is to record a macro when importing data from you txt fil. Post that macro and do my best to sort this out.

Alf

[jlamela]

you nailed it there was an extra 0 in the file name it is R1-0800 and that fixed it
I don't know how to mark a thread as "solved" but you did a grate job thanks again

[Alf]

You are welcome and thanks for feedback

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved