topic Re: Output into specific cells in an existing excel spreadsheet in SAS Procedures

Output into specific cells in an existing excel spreadsheet

JacobH — Tue, 25 Nov 2014 14:43:45 GMT

I need to output data into specific cells in a pre-existing excel spreadsheet. The only method I know of that can achieve this is by defining the excelfile through a libname statement, but since I use a 64 bit version of SAS (EG 6.4) and 32 bit version of MS-office (2007) using the libname statement results in the error statement: "ERROR: Connect: Class not registered". So... does anyone know of another method to achieve this (or alternatively a way to use the libname statement across different bit versions...)?

the code that I tried to run is: libname xls excel 'S:\1 Alle\JHH\Diverse\Legemappe\template.xls';

Any help will be greatly appreciated

Bedste regards,

Jacob Hornnes

Re: Output into specific cells in an existing excel spreadsheet

RW9 — Tue, 25 Nov 2014 14:49:35 GMT

Am afraid not. The fact that you are using .xsl indicates the file is in proprietary binary format, hence not even easily readable by other applications.

I always recommend people not to use Excel for <insert task> as its not for that.

I suppose one option you have is looking at this from the other end. I.e. export your data from SAS to CSV or Excel, just basic export. Then with your other Excel file, write a small bit of VBA code which opens the exported data, and copies the data to the specific cell.

Other than that I am afraid your hammering a screw in with a matchstick.

Re: Output into specific cells in an existing excel spreadsheet

art297 — Tue, 25 Nov 2014 15:23:46 GMT

Jacob,

Here is one way that might provide the solution you're seeking:

A Poor/Rich SAS Users Proc Export - sasCommunity

However, I noticed that your workbook is on an S drive, thus I presume that you're running a server version of SAS. I've updated the above mentioned macro to be able to run in server environments, but haven't yet posted the new code.

Let me know if the macro works for you or, if it doesn't, let me know and I'll finalize and post the updated code.

Art

Re: Output into specific cells in an existing excel spreadsheet

art297 — Tue, 25 Nov 2014 16:44:53 GMT

p.s. In the event that you decide to try the macro I suggested in my last post, I just updated the documentation and SAS code that you will find at A Poor/Rich SAS Users Proc Export - sasCommunity. The macro now includes a parameter that will allow it to run in server environments where one's local clipboard can't be accessed.

Re: Output into specific cells in an existing excel spreadsheet

JacobH — Wed, 26 Nov 2014 13:34:25 GMT

Dear Arthur,

you sir, are nothing short of a hero! When I run the program, however, I get the following error: ERROR: Undetermined I/O failure. And then the program crashes and the data is not added to the spreadsheet. I have tried to figure out what is wrong, but so far without any succes. Is it a problem that you are familiar with in regards to the macro?

I have inserted the log as well as my macro call below.

Bedste regards, and a huge thank you!

Jacob Hornnes

%exportxl(data=a, outfile=C:\tjek.xlsx, type=M, range=C15);

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: There were 1 observations read from the data set WORK.A.

NOTE: The data set WORK.T_E_M_P has 1 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

NOTE: There were 1 observations read from the data set WORK.T_E_M_P.

NOTE: The data set WORK.T_E_M_P has 1 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: The file CODE2INC is:

Filename=C:\Users\cj1g\AppData\Local\Temp\SEG3760\SAS Temporary Files\_TD2012_CNU1010ZZ6_\#LN00057,

RECFM=V,LRECL=32767,File Size (bytes)=0,

Last Modified=26. november 2014 14:26:26,

Create Time=26. november 2014 14:26:26

NOTE: 6 records were written to the file CODE2INC.

The minimum record length was 15.

The maximum record length was 56.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

3 The SAS System 10:02 Wednesday, November 26, 2014

ERROR: Undetermined I/O failure.

NOTE: There were 1 observations read from the data set WORK.A.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

Re: Output into specific cells in an existing excel spreadsheet

Reeza — Wed, 26 Nov 2014 13:45:39 GMT

You can install or check if you have installed PC File Server. This allows you to connect with the 32 bit version of excel. The code changes as either:

