[RESOLVED] problem with Workbooks Open event

I have the following code :

VB Code:

Function Vals(File As String, Prob As Integer) As Variant
Set oXLApp = New Excel.Application
  oXLApp.Visible = True
 
 oXLApp.Workbooks.OpenText FileName:=File, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
        TrailingMinusNumbers:=True
  
 Set oXLBook = oXLApp.Workbooks(File)
 Set oXLSheet = oXLBook.Worksheets(1)
.....
Vals = ...
 
End Function

I get a subscript out of range error at the
Set oXLBook = oXLApp.Workbooks(File)
statement - why ?

I've checked that File does contain the correct string.

If I change the statement to :
Set oXLBook = oXLApp.Workbooks.Open(File)
everything works fine, but now I don't parse the text file correctly (It seems like it assumes default settings that are different from those that I specify in the OpenText statement).

Thanks,

try

VB Code:

set oxlbook = oXLApp.Workbooks.OpenText (FileName:=File, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
        TrailingMinusNumbers:=True)

pete

Quote:

---

Set oXLBook = oXLApp.Workbooks(File)

---

File is the full path and filename of the workbook but for a workbooks collection name all you need is the filename or just provide the index number.

VB Code:

Set oXLBook = oXLApp.Workbooks(1)

westconn1 :
when I try that code I get 'expected function or variable at the OpenText statement. I don't understand why.

RobDog888 :
the code compiles but the Excel file is still not parsed right (default settings that are different from those that I specify in the OpenText statement).

I was mistaken - the code :

Set oXLBook = oXLApp.Workbooks(1)

works (parses) as per the OpenText statement.

Thanks for the help !

No prob. :)

Dont forget to Resolve your thread so other members know its solved. ;)