Hi Team,
I need a VBA script that can copy multiple text file rows and paste in excel columns.
See attached example of file contents and expected excel file output.
Rgds.
Hi Themba,
It's easier with Power Query,
see file attached: just put all text files in a folder, change the path to that folder in the excel output file and press Refresh All button from Data tab.
The only problem I see is that you have a header file mixed between data files, not obvious which file has the headers.
Thanks Catalin,
This will greatly assist.
The files do not necessarily have a header file as they all contain data.
Is there a way of increase the number of columns to be pulled as the files I'm working with have various number of rows.
Rgds.
All you have to do is to replace in the query:
= Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"})
with:
= Table.ExpandTableColumn(#"Removed Columns", "Custom", Table.ColumnNames(Table.Combine(#"Removed Columns"[Custom])), Table.ColumnNames(Table.Combine(#"Removed Columns"[Custom])))
