Sign in to follow this  
Followers 0
Googamanga

Importing Delimited Text Data into Excel

2 posts in this topic

#1 ·  Posted (edited)

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 by Googamanga

Share this post


Link to post
Share on other sites



And what is the error you get?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0