sas >> TAGSET MSOFFICE2K : XML and Excel

by olivier.leconte » Wed, 25 May 2005 19:15:59 GMT

Hi,

Do you know if Microsoft provides a complete userguide on how you can modify
Excel using XML.
It is partially explained in diferrent SUGI papers regarding TAGSET
MSOOFICE2K or in the paper of Chevell Parker but I want more information
regardin this technology.

Thanks for your help.

Olivier

sas >> TAGSET MSOFFICE2K : XML and Excel

by datametric » Wed, 25 May 2005 20:04:51 GMT


Hi / Salut,

Chevell provides a link at the end of there papers about microsoft XML userguide.

Go and see, it's excellent.

Sthane.


----Message d'origine----

sas >> TAGSET MSOFFICE2K : XML and Excel

by SASL001 » Wed, 25 May 2005 21:29:53 GMT

Olivier,

Have you looked at the MSDN documentation?

For example:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_
ta/html/OfficeExcelXMLMappingScenarios.asp

When you open this link, look on your left side for a load of resources on
XML and Excel.



Thanks,
Alan

Savian
"Bridging SAS and Microsoft Technologies"
-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of Olivier
LECONTE
Sent: Wednesday, May 25, 2005 4:16 AM
To: XXXX@XXXXX.COM
Subject: TAGSET MSOFFICE2K : XML and Excel

Hi,

Do you know if Microsoft provides a complete userguide on how you can modify
Excel using XML.
It is partially explained in diferrent SUGI papers regarding TAGSET
MSOOFICE2K or in the paper of Chevell Parker but I want more information
regardin this technology.

Thanks for your help.

Olivier

Similar Threads

1. DelGobbo's XML Tagset: Unable to assign a FILEREF for the Excel XML file

Hi All,

I'm working with Vincent DelGobbo's XML tagset after reading many good
things about it. When I setup my macro variable SAMPDIR at the top of
CompleteCode.sas, and I run the code in Enterprise Guide, I get an
error that says:

ERROR: Unable to assign a FILEREF for the Excel XML file

I've included the relevant log section below. Please note that the
reference for SAMPDIR is a UNC style network path, and the folder
exists, as the program successfully includes the ExcelXP.sas program
and the LoadXL.sas program.

If anybody has any suggestions for how to resolve this issue, that
would be excellent.

Thanks,
Ben

31         *;
32         *  Include recent version of the ExcelXP ODS tagset.
33         *;
34
35         %inc "&SAMPDIR.ExcelXP.sas";
5606
5607       *;
5608       *  Include the macro (XLXP2SAS) to import Excel XML to SAS
tables.
5609       *;
5610
5611       %inc "&SAMPDIR.LoadXL.sas";
6143
6144       filename myxml "&SAMPDIR.DataToImport.xml";
6145       filename mymap "&SAMPDIR.ExcelXP.map";
6146
6147       %xlxp2sas(	excelfile=FILEREF:myxml,
6148       			mapfile=FILEREF:mymap)

ERROR: Unable to assign a FILEREF for the Excel XML file.

2. Double quotation marks problem with XML, EXCEL XP Tagset

3. excelxp tagset and data type in xml

Hi,

I am pretty new to the excelxp taget world, but am finding my way.  My
current prob is I am using a proc print, the member_var is a
character, but only has numerics.  So when ods outputs it to xlm the
data type is numeric instead of string.  I need it to be string so if
someone saves the excel document I dont lose the leading zeros of the
member_var.  How can I change the data type of a variable when it
writes it to xml.  My code is below.

ods tagsets.ExcelXP path="xxx"
                    file="test.xls");
proc print data = test_dset noobs;
var member_var update;
run;
ods tagsets.ExcelXP close;

4. DelGobbo's XML Tagset: Unable to assign a FILEREF for the

5. Need good book for Sas to excel using msoffice2k tagsets

