Problem importing text file to Excel

[andols]

Hi,

I am tring to import a text file to Excel via VBA, I am working in ESRI ArcGIS Desktop.

I recorded a macro when I imported a text file in Excel and sligthly manipulated the code and pasted in ArcGIS VBA. It is the code below.

When I run it it opens a new Excel Sheet but instead of importing the text file a error messge apeares. It says.

Run-time error '438':
Object doesn't Supprt this property or method

Any idea anyone?
Anders

    Dim oExcel As Object    ' Variable to hold reference
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True

    oExcel.Workbooks.Add    'add a new workbook
        oExcel.ActiveSheet.QueryTables.Connection = "TEXT;C:\temp\Export_Output.txt"
        oExcel.ActiveSheet.QueryTables.Destination = oExcel.Range("A1")
        oExcel.ActiveSheet.QueryTables.Name = "Export_Output"
        oExcel.ActiveSheet.QueryTables.FieldNames = True
        oExcel.ActiveSheet.QueryTables.RowNumbers = False
        oExcel.ActiveSheet.QueryTables.FillAdjacentFormulas = False
        oExcel.ActiveSheet.QueryTables.PreserveFormatting = True
        oExcel.ActiveSheet.QueryTables.RefreshOnFileOpen = False
        oExcel.ActiveSheet.QueryTables.RefreshStyle = xlInsertDeleteCells
        oExcel.ActiveSheet.QueryTables.SavePassword = False
        oExcel.ActiveSheet.QueryTables.SaveData = True
        oExcel.ActiveSheet.QueryTables.AdjustColumnWidth = True
        oExcel.ActiveSheet.QueryTables.RefreshPeriod = 0
        oExcel.ActiveSheet.QueryTables.TextFilePromptOnRefresh = False
        oExcel.ActiveSheet.QueryTables.TextFilePlatform = xlWindows
        oExcel.ActiveSheet.QueryTables.TextFileStartRow = 1
        oExcel.ActiveSheet.QueryTables.TextFileParseType = xlDelimited
        oExcel.ActiveSheet.QueryTables.TextFileTextQualifier = xlTextQualifierDoubleQuote
        oExcel.ActiveSheet.QueryTables.TextFileConsecutiveDelimiter = False
        oExcel.ActiveSheet.QueryTables.TextFileTabDelimiter = True
        oExcel.ActiveSheet.QueryTables.TextFileSemicolonDelimiter = True
        oExcel.ActiveSheet.QueryTables.TextFileCommaDelimiter = False
        oExcel.ActiveSheet.QueryTables.TextFileSpaceDelimiter = False
        oExcel.ActiveSheet.QueryTables.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        oExcel.ActiveSheet.QueryTables.Refresh BackgroundQuery:=False

 

[PHV]

With oExcel.ActiveSheet.QueryTables.Add( _
Connection:="TEXT;C:\temp\Export_Output.txt", _
Destination:=oExcel.Range("A1"))
.Name = "Export_Output"
.FieldNames = True
.RowNumbers = False
...
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Need a SHEET reference rather than application

With oExcel.ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;C:\temp\Export_Output.txt", _
        Destination:=oExcel.[b]ActiveSheet[/b].Range("A1"))
    .Name = "Export_Output"
    .FieldNames = True
    .RowNumbers = False
...
End With

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]