How to get all the Excel or Word constants with their values 7

[Mike Gagnon]

For those interested:
"How to get all the Excel or Word constants with their values"
faq184-4248


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[craigsboyd]

Mike,

Very useful tip. I've used this countless times in the past to save me time. I also like the ability to quickly implement an interface by drag-n-drop as well. Using the object browser you can open the list of interfaces and drag any one of them to a prg file. Sure saves time when you want to use EventHandler().

Anyways, thanks for the FAQ and here's a star for your trouble.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[monikai]

Hi !
I use VFP6 and i very regret that i can't try your suggestions. I often fight with 'VBA in VFP'. Sometimes without results, and i must change all program (change way). May be it'll be easier in VFP7 or 8. I'm waiting when my bosses will decide to buy higher verrsion VFP.

Regards from Monika (Warszawa - Poland)
(monikai@yahoo.com)

 

[Mike Gagnon]

monikai

The only thing you cannot do with VFP6.0 is the drag-n-drop into a program, the rest is available to you in a different fashion, since the OLB file contains all the constants. Copy the follwing into a prg and run it.

PUBLIC oform1
oform1=NEWOBJECT("form1")
oform1.SHOW
RETURN
DEFINE CLASS form1 AS FORM

	HEIGHT = 445
	WIDTH = 567
	DOCREATE = .T.
	AUTOCENTER = .T.
	BORDERSTYLE = 1
	CAPTION = ".OLB Constants Extractor"
	MAXBUTTON = .F.
	MINBUTTON = .F.
	NAME = "Form1"

	ADD OBJECT txtolbfile AS TEXTBOX WITH ;
		HEIGHT = 27, ;
		LEFT = 65, ;
		READONLY = .T., ;
		TABINDEX = 2, ;
		TOP = 6, ;
		WIDTH = 458, ;
		NAME = "txtOLBFILE"

	ADD OBJECT label1 AS LABEL WITH ;
		AUTOSIZE = .T., ;
		CAPTION = &quot;.\<OLB File:&quot;, ;
		HEIGHT = 17, ;
		LEFT = 4, ;
		TOP = 11, ;
		WIDTH = 55, ;
		TABINDEX = 1, ;
		NAME = &quot;Label1&quot;

	ADD OBJECT cmdsave AS COMMANDBUTTON WITH ;
		TOP = 411, ;
		LEFT = 394, ;
		HEIGHT = 27, ;
		WIDTH = 84, ;
		CAPTION = &quot;\<Save to .h&quot;, ;
		ENABLED = .F., ;
		TABINDEX = 6, ;
		NAME = &quot;cmdSAVE&quot;

	ADD OBJECT cmdquit AS COMMANDBUTTON WITH ;
		TOP = 411, ;
		LEFT = 480, ;
		HEIGHT = 27, ;
		WIDTH = 84, ;
		CAPTION = &quot;\<Quit&quot;, ;
		TABINDEX = 7, ;
		NAME = &quot;cmdQUIT&quot;

	ADD OBJECT edtconstants AS EDITBOX WITH ;
		HEIGHT = 347, ;
		LEFT = 6, ;
		READONLY = .T., ;
		TABINDEX = 4, ;
		TOP = 52, ;
		WIDTH = 558, ;
		NAME = &quot;edtConstants&quot;

	ADD OBJECT cmdgetfile AS COMMANDBUTTON WITH ;
		TOP = 6, ;
		LEFT = 533, ;
		HEIGHT = 27, ;
		WIDTH = 26, ;
		CAPTION = &quot;...&quot;, ;
		TABINDEX = 3, ;
		NAME = &quot;cmdGETFILE&quot;

	ADD OBJECT cmdextract AS COMMANDBUTTON WITH ;
		TOP = 411, ;
		LEFT = 280, ;
		HEIGHT = 27, ;
		WIDTH = 110, ;
		CAPTION = &quot;\<Extract Constants&quot;, ;
		ENABLED = .F., ;
		TABINDEX = 5, ;
		NAME = &quot;cmdEXTRACT&quot;


	PROCEDURE cmdsave.CLICK
		STRTOFILE(THISFORM.edtconstants.VALUE,PUTFILE([Header File], ;
			JUSTSTEM(THISFORM.txtolbfile.VALUE) + [.h],[.h]))
	ENDPROC


	PROCEDURE cmdquit.CLICK
		THISFORM.RELEASE
	ENDPROC


	PROCEDURE cmdgetfile.CLICK
		LOCAL lcOLBFile

		lcOLBFile = GETFILE([OLB],[OLB File],[Open])
		IF EMPTY(lcOLBFile)
			RETURN .F.
		ENDIF
		IF UPPER(RIGHT(lcOLBFile,3)) # [OLB]
			MESSAGEBOX([Invalid File],0,[])
			RETURN .F.
		ENDIF
		THISFORM.txtolbfile.VALUE = lcOLBFile
		THISFORM.cmdextract.ENABLED= .T.
	ENDPROC
	PROCEDURE cmdextract.CLICK
		WAIT WINDOW [Processing...] NOCLEAR NOWAIT
		LOCAL oTLB_INFO, oConstants, lcConstantsStr, Obj, member
		#DEFINE CRLF CHR(13) + CHR(10)
		oTLB_INFO = CREATEOBJECT([tli.typelibinfo])
		oTLB_INFO.ContainingFile = (THISFORM.txtolbfile.VALUE)
		oConstants = oTLB_INFO.Constants
		lcConstantsStr = []
		FOR EACH Obj IN oTLB_INFO.Constants
                        lcConstantsStr = lcConstantsStr + CRLF + &quot;* &quot; + Obj.Name + CRLF   
                        FOR EACH member IN Obj.Members
                            lcConstantsStr = lcConstantsStr + [#DEFINE ] + ;
                            member.NAME + [ ] + ;
                            TRANSFORM(member.VALUE) + CRLF
                        NEXT member
		NEXT Obj

		THISFORM.edtconstants.VALUE=lcConstantsStr
		THISFORM.cmdsave.ENABLED= .T.
		WAIT CLEAR
		WAIT WINDOW [Complete!] TIMEOUT 2
	ENDPROC
ENDDEFINE

_{Code provided by Microsoft.}

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[JanBucek]

For Monika:
Visit

http://www.west-wind.com/Webtools.asp,

download GetConstants and try it. It works excellent.
HTH

 

[monikai]

Mike, you are great !

Regards from Monika (Warszawa - Poland)
(monikai@yahoo.com)

 

[TrueCode]

Stars for Mike. I always had to be going to and throw in the classbrowser and that has been very painful. You have even given us an Include File Output.

Stars for you like sand.

"I have sought your assistance on this matter because I have exhausted all the help that I can find. You are free to direct me to other source of help"

 

[ccostabr]

Mgagnon,

I really enjoy very much the way you manage VFP. You're a "landmark" here.
Please, do not laugh my question, but I'm really lost in this subject (constants).

- I used your code above, enjoyed very much, saved some *.H, but what are the constants used for ? What can I do whith them ?

Best regards
CCostaBR

 

[craigsboyd]

ccostabr,

Using compile-time constants in your program will reduce memory consumption, increase performance, and simplify programs. The last point here is a good one... it really makes your code easier to write and read. Instead of using a value of an argument when calling a function or setting a property you can use your constant which will usually have a good name that symbolizes in some small part what it does.

Take the fact I wrote

Microsoft's Mappoint - Create a Datamap
faq184-3886

...see how the constants in that FAQ make the code at the bottom more readable? And they certainly made it easier for me when I was coding the example, I didn't have to remember the numbers from memory, I could just look at the #DEFINE statements for the one I wanted.

In order to use a header file (.h) you could just put all of those #DEFINE statements into a file "CONST.H" and then at the top of the code in my FAQ instead of listing out all the #DEFINE statements you could put the line:

#INCLUDE CONST.H

...this tells the compiler to act as though that header file's contents were typed in right there. Having your #DEFINE statements in a header file greatly increases organization and your ability to reuse them elsewhere.



Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[torturedmind]

well said slighthaze. a star to both of you (mike n slighthaze)

kilroy

 

[ccostabr]

slighthaze,

Thanks a lot.
I really could understand. I'll improve my develpment.

Best Regards from Brazilians VFP Developers,
CCostaBR

 

[Mike Gagnon]

- I used your code above, enjoyed very much, saved some *.H, but what are the constants used for ? What can I do whith them ?

SlghtHaze has answer quite well this question, but here is another example. Lets say you want to automate Excel to create a chart based on a series of numbers that you inputed in the Excel sheet. The easiest way to achieve this might be to run Excel an create a macro, take to code from the VB editor and modify it in to work in VFP. So for the chart part here is the VB code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/4/2003 by user002
'

'
    Range("A1:B6").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B6"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

You will notice that all the values ARE constants the Excel uses, and if you don't know that actual numeric values of these, it might be a long time before you can translate this into a useable VFP function, BUT with the access to the numeric values with the suggestion I made you can easely translate you code into VFP. And if you want to change your chart style, you don't have to go through 100 possibilties before discovering the one that suits you. Here is the complete translation.

#DEFINE xlColumnClustered	51	
#DEFINE xlColumnStacked	52	
#DEFINE xlColumnStacked100	53	
#DEFINE xl3DColumnClustered	54	
#DEFINE xl3DColumnStacked	55	
#DEFINE xl3DColumnStacked100	56	
LOCAL oExcel as Excel.application
LOCAL oWorkbook,oSheet
oExcel = CREATEOBJECT("Excel.application")
oWorkbook= oExcel.Workbooks.Add()
oSheet = oWorkbook.activesheet
WITH oSheet
 .Range("A1").Select
    .Range("A1").FormulaR1C1 = "1"
    .Range("A2").Select
    .Range("A2").FormulaR1C1 = "2"
    .Range("A3").Select
    .Range("A3").FormulaR1C1 = "3"
    .Range("A4").Select
    .Range("A4").FormulaR1C1 = "4"
    .Range("A5").Select
    .Range("A5").FormulaR1C1 = "5"
    .Range("A6").Select
    .Range("A6").FormulaR1C1 = "6"
    .Range("B1").Select
    .Range("B1").FormulaR1C1 = "10"
    .Range("B2").Select
    .Range("B2").FormulaR1C1 = "11"
    .Range("B3").Select
    .Range("B3").FormulaR1C1 = "50"
    .Range("B4").Select
    .Range("B4").FormulaR1C1 = "60"
    .Range("B5").Select
    .Range("B5").FormulaR1C1 = "70"
    .Range("B6").Select
    .Range("B6").FormulaR1C1 = "90"
    .Range("A1:B6").Select
ENDWITH
WITH oWorkbook
    .Charts.Add
    .ActiveChart.ChartType = xlColumnClustered
    .ActiveChart.SetSourceData(oSheet.Range("A1:B6"))
    .ActiveChart.HasTitle = .f.
ENDWITH

oExcel.Visible =.t.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[nl]

Mike,
I am using VFP6.0 sp5 but cannot run your program.

Program stop on the line:

oTLB_INFO = CREATEOBJECT([tli.typelibinfo])

Am I miss something, it seems VFP6 don't
know the object "tli.typelibinfo"

Norman

 

[Mike Gagnon]

nl

Here is a note from Microsoft:

Tlbinf32.dll is a dynamic-link library (.dll) file ships with Microsoft Visual Studio 98 Service Pack 4.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ccostabr]

mgagnon,

Tks a lot.

CcostaBR

 

[FoxEgg]

Thanks for the education and the FAQ..

Star stuff...

John Fox