Choose() alternative(s)? 2

[bkdobs]

csv files are read by access in an import function what escapes me is how to do this in code ... Choose() is close but not exactly working the way I expected.

given; strList = "a,b,c"

? NumElements(strList) ' number of elements in list

for inI = 1 to NumElements(strList)

? Element(inI,strList)

next

Choose requires that its element list be separated ...
"a","b","c" ... this doesn't appear to allow for a function call; Choose(inI,strList)

so getting back to the CSV file

while not eof()
strLine = readline
for inI = 1 to numElements(strLine)
' do some code with element(inI,strLine)
next
loop

 

[RoyVidar]

Are you looking for the Split function?

[tt]dim s as string
dim arr() as string
dim l as long
s = "first;second;third;blah"
arr = split(s, ";")
for l = 0 to ubound(arr)
debug.print arr(l)
next l
debug.print join(arr, "|")[/tt]

Roy-Vidar

 

[JoeSmoe55]

I took the very lazy approach by using excel to split it for me. If you choose excel you must include the excel libraries. The split function looks a little easier. If I had it to do over I would probably use split but I didn't know about it until now. Oh well whatever works.
btw - csv files will be read by excel automatically.

regards - Joe

Private Sub importFromXSLFile(FName As String)
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim li As ListItem
Dim l As String, n As Integer, rub As Integer, cub As Integer, z As Integer

Set wb = Excel.Workbooks.Open(FName)
Set ws = wb.Worksheets(1)

rub = ws.UsedRange.Rows.Count
cub = ws.UsedRange.Columns.Count

For r = 1 To rub
Set li = ImportDataView.ListItems.Add(, , r)
For c = 1 To cub - 1
With ImportDataView
li.SubItems(c) = ws.Cells(r, c)
End With
Next c
Next r
End Sub

 

[bkdobs]

I was looking for an answer to my previous posting relating to speed ... by rewording it I received one of the answers I was looking for ... I tried the split function in my speed tests and found the speed of looking up values in Arrays was slower then the if elseif approach

for i = 1 to ubound(arr)
if arr(i) = "blah" then

as opposed to

if arr(1) = queryval then
elseif arr(2) = queryval then

...

I also tested a variation (requires fixed length named variables) aa1,aa2,aa3,aa4 ....

dim x as long
dim arr()
x = (instr(gueryval,listofvariables) / (lengthofvarname + 1)) - 1
MyVariable = arr(x)

My conclusion is that in order to tune the speed of an application it is best to steer clear of VBA's string functions when ever possible

what I believe is needed is the ability to have a global static table (in memory) that can be indexed and used with functions like findfirst or dlookup