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