By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Import Text Via VBA

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
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)
Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.