import multi tab excel file & export with tabs in same order

[kalbo]

I have a task.

Import all tabs of an excel workbook (xlsx) into SAS. Tabs can be called anything and number of tabs is variable.

I then need to read out all data sets to recreate the workbook as before, with tabs in their original order.

I have tried reading in excel files using libname statement with xlsx engine which reads them in ok, but does not enable the sheet order to be obtained. I tried using:

proc sql & dictionary.columns

sashelp.vtable

proc contents

but none of these methods help.

[ballardw]

Sure sounds like a task for an Operating System copy file. Why is SAS involved?

[kalbo]

What we are doing is doing a validation check of data contained in the sheets. This validation output is exported along with the data sheets of the original file so that we can hyperlink the issues detected. Strange, I know.

[Patrick]

Hmmm... if you re-write the data after validation then the data you create is no more really validated.

Would it be possible to just add a new sheet with the validation report to the existing Excel without "touching" the pre-existing data sheets?

What's the OS of your SAS Server? Unix/Linux or Windows?

[Ksharp · Accepted solution]

@Tom  could did it for you .The following code is from Tom which you could know the order of Sheet in a Excel file.

/*******************获得EXCEL中的Sheet名*******************/
filename _wbzip ZIP "c:\temp\file.xlsx" member='xl/workbook.xml';
data sheets ;
  infile _wbzip dsd dlm=' ' recfm=n ;
  sheetnum+1;
  input @'<sheet name=' sheetname :$32. @@;
run;

[kalbo]

Works great! thanks