Desktop productivity for business analysts and programmers

Ways to check if an excel sheet is open when sas is importing excel files

Reply
Occasional Contributor
Posts: 11

Ways to check if an excel sheet is open when sas is importing excel files

Hi,

 

Is there a way to find if an excel sheet which is to be imported into sas is kept open while running the importing code in sas? If so , could you kindly let me know how is it done.(I don't want to look into the log) I need to identify if the excel is open or closed in SAS which its being imported. My case,I need sas to create a new variable "Check" beside the excel name (Which is to be imported) in an output excel sheet and place the variable values as "Open" if the excel is open. and "Close" as if the excel  is closed while importing.

I am using SASEG 7.1

 

Help is appreciated

 

Thanks,

Thanu.

Super User
Super User
Posts: 7,720

Re: Ways to check if an excel sheet is open when sas is importing excel files

[ Edited ]

Sounds like you have a real mess of a process there.  Why do you need to be importing/exporting Excel files like?  As for your question, depends on what you are doing, if you libname to the file, then the file is locked until the libname is removed.  If it is a proc import then it should only take a short amount of time, but I suspect it will be locked during that time, its never really a question that comes up.  Personally I avoid using Excel for anything as much as possible, however in some rare occurences it is not possible to avoid - in these scenarios minimise the use of either SAS or Excel, i.e. if your doing a lot of work in Excel, then use VBA, drop SAS, if most is in SAS, then just import the data and forget Excel.  

 

Just to add, when Excel opens a file it creates a hidden file which tells it whether it is locked or not.  If you open folder options and switch Show hidden files and folders on, you will see them, called ~$<filename>.  Not sure if this is true of the import function however.

Super User
Posts: 9,867

Re: Ways to check if an excel sheet is open when sas is importing excel files

filename() function might give you some help.
Super User
Posts: 3,233

Re: Ways to check if an excel sheet is open when sas is importing excel files

What you are trying to do probably wont work. While your SAS job is reading the Excel file it will be locked and unable to be updated until the import has finished. You can't simultaneously read an Excel workbook and update another sheet in the same workbook either with the same SAS process (SAS processes are sequential - one must follow the other) or a different SAS process.

Ask a Question
Discussion stats
  • 3 replies
  • 339 views
  • 0 likes
  • 4 in conversation