Convert String Representation of Excel Constant to Actual Value

[jgrob3]

I'm importing a fixed width text file into another workbook using
workbooks.opentext and would like to store all the setup info for this
process in a table within an Excel sheet so the user can change the
setup when they need to.

At the moment, I have columns for Field Headings (text), Field Format
(being the text for xlColumnDataTypes), and start position (integer).

When I read the data in however, the FieldInfo is expecting an integer
Excel constant (eg xlTextFormat or 2), rather than the text of this
(eg "xlTextFormat").

Is there a way to convert from a string representation of an Excel
constant to the constant's actual value?

I thought of using Evaluate (but that's only for cell/range
references) or CInt (doesn't work either). I'm kludging it at the
moment by doing a vlookup and getting the appropriate integer value
that way but surely there's a more elegant solution?

Any ideas?

tia

Jeff

 

[Dave Peterson]

If you're looking for a single value,
you can open the VBE
hit ctrl-g (to see the immediate window)
and type this:
?xltextformat

You can also search the Object Browser:
In the VBE
Hit F2 to show that object browser.
search for xlColumnDatatype

Select each of the option and at the bottom of the screen you'll see the values.

Or you could download Chip Pearson's cross reference:
http://www.cpearson.com/excel/download.htm

Look for XLConst

 

[jgrob3]

If you're looking for a single value,
you can open the VBE
hit ctrl-g (to see the immediate window)
and type this:
?xltextformat

You can also search the Object Browser:
In the VBE
Hit F2 to show that object browser.
search for xlColumnDatatype

Select each of the option and at the bottom of the screen you'll see the values.

Or you could download Chip Pearson's cross reference:http://www.cpearson.com/excel/download.htm

Look for XLConst












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave - I realise I can see the equivalent values in the
immediate window but is there a way within VBA to convert the string
"xlTextFormat" (or indeed any other Excel constant) to its integer
value?

 

[Dave Peterson]

Not by me.

IIRC Chip Pearson did something here:

http://groups.google.co.uk/group/mi...31482618e83?lnk=st&q=&rnum=8#e6a7031482618e83

or
http://snipr.com/1nv3s

And...

Michel Pierron did something in this thread:
http://groups.google.co.uk/group/mi...3ef4c701cba?lnk=st&q=&rnum=1#494dc3ef4c701cba

or
http://snipr.com/1nv3q

Maybe you can look at all the constants, find a match and return the value.

 

[Rick Rothstein \(MVP - VB\)]

Thanks Dave - I realise I can see the equivalent values in the

immediate window but is there a way within VBA to convert the string
"xlTextFormat" (or indeed any other Excel constant) to its integer
value?

Just out of curiosity, what type of code are you writing where you think you
need to be able to do this? If you plan to have your user type this into
your program, that is one thing, but it seems unlikely that a user would
know the symbolic names of constants. On the other hand, within your program
itself, you should be able to use the symbolic name directly... I am having
trouble imagining a code situation where you would need to work with a piece
of text whose characters formed the symbolic name from which you would want
to derive the value of that symbolic name.

Rick

 

[jgrob3]

Just out of curiosity, what type of code are you writing where you think you
need to be able to do this? If you plan to have your user type this into
your program, that is one thing, but it seems unlikely that a user would
know the symbolic names of constants. On the other hand, within your program
itself, you should be able to use the symbolic name directly... I am having
trouble imagining a code situation where you would need to work with a piece
of text whose characters formed the symbolic name from which you would want
to derive the value of that symbolic name.

Rick

Rick,

What I'm doing is described in overview in the very first posting.
Essentially, I was planning to store all the values I need to process
a text file in a range within a worksheet so that the process is
flexible and can be re-used if necessary (see code extract below).

To make life easier and more readable for the person creating the
values for the range, I was going to let them select the the symbolic
names from a data-validated dropdown list since for the general
population, "xlTextFormat" is much more meaningful than "2".

My range has column headings: "FieldName" (text name for the field
that I then insert at the top of the imported text), "Format" (data-
validated text field with values such as "xlTextFormat" etc),
"Length" (field length), "Start Position" (starting position for each
field), and "FormatValue" (which currently contains a VLOOKUP to
another table to manually generate the hard-coded value of
"xlTextFormat" from the "Format" field value ... since otherwise I
can't get it all to work).

So I wanted my code to be able to read the values from the range
including "xlTextFormat", which would be stored as a string/text in
the cell, then convert this to its actual value (ideally) using VBA
but it doesn't seem easy!

Any other ideas?

tia

Jeff


code extract:

'initialise variables
iOrigin = xlWindows
iStartRow = 2
iStartPosCol = Range("tblFileFormatStartPos").Value
iFormatValCol = Range("tblFileFormatFormatValue").Value
iFieldCol = Range("tblFileFormatField").Value
stDataTag = Range("setupMthMvmtDataTag").Value

'build array for field import & field headings
With Range("tblFileFormat")
ReDim vFieldInfo(1 To .Rows.Count)
ReDim vFieldNames(1 To .Rows.Count)
For i = 1 To UBound(vFieldInfo)
If .Cells(i, iFormatValCol).Value = 0 Then
Exit For
End If
vFieldInfo(i) = Array(.Cells(i,
iStartPosCol).Value, .Cells(i, iFormatValCol).Value) '<-- ideally,
this is where we'd convert the text to its value
vFieldNames(i) = .Cells(i, iFieldCol)
Next
End With

Workbooks.OpenText Filename:=vFileToOpen, origin:=iOrigin,
startrow:=iStartRow, DataType:=xlFixedWidth, fieldinfo:=vFieldInfo,
trailingminusnumbers:=True