Import multiple xlsm files with different sheet names

[MadhavTR]

I have a requirement to import two xlsm files with different sheet names and I am planning to merge the output in single Output. The two input files have same format but different sheet names.

I tried using a directory and then a formula for sheet name but then got stuck because of different sheet names. Can anyone help in this?

Thanks

[binu_acs]

@MadhavTR Try the attached batch macro

[MadhavTR]

@binu_acs Hi.. does this change the column names in the subsequent workflow? for example i had period, bu as some of the columns, now i am getting error "parse error at char(4): unknown variable "bu" expression 1"

[MadhavTR]

Oh i think i got it is missing headers as my data starts my row 6 in the input folder. How can i do that in the macro you shared? @binu_acs 

[binu_acs]

@MadhavTR inside the macro change the below settings

[MadhavTR]

@binu_acs  I need to change the template file also right? What do i need to select while setting up the connection? Select the sheet name or Import only the list of sheet names?

[MadhavTR]

I selected import only list of sheet and able to combine in the output but i am getting error (screenshot attached) in the 2nd sheet.ymc macro which i am not able to understand.

PS: i have only 2 files in my folder which i am trying to process.

[binu_acs]

@MadhavTR yes , you need to change the .xlsx file in the macro and change to .xlsm. i updated the workflow and attached here

[MadhavTR]

I did that already (changed the file type and connecting to the right folder). For some weird reason i getting the sheet names and file names twice after the first sheetNames block. When i go inside it and check it is giving me correct (2 files and 2 sheet names) but when i come to the main workflow and run it is giving me duplicates.

[MadhavTR]

When i am digging more macro has a tendency to double up the values but i am not able to figure out how to fix it. Have you seen this issue?

Thanks for helping me.