asked on

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)

thanks.....

supunr

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

mambonee

ASKER

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..

supunr

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.

mambonee

ASKER

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
xlCSV->6

access 6 from theFormat.. (easy in c++ but no idea how to do it in VB.)

supunr

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....

e.g

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

mambonee

ASKER

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??

ASKER CERTIFIED SOLUTION

supunr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

supunr

could you post the equivalent C/C++ code. may be that could clear things out. (just an example of what you want to do)

MrRobin

Is xlCSV not just an constant equal to 6?

MrRobin

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

...is what you want... maybe?

MrRobin

Or are you saying you want to be able to determine 6 from the string value "xlCSV"?

supunr

MrRobin, from my understanding of this question he want to do is "6 from the string value "xlCSV"? "