Kindly help me to modify these COM Object Codes to import specific text file into MS Excel?

[Sabestian Caine]

Hello friends..

I have these codes-

Code: Select all

XL:=comobjactive("excel.application")
FullFileName:="C:\Users\htc\Desktop\important_data.txt"
XL.Application.Workbooks.OpenText(FullFileName), origin:=65001, StartRow:=1, DataType:=1, TextQualifier:=1, ConsecutiveDelimiter:=False, Tab:=tabD, Semicolon:=False, Comma:=csvD, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
return

When I run above codes, a text file named important_data.txt is imported into a new excel workbook, while I want to import it into the sheet3 of already opened Excel workbook.

Important_data.txt looks like this-



And when I import it into MS Excel using above codes, it imports the important_data.txt correctly into MS Excel, but it imports into new workbook. Please look at this screen shot-



In the above screen shot you can see that the file is imported correctly but it is imported into new workbook whose name is set important_data and the sheet’s name is also set important_data (as you can see the workbook name and sheet name in red circle in above image). I want that it should be imported into the already opened Excel workbook’s sheet3. Please look at this screen shot-


The above is the image of my default workbook in which I work. This workbook (my_regular_workbook.xlsx) remains opened all the time when I work. I want that important_data.txt should be imported into sheet3 of my this workbook.

Please tell me what modifications should we make into the codes to import the specific .txt file into already opened workbook?

I am attaching important_data.txt with this post.

Please help me.

Thanks a lot…

[AlphaBravo]

Code: Select all

XL:=ComObjActive("excel.application")
wb := xl.ActiveWorkbook
FullFileName:="C:\Users\htc\Desktop\important_data.txt"
XL.Application.Workbooks.OpenText(FullFileName)
wb2 := xl.ActiveWorkbook
wb2.Sheets(1).Cells.Copy(wb.Sheets(3).Range("A1"))
wb2.Close(False)
wb.Sheets(3).activate

[Sabestian Caine]

Code: Select all

XL:=ComObjActive("excel.application")
wb := xl.ActiveWorkbook
FullFileName:="C:\Users\htc\Desktop\important_data.txt"
XL.Application.Workbooks.OpenText(FullFileName)
wb2 := xl.ActiveWorkbook
wb2.Sheets(1).Cells.Copy(wb.Sheets(3).Range("A1"))
wb2.Close(False)
wb.Sheets(3).activate

Thank you so much dear AlphaBravo ...

Dear AlphaBravo your codes are working fine... but there is a problem... Sir, when i run your codes, they first import important_data.txt into a new workbook and then copy-paste the data into sheet3 of my already opened workbook. Sir, in fact I want that whenever codes are run they should import important_data.txt into my already opened workbook directly i.e. it should not be imported into new workbook and then copy paste it into my already opened workbook.

So sir I request you to provide me such codes by which i can import important_data.txt directly into my already workbook's sheet3.

Thanks a lot AlphaBravo...

[AlphaBravo]

question 1: How would you do open a text file to an existing sheet manually?
question 2: Please elaborate why opening into an new workbook then copy/paste is not good enough?

[Sabestian Caine]

question 1: How would you do open a text file to an existing sheet manually?
question 2: Please elaborate why opening into an new workbook then copy/paste is not good enough?

Hello dear AlphaBravo ...

Sir, for opening/importing text file in excel, i am first going in DATA tab and then click From Text button. it opens a dialog box and i put the full path of important_data.txt, then again a dialog box appears and i click on next and then on next button again, then again it shows a dialog box asking from which cell i will like to import this text file then i click on cell a1 then i click on ok button and then important_data.txt is imported into my existing workbook. Please look at these images-














for this these vba codes can also be used-

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\htc\Desktop\important_data.txt", Destination:=Range("$A$1"))
        .Name = "important_data"
        .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 = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(12, 11, 20, 7, 26, 7, 14, 20, 5, 13, 12, 13)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

As you can see above are the VBA codes. I do not know how to convert them into COM object codes. If above codes are converted into COM object codes then my problem may solve also.


Sir, secondly you asked why it is not good enough to import text file into other workbook and then to copy paste it into existing workbook? so, I would say that it is not a big issue, but i know by using COM object in AHK we can directly import the specific text file into existing workbook. Secondly, (pardon me) it doesn't look logical to first import the text file into other workbook and then to copy paste it into existing workbook.


I hope you will help me...


Thanks a lot AlphaBravo...

[AlphaBravo]

Edited :

Code: Select all

xl := ComObjActive("Excel.Application")
QT := xl.ActiveSheet.QueryTables.Add(Connection:= "TEXT;C:\Users\htc\Desktop\important_data.txt", Destination:=xl.Range("$A$1"))
QT.Name := "important_data"
QT.FieldNames := True
QT.RowNumbers := False
QT.FillAdjacentFormulas := False
QT.PreserveFormatting := True
QT.RefreshOnFileOpen := False
QT.RefreshStyle := 1 ; xlInsertDeleteCells
QT.SavePassword := False
QT.SaveData := True
QT.AdjustColumnWidth := True
QT.RefreshPeriod := 0
QT.TextFilePromptOnRefresh := False
QT.TextFilePlatform := 437
QT.TextFileStartRow := 1
QT.TextFileParseType := 2 ; xlFixedWidth
QT.TextFileTextQualifier := 1 ; xlTextQualifierDoubleQuote
QT.TextFileConsecutiveDelimiter := False
QT.TextFileTabDelimiter := True
QT.TextFileSemicolonDelimiter := False
QT.TextFileCommaDelimiter := False
QT.TextFileSpaceDelimiter := False

