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 :
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
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
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.
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: