Jump to content
Sign in to follow this  
CE101

Excel's Text-Import Wizard

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 by CE101

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  

×