as literal data access

hi there,

say i have the followng lines in a text file

xlNormal=Microsoft Excel Workbook (*.xls)
xlCSV=CSV Comma Delimited (*.csv)
xlText=Text Tab Delmited (*.txt)

if I want to use "xlCSV" as a variable for an excel object, is there a way to do it?

ie. once i read in the data, xlCSV is assigned to a variable theFormat

then run :

Excel.ActiveWorkbook.SaveAs csvFile, FileFormat:=theFormat

so :  theFormat->xlCSV->6

can this be done or do i have to actually enter the integer value. xlCSV is a component of the excel object libary.

I would rather use the excel libary components that hard code the ints. ie.

6=CSV Comma Delimited (*.csv)      - (bad)

rather than

xlCSV=CSV Comma Delimited (*.csv)      - (good)

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

select case TheFormat
case xlNormal:
    Excel.ActiveWorkbook.SaveAs csvFile, FileFormat:=xlNormal
case xlNormalxlCSV:
    Excel.ActiveWorkbook.SaveAs csvFile, FileFormat:=xlNormalxlCSV
case xlText:
    Excel.ActiveWorkbook.SaveAs csvFile, FileFormat:=xlText
end select
mamboneeAuthor Commented:
yeh, but there are 32 different file types (3 i gave were just an example) and i was hoping there was a cleaner way other than a 32 level case statement..
OK, if you have access to mofifying the textfile, then ....

01:xlNormal=Microsoft Excel Workbook (*.xls)
02:xlCSV=CSV Comma Delimited (*.csv)
03:xlText=Text Tab Delmited (*.txt)

use the above method and parse the values to the get the constant (as you have mensioned in an earlier comment) and use that constant value intead.  Text file is still clear and those numbers in the front, people can ignore.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

mamboneeAuthor Commented:
yeh, i guess thats what i will probable end up having to do, but it would still be nice to know if you can access a variable inside a variable as literal

ie theFormat->xlCSV

access 6 from theFormat..    (easy in c++ but no idea how to do it in VB.)
I think the same question was asked before by someone else and unfortunately the answer was to use a select case statment.  Declare an array of types....


Private Type XLFormat
      Name as string
      Value as long
      Desc as string
End sub

Private XlFormats(30) as XLFormat

then you could use...

XLFormat(i).Value to pass in to the save function
mamboneeAuthor Commented:
still leaves me with the original question.. it is possible to access a variable in a variable...  In C++ i can easily use  pointers and do it as many times as i want, so it stands to reason that vb might do the same....??

theFormat - references -> xlCSV - references  -> 6

Can VB access the 6 from theFormat??
"variable in a variable"

in terms of C, are you talking about the struct? If you have a variable called X = "y" then  you can not asign X = 2 and get variable y = 2.

dim y as long
dim x as string

x = "y"
x = 2 ' that does not set the variable y to 2. and I am not sure how else this could be done with out using a select statment.

that is why I said you could use a type declaration, (similar to struct in C/C++) to get all the xlFormats and their values.  I think I am not understanding your question correctly.  So I leave this question for other experts to answer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
could you post the equivalent C/C++ code.  may be that could clear things out. (just an example of what you want to do)
Is xlCSV not just an constant equal to 6?
As far as I can tell...

    xlNormal = -4143 and is a memeber of XlWindowState
    xlCSV = 6 and is a member of of XlFileFormat and
    xlText = -4158 and is a member XlPivotFieldDataType

...but I think you just want XlFileFormat constants so maybe...

    Dim TheFormat As XlFileFormat what you want... maybe?
Or are you saying you want to be able to determine 6 from the string value "xlCSV"?
MrRobin, from my understanding of this question he want to do is "6 from the string value "xlCSV"? "
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.