Excel's Text-Import Wizard

[CE101]

I'm trying to automate Excel's "text-import" wizard.

The objective is to convert the comma-delimited data from a TXT file to XLS format.

The TXT data looks like this...

"Document","Company","Name","Phone","Error Code"
"Intro2","ABC Industries","Jim Xarchos","+1 (313) 7262215","20"
"Intro1","KLM Resources","Harrold Kuehlein","+1 (205) 6228247","11"
"Intro1","XYZ Enterprises","Larry Alexiou","+1 (942) 4722791","11"

I ran the Excel macro recorder and got the following VBA code...

Workbooks.OpenText Filename:= _
        "C:\Documents and Settings\Cary1\Desktop\junk1.txt", Origin:=437, StartRow _
        :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
        3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
        , 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1)), _
        TrailingMinusNumbers:=True 
     ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Cary1\Desktop\junk1X.xls", FileFormat:=xlNormal, _
        Password:="FALSE", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

Fortunately, I know the values of the required Excel constants (x1Delimited, x1DoubleQuote, x1Normal) thanks to the file, Constants.txt, uploaded a few days ago by Enaiman to the following topic http://www.autoitscript.com/forum/index.php?showtopic=116795

So I've coded it up for AutoIT as follows...

#include <Excel.au3>
Dim $oExcel
#include <Array.au3>
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.Workbooks.OpenText("C:\Temp\Temp1.txt", _ 
            437,        _   ; Origin     
            1,          _   ; StartRow 
            1,      _   ; DataType = x1Delimited
            1,      _   ; TextQualifier = x1DoubleQuote
            False,      _   ; ConsecutiveDelimiter
            True,       _   ; Tab
            False,      _   ; Semicolon
            True,       _   ; Comma
            False,      _   ; Space
            False       _   ; Other 
                        )
$oExcel.ActiveWorkbook.SaveAs("C:\Temp\Temp1.xls", _ 
            -4143,      _   ; FileFormat = x1Normal   (ie XLS) 
            False,      _   ; Password
            "",     _   ; WriteResPassword  ("" - null)
            False,      _   ; ReadOnlyRecommended
            False       _   ; CreateBackup:=False 
                        )

I'm not sure how to code up the "FieldInfo:=Array(Array(1, 1)" stuff, so I left it out. I don't think it's critical.

Anyways the import works fine, except for one thing. When I try to reopen the file it asks for a password.

I don't know why. For the 2nd and 3rd arguments to SaveAs I have specified Password = False and WriteResPassword is null (""). Perhaps double quotes is not the way to express a null value ?????

Any suggestions would be greatly appreciated. Sample code would be even better.

Edited July 12, 2010 by CE101

[CE101]

There have been no suggestions on how to fix my "$oExcel.ActiveWorkbook.SaveAs" statement, so I will use AutoIT's own functions instead...

_ExcelBookSaveAs($oExcel, "C:\Temp\Temp1.xls", "xls", 0, 1) ;  Save file, overwrite existing file if necessary
_ExcelBookClose($oExcel) ; Close file

Edited July 14, 2010 by CE101