How to import data from .txt file into excel sheet using com interface?

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Sabestian Caine
Posts: 254
Joined: 12 Apr 2015, 03:53

How to import data from .txt file into excel sheet using com interface?

Post by Sabestian Caine » 04 Dec 2016, 04:59

friends i have a .txt file whose data i want to be imported in excel sheet. In that .txt file there are 12 columns of data which are separated by tabs. for this i made a simple script to automate mouse clicks, but it takes much time to import data from .txt file into excel sheet by this script as there are only mouse clicks and mouse drags in my script. You can see the sample of my .txt file here-
http://www.filetolink.com/08a5d71a51

or you can see the snap shoot of .txt file here-
http://imgur.com/a/PXQLN

i want to import such .txt files into excel sheet with the help of com interface. Please help me.

THANKS.
I don't normally code as I don't code normally.
YOU do what YOU want, but happens what HE wants.
YOU do what HE wants, will happen what YOU want.
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: How to import data from .txt file into excel sheet using com interface?

Post by kon » 04 Dec 2016, 07:58

Maybe try SafeArrayFromStr.
The example splits text into rows using linefeeds (`n) and columns using commas (,). You would just change the commas to tabs `t.
User avatar
Sabestian Caine
Posts: 254
Joined: 12 Apr 2015, 03:53

Re: How to import data from .txt file into excel sheet using com interface?

Post by Sabestian Caine » 04 Dec 2016, 10:30

kon wrote:Maybe try SafeArrayFromStr.
The example splits text into rows using linefeeds (`n) and columns using commas (,). You would just change the commas to tabs `t.
Thanks dear kon for your good answer. Please tell me how should i use these codes to split data into rows and columns. as i have copied all data of .txt file into clipboard now how to use these codes to split the data into rows and columns and to paste it into excel sheet.

there are two sets of codes in this link-
https://autohotkey.com/boards/viewtopic.php?f=6&p=95606

first are these-

Code: Select all

ExampleStr :=
    (LTrim Join`r`n
       "a,b,c,d
        1,2,3"
    )
MyObj := SafeArrayFromStr(ExampleStr, "`n", ",", "`r")
xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true
Book := xlApp.Workbooks.Add
TopLeftCell := xlApp.Cells(2, 3)                   ; Cell C2. The Cell at the start of the Range
BotRightCell := TopLeftCell.Offset(MyObj.Rows - 1, MyObj.Cols - 1)        ; Cell at end of Range
xlApp.Range(TopLeftCell, BotRightCell).Value := MyObj.SArr          ; Set the value of the Range
and second are these-

Code: Select all

; SafeArrayFromStr - https://autohotkey.com/boards/viewtopic.php?f=6&t=19872
; Revised: August 10, 2016
SafeArrayFromStr(Str, DelimRow, DelimCol:="", OmitRow:="", OmitCol:="")
{
    MaxRow := (Rows := StrSplit(Str, DelimRow, OmitRow)).MaxIndex()    ; Remember max row number
    MaxCol := 0
    for i, Row in Rows                                             ; Split each row into columns
        if ((Rows[i] := StrSplit(Row, DelimCol, OmitCol)).MaxIndex() > MaxCol)
            MaxCol := Rows[i].MaxIndex()                            ; Remember max column number
    SafeArray := ComObjArray(12, MaxRow, MaxCol)        ; Create a safearray of the correct size
    for Rn, Row in Rows                                           ; Copy Rows into the safearray
        for Cn, Cell in Row
            SafeArray[Rn - 1, Cn - 1] := Cell             ; Safearray indices start at 0 (not 1)
    return {SArr: SafeArray, Rows: MaxRow, Cols: MaxCol}
}
Kindly tell me how could i use these codes to split my data of .txt file into rows and columns.

Thanks..
I don't normally code as I don't code normally.
YOU do what YOU want, but happens what HE wants.
YOU do what HE wants, will happen what YOU want.
User avatar
Sabestian Caine
Posts: 254
Joined: 12 Apr 2015, 03:53

Re: How to import data from .txt file into excel sheet using com interface?

Post by Sabestian Caine » 04 Dec 2016, 11:59

Please tell me how to set these vba codes to work in ahk environment for importing data form .txt file-

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;G:\fineports\FinancialReport_25108.txt" _
        , Destination:=Range("$A$1"))
        .Name = "FinancialReport_25108"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 73
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(17, 1, 27, 32, 10)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I don't normally code as I don't code normally.
YOU do what YOU want, but happens what HE wants.
YOU do what HE wants, will happen what YOU want.
Guest

Re: How to import data from .txt file into excel sheet using com interface?

Post by Guest » 04 Dec 2016, 13:08

Sabestian Caine wrote:You can see the sample of my .txt file here-
http://www.filetolink.com/08a5d71a51
please post sample text in code box like this:

Code: Select all

friends i have a .txt file whose data i want to be 
imported in excel sheet. In that .txt file there are 12 columns of data which
are separated by tabs.  for this i made a simple script to automate mouse 
clicks, but it takes much time to import data from .txt file into excel sheet
by this script as there are only mouse clicks and mouse drags in my script.
no download or login required.
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: How to import data from .txt file into excel sheet using com interface?

Post by kon » 04 Dec 2016, 14:34

Most of the VBA code from the macro recorder is redundant; a lot of these lines are probably not required. ie: The Excel macro recorder has a habit of setting properties to their default values (or whatever arbitrary values you have set at the time of recording).

Code: Select all

/*
    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;G:\fineports\FinancialReport_25108.txt" _
            , Destination:=Range("$A$1"))
            .Name = "FinancialReport_25108"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 73
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
            .TextFileFixedColumnWidths = Array(17, 1, 27, 32, 10)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub
*/
x := ComObjActive("Excel.Application")
q := x.ActiveSheet.QueryTables.Add("TEXT;G:\fineports\FinancialReport_25108.txt", x.Range("$A$1"))
q.Name := "FinancialReport_25108"
q.FieldNames := True
q.RowNumbers := False
q.FillAdjacentFormulas := False
q.PreserveFormatting := True
q.RefreshOnFileOpen := False
q.RefreshStyle := xlInsertDeleteCells := 1
q.SavePassword := False
q.SaveData := True
q.AdjustColumnWidth := True
q.RefreshPeriod := 0
q.TextFilePromptOnRefresh := False
q.TextFilePlatform := 437
q.TextFileStartRow := 73
q.TextFileParseType := xlFixedWidth := 2
q.TextFileTextQualifier := xlTextQualifierDoubleQuote := 1
q.TextFileConsecutiveDelimiter := False
q.TextFileTabDelimiter := True
q.TextFileSemicolonDelimiter := False
q.TextFileCommaDelimiter := False
q.TextFileSpaceDelimiter := False
a := ComObjArray(0x3, 6)
Loop, 6
    a[A_Index - 1] := 1
q.TextFileColumnDataTypes := a
a := ComObjArray(0x3, 5)
for i, v in [17, 1, 27, 32, 10]
    a[i - 1] := v
q.TextFileFixedColumnWidths := a
q.TextFileTrailingMinusNumbers := True
q.Refresh(False)
User avatar
Sabestian Caine
Posts: 254
Joined: 12 Apr 2015, 03:53

Re: How to import data from .txt file into excel sheet using com interface?

Post by Sabestian Caine » 10 Dec 2016, 07:45

kon wrote:Most of the VBA code from the macro recorder is redundant; a lot of these lines are probably not required. ie: The Excel macro recorder has a habit of setting properties to their default values (or whatever arbitrary values you have set at the time of recording).

Code: Select all

/*
    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;G:\fineports\FinancialReport_25108.txt" _
            , Destination:=Range("$A$1"))
            .Name = "FinancialReport_25108"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 73
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
            .TextFileFixedColumnWidths = Array(17, 1, 27, 32, 10)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub
*/
x := ComObjActive("Excel.Application")
q := x.ActiveSheet.QueryTables.Add("TEXT;G:\fineports\FinancialReport_25108.txt", x.Range("$A$1"))
q.Name := "FinancialReport_25108"
q.FieldNames := True
q.RowNumbers := False
q.FillAdjacentFormulas := False
q.PreserveFormatting := True
q.RefreshOnFileOpen := False
q.RefreshStyle := xlInsertDeleteCells := 1
q.SavePassword := False
q.SaveData := True
q.AdjustColumnWidth := True
q.RefreshPeriod := 0
q.TextFilePromptOnRefresh := False
q.TextFilePlatform := 437
q.TextFileStartRow := 73
q.TextFileParseType := xlFixedWidth := 2
q.TextFileTextQualifier := xlTextQualifierDoubleQuote := 1
q.TextFileConsecutiveDelimiter := False
q.TextFileTabDelimiter := True
q.TextFileSemicolonDelimiter := False
q.TextFileCommaDelimiter := False
q.TextFileSpaceDelimiter := False
a := ComObjArray(0x3, 6)
Loop, 6
    a[A_Index - 1] := 1
q.TextFileColumnDataTypes := a
a := ComObjArray(0x3, 5)
for i, v in [17, 1, 27, 32, 10]
    a[i - 1] := v
q.TextFileFixedColumnWidths := a
q.TextFileTrailingMinusNumbers := True
q.Refresh(False)
thanks dear kon...........
I don't normally code as I don't code normally.
YOU do what YOU want, but happens what HE wants.
YOU do what HE wants, will happen what YOU want.
Post Reply

Return to “Ask For Help”