Subscript Out of Range

[DThreadgill]

Hi again -
I have a text file that is received on a daily basis and I use VBA to break down each field and add to an existing table. There are 50 fields in the text file (separated by semicolon) and I've had no problem whatsoever in it working. However, there have been 3 new fields (time - ie., 13:47:51) added to the text file and I need to modify my existing code (all fields not shown since it would take up too much space):

strFileName = "C:\Documents and Settings\user\Desktop\sometextfile.txt"
 
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(strFileName)) Then
Else
MsgBox "File Not Found"
Exit Sub
 
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:="Select * from [test]", _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
options:=adCmdText
 
Open strFileName For Input As #1
 
Do Until EOF(1)
    Line Input #1, strLineInput
strfield(1) = Mid(strLineInput, 1, 12)
strfield(2) = Mid(strLineInput, 14, 3) - - and everything in between
strfield(50) = Mid(strLineInput, 720, 8)  <----last field
rst.AddNew
rst![F1] = Trim(strfield(1))
rst![F2] = Trim(strfield(2))
rst![F50] = Trim(strfield(50))
rst![DateStamp] = Date
rst.Update
Loop
Close #1
rst.Close
Set rst = Nothing

It's as simple as adding three (actually 6) more lines (parse the fields):

strfield(51) = Mid(strLineInput, 729, 8)
strfield(52) = Mid(strLineInput, 738, 8)
strfield(53) = Mid(strLineInput, 747, 8)

And updating the table

rst![F51] = Trim(strfield(51))
rst![F52] = Trim(strfield(52))
rst![F53] = Trim(strfield(53))

The fields were added to the table as F51 - Date/Time - format is Long Time - etc.

I get the Subscript out of Range on strfield(53) = Mid(strLineInput, 747, 8). I've even changed the start position to 738 an length to 8 (same as 52) and still get the error.

I sure don't want to have to do something different since this was working before. Anyone have any ideas as to why it won't bring in that last field?

Any help is much appreciated.

[Rabbit]

I don't see strField dimmed anywhere. However, here's what I think the problem is, your array might be 50 elements big, but the index starts, by default, at 0 and ends at 49. So the first element of the array is strField(0) not strField(1). And the last element is strField(49) not strField(50).

But, isn't there a command to import a delimited text file? Why write code for each field?

[DThreadgill]

I don't see strField dimmed anywhere. However, here's what I think the problem is, your array might be 50 elements big, but the index starts, by default, at 0 and ends at 49. So the first element of the array is strField(0) not strField(1). And the last element is strField(49) not strField(50).

But, isn't there a command to import a delimited text file? Why write code for each field?

Thanks Rabbit!!! - strfield was dimmed but it was strfield(52)...I changed it to 53 and all is fine!! I didn't do the import command because even though the field length is 25 - - the actual field imported could be 17...wanted to trim the spaces upon import.

Thanks so much!!!!

[Rabbit]

Thanks Rabbit!!! - strfield was dimmed but it was strfield(52)...I changed it to 53 and all is fine!! I didn't do the import command because even though the field length is 25 - - the actual field imported could be 17...wanted to trim the spaces upon import.

Thanks so much!!!!

Not a problem. But that's weird, you're trying to access index 50, which is under 52 so there shouldn't have been a problem.

[DThreadgill]

Not a problem. But that's weird, you're trying to access index 50, which is under 52 so there shouldn't have been a problem.

Index 50 is where the original code stopped - I had to add 51-53 in order to get the additional 3 fields in there and it stopped at 53 (cause i had it dimmed at 52)...which I didn't look at before you mentioned it. Ya'll are amazing - - I have been trying to figure it out since this morning and even though ya'll can't "see" the code, the extra pair of eyes help out tremendously! Thanks!

[Rabbit]

Not a problem, good luck.