topic Re: Importing an Excel file that has not been opened/is in protected view in SAS Programming

Importing an Excel file that has not been opened/is in protected view

sas_user_2023 — Tue, 17 Oct 2023 20:21:22 GMT

I think I may have a rather unique issue and have not yet found a viable solution. I am using this macro to scan a folder for xlsx files, create a list of files in the folder (all of identical format), and then import them. I am interested in importing 6 sheets from each file so I have adjusted the code accordingly. The reason I mention this is because I will be importing thousands of files, each exported manually from on online database and saved to said folder. The issue I am running into using a simple proc import step with sheet statements (tested outside of macro) is "ERROR: Couldn't find sheet in spreadsheet." When commenting out the sheet statement and trying to just import the file (defaulting to importing the first sheet) I get "No worksheets in the .XLSX file Requested Input File Is Invalid ERROR: Import unsuccessful. See SAS Log for details." After diligently checking sheet names, file names, etc., I have discovered that the issue lies in one of two problem: 1) these files are not being opened and saved prior to attempting to import them into SAS which may or may not tie into 2) the files are in protected view and have not been opened and saved to enable editing. Does anyone have any ideas how I could get around this without opening and saving every single file? Again, there are thousands.

Re: Importing an Excel file that has not been opened/is in protected view

Reeza — Tue, 17 Oct 2023 21:16:51 GMT

Can you add the location to your trusted locations in Excel?

https://support.microsoft.com/en-us/office/add-remove-or-change-a-trusted-location-in-microsoft-office-7ee1cdc2-483e-4cbb-bcb3-4e7c67147fb4

Re: Importing an Excel file that has not been opened/is in protected view

sas_user_2023 — Tue, 17 Oct 2023 21:36:21 GMT

Thanks for the suggestion. Unfortunately, that did not work

Re: Importing an Excel file that has not been opened/is in protected view

SASKiwi — Wed, 18 Oct 2023 00:25:52 GMT

Have you tried the EXCEL or XLSX LIBNAME engines? They may behave differently to that macro.

Re: Importing an Excel file that has not been opened/is in protected view

Patrick — Wed, 18 Oct 2023 03:38:53 GMT

I couldn't replicate the issue you describe in my SAS9.4M7 Windows workstation environment.

I've downloaded from the Reserve Bank of Australia Excel https://www.rba.gov.au/statistics/tables/xls/a03-2hist.xlsx?v=2023-10-18-13-54-25

After the download I can execute both of below code versions without any issues.

/* version 1 */
%let workbook=a03-2hist.xlsx;
proc import 
  file="c:\temp\&workbook" dbms=excel replace out=work.test;
  sheet='rba securities lending';
run;

/* version 2 */
%let workbook=a03-2hist.xlsx;
libname test "c:\temp\&workbook" access=readonly;
data _null_;
  set test.'rba securities lending$'n;
run;
libname test clear;

Even after execution of above code when I open the downloaded file via Excel I still get first:

Also interesting is that if I download the file via a REST call then I get a file that opens without Protected View.

%let workbook=a03-2hist.xlsx;
filename out "c:\temp\&workbook";
proc http
  url="https://www.rba.gov.au/statistics/tables/xls/&workbook?v=2023-10-18-13-54-25"
  method="get" 
  out=out
  ;
run;
filename out clear;

If things aren't working this way for you then you might need to talk to a Windows admin at your site.

Re: Importing an Excel file that has not been opened/is in protected view

sas_user_2023 — Mon, 23 Oct 2023 15:45:02 GMT

Solution: using Microsoft Power Automate instead to automate opening and saving the downloaded files in the folder. This was the easiest and quickest solution I could piece together to circumvent the issue.