Importing Delimited Text Data into Excel

[Googamanga]

I have an Excel sheet that is linked to a source text file. The link to the source source file needs to be changed periodicaly to another file. Currently, to change the link to another source file I open up excel and click on "Refresh All" which asks me the name of the new source file. I am trying to automate this process. Another important challenge is that the text file needs to be linked to Cell "B2", Column A and Row 1 are populated with important equations.

I havn't found a way to do this UDFs. I tried looking at the source code of the Excel UDFs and only got more confused.

I ran a macro in excel of what i need to and the following cme out. This is what i need to do in AutoIt.

Please help!

############   IN VBA ###########
    Range("B2").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;K:\CostDownloads\Vendor\Source\2010\Vendor20091230-05.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

############   IN AUTOIT    ############

Local $Excel = ObjCreate("Excel.Application")

    $Excel.Visible = True
    ;;http://msdn.microsoft.com/en-us/library/aa215950(office.11).aspx
    $Excel.Workbooks.Open("K:\CostDownloads\Vendor\Import\Vendor_Import_Template.xls", 2, False)
    ;;http://msdn.microsoft.com/en-us/library/aa195811(office.11).aspx

    With $Excel.Workbook.WorkSheets(1).Range("B2").QueryTable
        .Connection = "TEXT;K:\CostDownloads\Lexis\Source\2010\Lexis20091230-05.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh
    endWith

Edited January 16, 2010 by Googamanga

[Juvigy]

And what is the error you get?