Extract text from only second row from all excel files in folder.(this row is merged)

I want to,

Extract text from only second row of all excel files in folder along with file name and update in a new file, with the text and file name.
(PS: the row is merged column cells)

Appreciate any help. Thanks!

Links to get you started:

https://www.thespreadsheetguru.com/t...a-given-folder

https://stackoverflow.com/questions/...lder-using-vba

Quote:

---

the row is merged column cells)

---

one sheet or multiple sheets to each workbook?


here is an example for processing all the files in a folder, it only looks at the first worksheet, it also does not look at the merged cells, but that is not hard to add in

Code:

---

set xl = createobject("excel.application")
pth = "C:\mypath\"    '  change to suit
fname = dir(pth & "*.xls")  ' find first xls file in folder
do while len(fname) > 0
    set xl.wb = workbooks.open(pth & fname)
    s = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range("a2").Resize(, 5))), "\")  '  change the resize and separator to suit
    debug.print s
    wb.close false  ' do not save changes
    fname = dir  '  get next file
loop
xl.quit
set xl = nothing

---

this was typed directly into the browser, so may contain typos or code errors