Import Text Via VBA

[ethoemmes]

I am trying to import a text file via VBA, I have to import via vba as
the file i am importing is not consistently formatted and I also need
to perform some manipulation of the data before it is imported. I am
building an array from a text file and then appending it via DAO.
(Example code below)
I have two problems at the moment firstly every field I import could be
empty so I am having to wrap each field in
If strarray(n) <> "" - is there an easier way of doing this?

Also I am getting a subscript out of range error when it tries to
append strarray(10) - does anyone know why this is happening?

Set RS1 = CurrentDb.OpenRecordset("tblTEMPVendors", dbOpenTable)
FP1 = FreeFile
Open Sourcefile For Input As FP1
Do Until EOF(FP1)
Line Input #FP1, strData
strArray = Split(strData, vbTab)
If Not strData = "" Then 'exclude gaps in header
If Len(strArray(0)) = 0 Then 'exclude report title in line 1
If Not strArray(1) = "Vendor" Then 'exclude column heading
RS1.AddNew
If strArray(1) <> "" Then
RS1.Fields(0).Value = strArray(1)
End If
If strArray(2) <> "" Then
RS1.Fields(1).Value = strArray(2)
End If
RS1.Update

[pietlinden]

show us the definition of strArray()? Is it

Dim strArray(1 to 10)?

or

Dim strArray(10)?

if you used the latter, the index goes from 0 to 9, not 1 to 10.

[Tom]

This doesn't directly answer your questions, but have you looked at the
import text module posted at
http://www.mvps.org/access/modules/mdl0057.htm. I've used it in the
past and it has worked well.

Tom

[Trevor Best]

pi********@hotmail.com wrote:

show us the definition of strArray()? Is it

Dim strArray(1 to 10)?

or

Dim strArray(10)?

if you used the latter, the index goes from 0 to 9, not 1 to 10.

Actually it will go from 0 to 10, giving 100 elements.

--
This sig left intentionally blank

[MGFoster]

Trevor Best wrote:

pi********@hotmail.com wrote:

show us the definition of strArray()? Is it

Dim strArray(1 to 10)?

or

Dim strArray(10)?

if you used the latter, the index goes from 0 to 9, not 1 to 10.

Actually it will go from 0 to 10, giving 100 elements.

Don't you mean "giving 11 elements"? Single dimension array, right?

0 to 10: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Trevor Best]

MGFoster wrote:

Trevor Best wrote:

pi********@hotmail.com wrote:

show us the definition of strArray()? Is it

Dim strArray(1 to 10)?

or

Dim strArray(10)?

if you used the latter, the index goes from 0 to 9, not 1 to 10.

Actually it will go from 0 to 10, giving 100 elements.

Don't you mean "giving 11 elements"? Single dimension array, right?

0 to 10: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Um yes, 100 was a typo, still on my first cup of tea today :-)

--
This sig left intentionally blank