Runtime error 9 subscript out of range

[vba317]

I am trying to open up an CSV file in a text delimited format. The file processes alright but when my routine returns to where it came from I get a runtime error 9 on line Set wrkbkOrig = Workbooks(sPracticeSelected).

Any help is appreciated.

'To open a csv file
If Right(sPracticeSelected, 3) = "csv" Then
 Call OpenCSVFile(sPracticeSelected)
 Set wrkbkOrig = Workbooks(sPracticeSelected)
End If


Public Sub OpenCSVFile(sPracticeSelected As String)
    Dim wrkbkCSV As Excel.Workbook
    Stop
    Set wrkbkCSV = Workbooks.Open(sPracticeSelected)
    Open sPracticeSelected For Input As #1
    row_number = 0
    Do Until EOF(1)
     Line Input #1, LinefromFile
     LineItems = Split(LinefromFile, "|")
     ActiveCell.Offset(row_number, 0).Value = LineItems(0)
     ActiveCell.Offset(row_number, 1).Value = LineItems(1)
     ActiveCell.Offset(row_number, 2).Value = LineItems(2)
     ActiveCell.Offset(row_number, 3).Value = LineItems(3)
     ActiveCell.Offset(row_number, 4).Value = LineItems(4)
     ActiveCell.Offset(row_number, 5).Value = LineItems(5)
     ActiveCell.Offset(row_number, 6).Value = LineItems(6)
     ActiveCell.Offset(row_number, 7).Value = LineItems(7)
     ActiveCell.Offset(row_number, 8).Value = LineItems(8)
     ActiveCell.Offset(row_number, 9).Value = LineItems(9)
     ActiveCell.Offset(row_number, 10).Value = LineItems(10)
     ActiveCell.Offset(row_number, 11).Value = LineItems(11)
     ActiveCell.Offset(row_number, 12).Value = LineItems(12)
     ActiveCell.Offset(row_number, 13).Value = LineItems(13)
     ActiveCell.Offset(row_number, 14).Value = LineItems(14)
     ActiveCell.Offset(row_number, 15).Value = LineItems(15)
     ActiveCell.Offset(row_number, 16).Value = LineItems(16)
     ActiveCell.Offset(row_number, 17).Value = LineItems(17)
     ActiveCell.Offset(row_number, 18).Value = LineItems(18)
     ActiveCell.Offset(row_number, 19).Value = LineItems(19)
     ActiveCell.Offset(row_number, 20).Value = LineItems(20)
     ActiveCell.Offset(row_number, 21).Value = LineItems(21)
     ActiveCell.Offset(row_number, 22).Value = LineItems(22)
     ActiveCell.Offset(row_number, 23).Value = LineItems(23)
'     ActiveCell.Offset(row_number, 24).Value = LineItems(24)
'     ActiveCell.Offset(row_number, 25).Value = LineItems(25)
'     ActiveCell.Offset(row_number, 26).Value = LineItems(26)
'     ActiveCell.Offset(row_number, 27).Value = LineItems(27)
'     ActiveCell.Offset(row_number, 28).Value = LineItems(28)
'     ActiveCell.Offset(row_number, 29).Value = LineItems(29)
'     ActiveCell.Offset(row_number, 30).Value = LineItems(30)


     row_number = row_number + 1
     Loop
     
'     Close #1
     Set wrkbkCSV = Nothing
End Sub


[\code]

 

[Norie]

Does sPracticeSelected include the path to the CSV as well as the filename?

 

[vba317]

Yes it does.

 

[Norie]

You need the path when opening a workbook, you don't need it when trying to refer to it with Workbooks.

Actually I'm a little confused by your code, you appear to be opening the workbook 2 times.

First here,

Set wrkbkCSV = Workbooks.Open(sPracticeSelected)

then here.

Open sPracticeSelected For Input As #1

Does that not cause any file access violation type problems?

 

[vba317]

I figured a way around it. removed the Set wrkbkCSV = Nothing from the sub and I changed the code to

Set wrkbkOrig = wrkbkCSV

Now it works