Transferring sheets from one excel file to another

[SP_SAS]

I have two .xlsx files that both have data and images. I want to combine the sheets of both the excel files into one. 

I did a simple thing like

libname a xlsx "myfile1.xlsx";  (has two sheets sheeta1 sheeta2)

libname b xlsx "myfile2.xlsx"; (has two sheets sheetb1 sheetb2)

data a.sheetb1;

set b.sheetb1;

run;

data a.sheetb2;

set b.sheetb2;

run;

This copies the data fine but does not copy the images. the final file myfile1.xlsx has 4 sheets.

How can I keep the images also when I do this kind of copy.

If SAS can't do it. Can Python do it?

Thanks

[Patrick]

If that's a one-off task then I'd do this manually as it's really simple and quick using Excel.

I don't believe there is a straightforward way using SAS other than calling Windows/Excel to do the job for you (which means the process needs to execute in an environment where Excel is installed). 

It looks like the Python Pandas package would allow you to do such a 1:1 copy of a sheet. Not sure though if this still requires Excel installed on the same machine and Pandas is just calling these Excel components internally.

https://stackoverflow.com/questions/44593705/how-to-copy-over-an-excel-sheet-to-another-workbook-in-... 

import pandas as pd

#change xxx with the sheet name that includes the data
data = pd.read_excel(sourcefile, sheet_name="xxx")

#save it to the 'new_tab' in destfile
data.to_excel(destfile, sheet_name='new_tab')

[SP_SAS]

This will not copy the image files though..

[Patrick]

@SP_SAS 

OK, then it appears the same answer I've given for SAS also applies for Python unless someone wrote a package which does the copying the way you need it - but that's then likely the same like finding somewhere an already written SAS macro which does such a copy. I guess both would need to call Windows/MS Office components.

[Reeza]

Then use VBS and call that VBS from Python or SAS.

[SASKiwi]

Behind the scenes SAS is importing the data from one sheet in your source workbook then writing that data out to a sheet in your target workbook. Anything that can't be stored in a SAS dataset like images wont be included.