Combiner macro which takes data from row 1 and provides full path. No useful macro found

Question

There is folder, and within folder there are sub folders, where we have excel files, multiple excel files, multiple sheets, non-consistent.

Now I want to create a macro, where I enter the path , and it picks all the files, remember, there are multiple folders, the output should have full path and then of course sheet name, file name and the data.

The data starts from row 1 only so I have to make sure that the Alteryx doesn't take the first line as header as it is non consistent.

All the macros so far here are not dynamic and I don't even have the template sheet, all sheets are different hence need a macro which can stack all the sheets from all the files from all the folders.

SPetrie · Accepted Answer

A directory tool set to look into sub folders will get you a list of all files.

A dynamic input using those paths and set to output a list of sheet names only will get you the sheet name of every file in those directories.

Update the full path with file name to include the sheet names  c:\file.xlsx|||Sheet1 for example and then pass that a batch file with another dynamic input set with first row contains.

There are lots of batch file macro examples you should be able to use as a start as well.