Split function to create mixed type array 1

[JesOakley]

Hi . I'm trying to use vba to open a .csv file out of excel, and use split to load up the various elements into an array. The elements can be text, numbers and dates. I've read on the net that i can declare my array as an object, and then convert when i want to do something with them. My problem is that I get a type mismatch error on the 'split' line. I've checked the help, msdn help and various web pages, but can't see what i'm doing wrong. Any help would be gratefully received. I'm using Access 2007. The essence of the code is:

Dim intOutFileCount As Integer
Dim intInFileNo As Integer, intOutFileNo As Integer
Dim strInfile As String, strInRec As String
Dim InRecord() As Object

    ' open infile
    intInFileNo = FreeFile
    Open "filename.csv" For Input As #intInFileNo

    ' loop thru infile
    Do While Not EOF(intInFileNo)

        ' load up InRecord
        strInRec = Input(LOF(intInFileNo), intInFileNo)
        InRecord = Split(strInRec, ",", -1) ' type mismatch. Error 13
        .
        . {more code}
        .
        ' write to outfile
        Print #intOutFileNo, CInt(InRecord(7)); CDate(InRecord(8)); CDate(InRecord(9))
    Loop

 

[LarrySteele]

Jes,

Define InRecord as a variant and you should be fine.

HTH,
Larry

 

[HarleyQuinn]

You're trying to create an array of objects, Split doesn't return an object array.

You can have Split return into a string array or into a variant that will hold the string array. Then do your conversions when you need them (as you've doen later on in your code).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[JesOakley]

Thanks for the prompt response! Variant didn't seem to work, but string did, so I'll have to do it that way I guess. Cheers!

 

[MichaelRed]

generally, reading text files on a line for line basis is quite slow. this becomes the dominant process in parseinglarge amounts of data.

The routine below will return the entire file in a string array which may be processed to allocate the fields propsely. If you use (advanced) search, with the routine name, there are numerous threads, some of which include a companion procedure showing how the parseiing is done.

Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim MyFil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    MyFil = FreeFile

    Open FilIn For Binary As #MyFil

    MyTxt = String(LOF(MyFil), " ")
    Get #MyFil, 1, MyTxt

    Close #MyFil

    basGrabFile = MyTxt

End Function

This will "Grab" the entire. Not just the file name / path. In fact you must provide that info to the function. Further analysis of the file for your purposes is up to you, e.g. finding / knowing the file type; breaking it into seperate 'objects' via delimiters ... )

To see some simple examples, search these (Tek-Tips) fora for the name of the procesure. There are some examples showing how to process text files (CSV?) into individual records and fields. Dealing with other file types is a seperate iessue.



MichaelRed