Why is the file .xls if it's Office 2007, shouldn't it be xlxx?

libname xls excelcs 'S:\1 Alle\JHH\Diverse\Legemappe\template.xls';

libname xls pcfiles path= 'S:\1 Alle\JHH\Diverse\Legemappe\template.xls';

SAS Support Downloads Browse

Re: Output into specific cells in an existing excel spreadsheet

art297 — Wed, 26 Nov 2014 14:15:02 GMT

Jacob,

It could be any one of a number of reasons. However, given that your output file is C:\tjek.xlsx and that you included the type=M parameter, my first guess is that the file doesn't exist. I know that on my computer I can't save files directly to the c drive, only to directories under c. Did C:\tjek.xlsx exist before you ran the macro?

And, out of curiosity, is SAS on your computer, or are you running a server version. And, if it is the latter, does it know what your c: drive is?

From your original description, though, I'd think that you'd want to use the type=N, template and templatesheet parameters. The macro lets you specify an Excel template when creating or modifying a workbook or worksheet.

Art

Re: Output into specific cells in an existing excel spreadsheet

JacobH — Wed, 26 Nov 2014 14:56:55 GMT

Dear Arthur,

I had created the file beforehand, no problem, and previous to that I tried with an other location. SAS is installed on the computer itself so it cannot be a server issue. I created a template and tried again with the following code as you suggested and I got the same error message.

%exportxl(data=a, outfile=S:\1 Alle\JHH\Diverse\ny_mappe.xlsx, type=N, range=C5,template=C:\Users\cj1g\AppData\Roaming\Microsoft\Skabeloner\template.xltx,

templatesheet=template);

If you have any idea what could be wrong I will greatly appreciate any help, in any case thanks so much for your help already!

Bedste regards,

Jacob

Re: Output into specific cells in an existing excel spreadsheet

art297 — Wed, 26 Nov 2014 15:03:58 GMT

Jacob,

So we don't use up too much of the forum's bandwidth, write me off line at art@analystfinder.com

Please attach your log and provide as much info as you can. I have a couple of hours to spare this morning and would like to find out why you aren't able to run the macro.

Art

Re: Output into specific cells in an existing excel spreadsheet

JacobH — Thu, 27 Nov 2014 09:29:20 GMT

I tried running it in Base SAS (9.4) and that solved the issue with the Error message. Then my colleague pointed out that I had not specified the sheet in the templatesheet macro variabel (doh!), and now it works like a charm. Thank you so much for your patience as well as a great macro that I plan on using a lot!

Best regards,

Jacob Hornnes

Re: Output into specific cells in an existing excel spreadsheet

Ksharp — Thu, 27 Nov 2014 13:07:33 GMT

Arthur.T,

Could I insert something into a sepecial place of RTF(i.e. Microsoft Word) ?

Re: Output into specific cells in an existing excel spreadsheet

art297 — Thu, 27 Nov 2014 14:24:13 GMT

: Not the way the macro is currently written. You could, of course, use the macro as a template for creating another macro that opened Word rather than Excel, and Word commands and tags rather than Excel cells and actions.

Re: Output into specific cells in an existing excel spreadsheet

RW9 — Thu, 27 Nov 2014 14:58:28 GMT

Isn't it slightly easier for RTF? For Excel files in the OP, he had an existing ZIP (XLSX) which needed to have a value updated. The macro opens Excel and uses the functions in there - please correct me if I misunderstand Arthur. For RTF, this is just a plain text file with some additional tags so you can directly manipulate the file in any way you like, so long as you know RTF syntax. Point being is RTF is before Word so why use Word to edit it, whereas the Excel sheet is already in Excel format (not a flat tagset output), so is harder (but not impossible if you know the Open Office format) to manipulate without going through Excel frontend.

Now if we just had a ZIP file reader in SAS, we could go in an directly modify Office docs...

Re: Output into specific cells in an existing excel spreadsheet

art297 — Thu, 27 Nov 2014 15:24:19 GMT

: Probably, but I've never worked with RTF files or open office. I was only stating that the macro isn't currently designed to do that, but could probably be modified to accomplish such tasks.