topic Re: Get the sheet names list from Excel with name too long (>30 char) in SAS Programming

Get the sheet names list from Excel with name too long (>30 char)

thogerar1 — Mon, 31 Jul 2017 10:40:22 GMT

Hi,

I'm wondering if it is possible to get the list of sheet names from an excel.

It's possible here with valide sheet name :

https://communities.sas.com/t5/Base-SAS-Programming/Query-excel-to-find-sheet-name/m-p/45477/highlight/true#M9403

I tested it, it worked.

But my sheet names have more than 30 characters.

The sheet names will change every week.

The excel can contains 20 or 40 sheets per week.

Do you know if it's possible to get the list from the excel ?

If I import the excel, only valide name or shown.

I can read the long name sheet like this :

PROC SQL;
CONNECT TO EXCEL (PATH='H:\desktop\myexcel.xls');
SELECT * FROM CONNECTION TO EXCEL
(SELECT * FROM `loulou.xarpiegewaar@vnpcccparizzz`
DISCONNECT FROM EXCEL;
QUIT;

But I cannot get the entire list of sheets somewhere like in a macro variable or a data set.

even from SASHELP.VSTABVW or sashelp.vtable

Thanks by advance,

Regards

Tom

Re: Get the sheet names list from Excel with name too long (>30 char)

RW9 — Mon, 31 Jul 2017 11:13:33 GMT

LIbname excel should create a libname which you can look at for sheet names (i.e. from the sashelp vtable view). I would question however, how you get sheet names with more than 31 characters as its not possible through the UI of Excel. Also, if the file keeps changing, diff sheets etc. how you aim to handle using the file anyways.

Re: Get the sheet names list from Excel with name too long (>30 char)

thogerar1 — Mon, 31 Jul 2017 12:00:21 GMT

Hi,

It's possible to have sheetnames > 32 chars.

See this error message.

http://support.sas.com/kb/45/801.html

I found a solution,

I used this VBA code to reduce sheet names by 2 letters :

Sub removeLetters()
Dim WS As Worksheet
For Each WS In Sheets
   WS.Name = Left(WS.Name, Len(WS.Name) - 2)
Next WS
End Sub

Then the import completly worked with all the sheets appearing in the library.

Regards,

Tom

Re: Get the sheet names list from Excel with name too long (>30 char)

RW9 — Mon, 31 Jul 2017 12:04:46 GMT

Ah yes adding the $ does make it 32. if you are happy with using VBA, then you could simplfy your process by using VBA to create a CSV file of all the data, do loop over sheets in workbook, then for each dump out the data to a text file separated by commas, and have another column for sheet name. I.e. you then get one CSV file with all the data and sheetname as a column. Avoids fidlly programming in SAS and simplfies your import code to only be one datastep read CSV.