xcaliber13

Excel VBS and AutoIt

4 posts in this topic

In VBS  you can import a text file into Excel with a xlFixedWidth    Example:

 objExcel.Workbooks.OpenText"C:\temp\temp2.txt",,,xlFixedWidth,,,,,,,,, _
    Array(Array(0, xlTextFormat),Array(8, xlTextFormat),Array(20, xlTextFormat),Array(31, xlTextFormat))

This lets me define the start and end of each column and the format of that column. Can I define the start and end of each column using

_Excel_BookOpenText($oExcel, $sTextFile, Default, $xlFixedWidth) ?

Thank you
 

Share this post


Link to post
Share on other sites



According to MSDN you need to specify the starting position and the type in an array as described for xlDelimited in Example 1 of _Excel_BookOpenText.
Details can be found here: https://msdn.microsoft.com/en-us/library/ff837097(v=office.14).aspx Section: Remarks - FieldInfo paramter Information


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Water,

            Just getting back to this one.  Sorry for the delay.   So in Example 1  of _Excel_BookOpenText

Local $aField1[2] = [1, $xlTextFormat]
Local $aField2[2] = [2, $xlTextFormat]
Local $aField3[2] = [3, $xlGeneralFormat]
Local $aField4[2] = [4, $xlDMYFormat]
Local $aField5[2] = [5, $xlTextFormat]
Local $aFieldInfo[5] = [$aField1, $aField2, $aField3, $aField4, $aField5]

$aField1[2]  <---    Is this the number [2] that is the starting position of the column?  If not  then where do I put the starting position of each column?

Like the first column starts at position 0,  the second column starts at position 20,  the third column starts at position 30  

 

Thank you

Share this post


Link to post
Share on other sites

Yes. According to the help file:

$aFieldInfo [optional] An array containing parse information for individual columns of data.
    The interpretation depends on the value of DataType.
    When the data is delimited, this argument is an array of two-element arrays, with each two-element array
    specifying the conversion options for a particular column.
    The first element is the column number (1-based), and the second element is one of the XlColumnDataType
    constants specifying how the column is parsed (default = keyword Default)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

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