Unqualified Reference: running excel in the background from Access Opening Text Doc

[Rowrow]

I have a minor problem with opening a text document in excel through access. As I am in Access I am assuming the array references in the .opentext... array, are unqualified.

Please give me your thoughts. I can problem work around the problem, but I hate leaving a problem unsolved (it is bound to crop up again).

Many Thanks
Row

Sub ProcessEpro(strDir As String)
Dim xl As Object
Dim strFile As String
strFile = Mid(strDir, 66, Len(strDir) - 69)
Set xl = CreateObject("Excel.Application")
<<< The problem is in here >>>

xl.workbooks.Opentext FileName:=strDir, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma _
:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, _
2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2)), TrailingMinusNumbers:=True

<<<end problem="" of="">>>


xl.workbooks(strFile).Close
Set xl = Nothing
End Sub</end>

 

[Rowrow]

Had to work around it a bit:

Wrote the code in excel and then called the sub from Access.

xl.run "Macro"

If anyone has a better solution I am still interested.

Thanks
Row

 

[RoryA]

I don't think it's the Array function that's the issue, it's the xl constants. Try this:

Sub ProcessEpro(strDir As String)
Const xlMSDOS as long = 3
Const xlDelimited as long = 1
const xlNone as long = -4142

Dim xl As Object
Dim strFile As String
strFile = Mid(strDir, 66, Len(strDir) - 69)
Set xl = CreateObject("Excel.Application")

xl.workbooks.Opentext FileName:=strDir, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma _
:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, _
2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2)), TrailingMinusNumbers:=True
xl.workbooks(strFile).Close
Set xl = Nothing
End Sub