BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thogerar1
Calcite | Level 5

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/highlig...

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
thogerar1
Calcite | Level 5

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

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

thogerar1
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4831 views
  • 0 likes
  • 2 in conversation