Jump to content
Sign in to follow this  
exodius

Excel VBA - TextToColumns

Recommended Posts

I'm not having any luck with converting the Excel 2007 VBA code for TextToColumns into AutoIt COM formatting..

Could someone lend a hand?

The Excel VBA:

Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

The following code from this post seems like it's headed in the right direction, but I can't get it to actually work. :(

$oExcel.columns("E:E").select
Dim $aArray[2]=(1,2)
$oExcel.Selection.TextToColumns($oExcel.range("E1"),1,1,true,true,true,false,false,false,$aArray,1)

Share this post


Link to post
Share on other sites

This way is much easier. Kudos to PsaltyDS for explaining the technique.


#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Share this post


Link to post
Share on other sites

How it's done then?

I also want to transform a csv file with Excel's TextToColumns function using Autoit commands. Has anyone succesfully done it yet?

Share this post


Link to post
Share on other sites

@creaciones, you should really  open a new post rather than resurrect these old posts as AutoIt code has changed a lot since 2010.

With that said here is a basic example of TextToColumns

#include <Array.au3>
#include <Excel.au3>
#include <ExcelConstants.au3>

$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\DemoData.csv") ;~ Column A = 16/03/2019 2:30 pm
$oRange1 = $oWorkbook.ActiveSheet.Range("A:A") ;~ Source Range
$oRange2 = $oWorkbook.ActiveSheet.Range("C:C") ;~ Destination Range

;~ Reference: https://www.oreilly.com/library/view/programming-excel-with/0596007663/re515.html
$oRange1.TextToColumns($oRange2, $xlDelimited, $xlTextQualifierDoubleQuote, True, False, False, False, True, True, "/")
;~ Results
;~ Column C = Day
;~ Column D = Month
;~ Column E = Year
;~ Column F = Time
;~ Column H = Am/Pm

 

Edited by Subz

Share this post


Link to post
Share on other sites

MAAAAANY many thanks! really men i spent a long time searching this. I'm new, sorry for not opening a new thread, my apologies.

Edited by creaciones

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...