topic Re: Combine different Excel (xlsx) files into one file with different sheets Through SAS CMD/VB scr in SAS Programming

Combine different Excel (xlsx) files into one file with different sheets Through SAS CMD/VB script

SASuserlot — Mon, 21 Nov 2022 21:30:15 GMT

I am looking to combine all the excel files in folder starts with a specific into one file ( use each individual excel file as sheet in combine file) after combination delete individual files. I am looking to do it instead of importing them back into SAS datasets and doing proc report. Is there a way I can do it through using xCMD or vbscript code running through the SAS. Thank you.

Re: Combine different Excel (xlsx) files into one file with different sheets Through SAS CMD/VB scr

Reeza — Mon, 21 Nov 2022 21:54:23 GMT

If they're all in one folder here's a script. https://www.codeproject.com/Tips/5264786/Merge-Many-Excel-Files-into-one-VBS
Last one on the page. I'd modify it to not use prompts and to have SAS pass the folder name.
Note that it combines all in a folder.

Re: Combine different Excel (xlsx) files into one file with different sheets Through SAS CMD/VB scr

SASuserlot — Wed, 23 Nov 2022 00:25:58 GMT

thank you @Reeza . I will work on this. Will let you know if I encounter issues or need any updates.

Re: Combine different Excel (xlsx) files into one file with different sheets Through SAS CMD/VB scr

SASuserlot — Mon, 28 Nov 2022 22:44:35 GMT

Hi @Reeza , I trying to use it, But I am struggling how to use the VBA code in SAS. Can you please provide some startup code to start on it.