Hi Everyone,
I've been creating excel reports from sas for a while now, and still
struggle to do all the unique 'one time' things that each report invariably
requires.  I do it sporadically enough where I find myself re-learning it
all each time.  Does anybody know of a good book that really covers the
full depth of creating excel spreadsheets and all the tagset modifications
and such that are done?  At this point somebody has had to have written a
book on it!  My current reference section for this involves a whole pile of
printouts from various papers, websites, etc.  Someone somewhere has had to
put it all together.  Thanks!
--Bob

6. MSOffice2k to XP - Microsoft Office

7. Excel Spreadsheet XML -- How to find Excel/XML syntax quickly

Hi Perry,

I don't want you to have to take a 'long'
time, so I'll tell you my secret for finding
these in approx 30 seconds...

For example, your first question was that
the freeze panes wasn't working. Since XML
is merely text files then your favorite
editor works just fine, PLUS anything that
will compare two text files. I use UltraEdit
which has lots of features including an nice
compare feature, or any DIFF or line compare
utility will do.

I open Excel, type in 'Fred" in A1, type in
'Barney' in A2, and make sure the focus is
left in A2(where I am going to eventually
have the freeze pane axis). Here is trick
number 1, SAVE the spreadsheet as XML to
disk at this point *without* the freeze panes.
Then trick number 2 is to add the freeze panes
and save to disk as XML to another filename.
Your compare utility will show you the exact
lines(syntax) of *that* feature, as a difference
between the two text files. Sometimes a larger
sample requires Wilma, Betty, Pebbles, Dino and
BammBamm. Occasionally Mr Slate.

So that is the quick way to find out how to
add any new Excel feature or cosmetic. Just
compare the XML text files of before and after
adding just that one change only!

In your case, your syntax for freeze panes was
perfectly fine. So I did a copy and paste of your
4 or 5 option groups one at a time into my sample XML
text file and then opened the workbook to see if the
syntax worked okay. That is how I found the <:Header.

Since I typically just use Excel to make my
original and then wrap PUT statements around it,
and then use Excel to make single changes and
locate/copy/paste with PUT statements just the
change lines, I have the luxury of rarely having
extraneous typos. Since I many times only take
a few minutes away from work to do SAS-L responses,
I did you wrong the first go-'round by not pondering
that your <WorksheetOptions header may have been
hand-typed, cuz I always just copy and paste it.
Therefore I had tested your original set of options
in a valid header string and they all worked. It
wasn't until you mentioned later that things still
were not working that I copy and pasted ALL of your
code into my small sample that with the compare
utility again immediately spotted the header typo.

With those easy/cheap tools(Excel&UltraEdit$39) plus
the approach above, I found all three of your answers
accumulatively in a total probably less than 90 seconds.



So, Happy Fast Resolving,
Mark



-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of
 XXXX@XXXXX.COM 
Sent: Thursday, August 09, 2007 7:33 AM
To:  XXXX@XXXXX.COM 
Subject: Re: Excel Spreadsheet XML

WE HAVE SUCCESS!!!

Mark,

Thank you SO much. I don't want to think about how long it would have
taken me to find that typo without your help.


On Aug 8, 12:04 pm,  XXXX@XXXXX.COM  ("Terjeson, Mark") wrote:
> Hi Perry,
>
> I think you'll have good luck with your entire
> macro if you change one hyphen to a colon.
>
> Change
>     put '    <WorksheetOptions
> xmlns="urn-schemas-microsoft-com:office:excel">';
> To
>     put '    <WorksheetOptions
> xmlns="urn:schemas-microsoft-com:office:excel">';
>
> See if things don't come alive now!
>
> Let us know how it goes.....
>
> Hope this is helpful.
>
> Mark Terjeson
> Senior Programmer Analyst, IM&R
> Russell Investments
>
> Russell Investments
> Global Leaders in Multi-Manager Investing

8. How can data convert XML to Excel or Excel to XML