Open Tab Delimited Text File in Excel From Access

[emorris1000]

Been working on some stuff that requires me to open a tab delimited text file in excel. I have the Excel scripting library referenced, the basic codes goes as follows (with a couple of irrelevant deletions).

Dim StrFilePathB As String
Dim fname As String
Dim rs As Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
 
fname = "~~~~~filename~~~~"
StrFilePathB = "~~~file location~~~"
 
Set xlApp = GetObject(, "Excel.Application")

If xlApp Is Nothing Then    'No instance of Excel is available
    Set xlApp = New Excel.Application
End If
 
Set xlBook = xlApp.Workbooks.OpenText(filename:= "" & StrFilePathB & fname & "", StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True, Local:=True)
 
 
......

That last line throws a compile error. I lifted most of this code directly out of an Excel VBA script I wrote a while ago. I referenced the Excel scripting library and I believe the way I have modified it should run here...

Anyways, anyone have any ideas why that's not working right?

 

[DJkarl]

Been working on some stuff that requires me to open a tab delimited text file in excel. I have the Excel scripting library referenced, the basic codes goes as follows (with a couple of irrelevant deletions).

Dim StrFilePathB As String
Dim fname As String
Dim rs As Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
 
fname = "~~~~~filename~~~~"
StrFilePathB = "~~~file location~~~"
 
Set xlApp = GetObject(, "Excel.Application")
 
If xlApp Is Nothing Then    'No instance of Excel is available
    Set xlApp = New Excel.Application
End If
 
Set xlBook = xlApp.Workbooks.OpenText(filename:= "" & StrFilePathB & fname & "", StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True, Local:=True)
 
 
......

That last line throws a compile error. I lifted most of this code directly out of an Excel VBA script I wrote a while ago. I referenced the Excel scripting library and I believe the way I have modified it should run here...

Anyways, anyone have any ideas why that's not working right?

Looking at the function OpenText it does not seem to have a return type, if you change

this

Set xlBook = xlApp.Workbooks.OpenText(filename:= "" & StrFilePathB & fname & "", StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True, Local:=True)

to this

Call xlApp.Workbooks.OpenText(filename:= "" & StrFilePathB & fname & "", StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True, Local:=True)

it should work to open the file, if you need the workbook reference you will need to set it via xlApp.Workbooks(1) or perhaps the ActiveWorkbook.