SafeArray1 := ComObjArray(0x3, 13)
loop 13
    SafeArray1[A_Index - 1] := 1
QT.TextFileColumnDataTypes := SafeArray1

SA2 := [12, 11, 20, 7, 26, 7, 14, 20, 5, 13, 12, 13]
SafeArray2 := ComObjArray(0x3, 12)
loop 12
    SafeArray2[A_Index - 1] := SA2[A_Index]

QT.TextFileFixedColumnWidths :=  SafeArray2
QT.TextFileTrailingMinusNumbers := True
QT.Refresh( BackgroundQuery:=False)
return

[Sabestian Caine]

Edited :

Code: Select all

xl := ComObjActive("Excel.Application")
QT := xl.ActiveSheet.QueryTables.Add(Connection:= "TEXT;C:\Users\htc\Desktop\important_data.txt", Destination:=xl.Range("$A$1"))
QT.Name := "important_data"
QT.FieldNames := True
QT.RowNumbers := False
QT.FillAdjacentFormulas := False
QT.PreserveFormatting := True
QT.RefreshOnFileOpen := False
QT.RefreshStyle := 1 ; xlInsertDeleteCells
QT.SavePassword := False
QT.SaveData := True
QT.AdjustColumnWidth := True
QT.RefreshPeriod := 0
QT.TextFilePromptOnRefresh := False
QT.TextFilePlatform := 437
QT.TextFileStartRow := 1
QT.TextFileParseType := 2 ; xlFixedWidth
QT.TextFileTextQualifier := 1 ; xlTextQualifierDoubleQuote
QT.TextFileConsecutiveDelimiter := False
QT.TextFileTabDelimiter := True
QT.TextFileSemicolonDelimiter := False
QT.TextFileCommaDelimiter := False
QT.TextFileSpaceDelimiter := False

SafeArray1 := ComObjArray(0x3, 13)
loop 13
    SafeArray1[A_Index - 1] := 1
QT.TextFileColumnDataTypes := SafeArray1

SA2 := [12, 11, 20, 7, 26, 7, 14, 20, 5, 13, 12, 13]
SafeArray2 := ComObjArray(0x3, 12)
loop 12
    SafeArray2[A_Index - 1] := SA2[A_Index]

QT.TextFileFixedColumnWidths :=  SafeArray2
QT.TextFileTrailingMinusNumbers := True
QT.Refresh( BackgroundQuery:=False)
return

Thank you dear AlphaBravo.... you really solved my problem... so nice of you... you are super expert in COM AHK Coding....


Sir, please do my one more favor...

I want to learn these codes-

xl := ComObjActive("Excel.Application")
QT := xl.ActiveSheet.QueryTables.Add(Connection:= "TEXT;C:\Users\htc\Desktop\important_data.txt", Destination:=xl.Range("$A$1"))
QT.Name := "important_data"
QT.FieldNames := True
QT.RowNumbers := False
QT.FillAdjacentFormulas := False
QT.PreserveFormatting := True
QT.RefreshOnFileOpen := False
QT.RefreshStyle := 1 ; xlInsertDeleteCells
QT.SavePassword := False
QT.SaveData := True
QT.AdjustColumnWidth := True
QT.RefreshPeriod := 0
QT.TextFilePromptOnRefresh := False
QT.TextFilePlatform := 437
QT.TextFileStartRow := 1
QT.TextFileParseType := 2 ; xlFixedWidth
QT.TextFileTextQualifier := 1 ; xlTextQualifierDoubleQuote
QT.TextFileConsecutiveDelimiter := False
QT.TextFileTabDelimiter := True
QT.TextFileSemicolonDelimiter := False
QT.TextFileCommaDelimiter := False
QT.TextFileSpaceDelimiter := False
;********* I know above codes are generated by VBA macro in excel*********************
;*********************I want to learn the following codes*******************************


SafeArray1 := ComObjArray(0x3, 13) ; why you created safe array here?
loop 13 ; why you used loop 13 here?
SafeArray1[A_Index - 1] := 1 ; b]what is the meaning of this line?[/b]
QT.TextFileColumnDataTypes := SafeArray1 ; what is this?
; and futher what is the purpose of following codes-
SA2 := [12, 11, 20, 7, 26, 7, 14, 20, 5, 13, 12, 13]
SafeArray2 := ComObjArray(0x3, 12)
loop 12
SafeArray2[A_Index - 1] := SA2[A_Index]

QT.TextFileFixedColumnWidths := SafeArray2
QT.TextFileTrailingMinusNumbers := True
QT.Refresh( BackgroundQuery:=False)
return

Please make me understand the use of above codes. I am highly obliged to you... Thank you very much dear AlphaBravo...

[AlphaBravo]

It is mostly Query Table properties, I merely interpreted it to Autohotkey format.

Autohotkey does not understand Excel constants like "xlInsertDeleteCells", "xlFixedWidth" and such, you just have to look up their values.

These two properties were entered as arrays in VBA, that's why I used safe arrays.

Code: Select all

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(12, 11, 20, 7, 26, 7, 14, 20, 5, 13, 12, 13)