Import multiple xlsm files with different sheet names

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

Developer

Accepted answers

All comments

binu_acs

@MadhavTR Try the attached batch macro

Combine_Macro.yxzp

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.

Untitled.jpg

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

Combine_Macro.yxzp

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.

Untitled.jpg

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703