Pivot Table VBA in SAS

[deleted_user]

I have used the code in the link below to create a pivot table, which worked great. I was hoping to adapt the code to also create a calculated field from SAS code.

http://www2.sas.com/proceedings/sugi31/035-31.pdf

1. I would like to understand what these numbers refer to
-4157, -4112, and -4106

2.Create Calculated Field per "On the pivot table toolbar, choose PivotTable > Formulas > Calculated Field"

[Cynthia_sas]

Hi:
The author of that paper provided his email address at the end of the paper (on page 4). You might want to contact him and ask what those numbers were used for.

cynthia

Also, for future reference, if you use < or > in your postings, it can sometimes cause your posting to appear truncated because the forum posting software uses < and > as HTML tags. This post
http://support.sas.com/forums/thread.jspa?messageID=27609毙
describes how you have to post < or > symbols in order to convey the correct character and avoid truncation.

[MattF]

Those numbers of several of thousands Excel "magic" contstants:

From Excel, (alt-F11) Macro Editor, (F2) Object Browser,

Const xlTextQualifierNone = -4142 (&HFFFFEFD2)
Const xlSum = -4157 (&HFFFFEFC3)
Const xlCount = -4112 (&HFFFFEFF0)
Const xlAverage = -4106 (&HFFFFEFF6)

In this example they are passed as options to VB functions, e.g.

XL.Workbooks.OpenText
Sub OpenText(Filename As String, [Origin], [StartRow], [DataType], [TextQualifier

As XlTextQualifier = xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab],

[Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [TextVisualLayout], [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers], [Local])

Best,
-Matt