Excel Macro to import data

Question

Hello!

I am trying to do my homework and I need your help.

I have an application (.exe) which gives me in return about 20 files (Type: file), each of them with about 15 rows and 2 columns. Before I run the application I have to set some data in the Preinput file.

From all those 20 files I need the data from only 6 of them for each iteration (change in the Preinput file).

So I recorded a Macro that will import me that data in Excel and from there I manually put in a separate sheet. The problem is that I receive an error when I try to

run the Macro: Run-time error: '5' Invalid procedure call or argument.

The code is this (for all 6 files in the same Macro): 

Sub Macro1()

'

' Macro1 Macro

'

'

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;I:\Strutture per Veicoli Aerospaziali\Second Exercise\Exercise 2 - Macro Folder\IDA_prv_3." _

        , Destination:=Range("$C$1"))

        .CommandType = 0

        .Name = "IDA_prv_3."

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlFixedWidth

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileFixedColumnWidths = Array(24)

        .TextFileDecimalSeparator = "."

        .TextFileThousandsSeparator = " "

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    Range("F1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;I:\Strutture per Veicoli Aerospaziali\Second Exercise\Exercise 2 - Macro Folder\IDA_extv_1." _

        , Destination:=Range("$F$1"))

        .CommandType = 0

        .Name = "IDA_extv_1."

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlFixedWidth

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileFixedColumnWidths = Array(24)

        .TextFileDecimalSeparator = "."

        .TextFileThousandsSeparator = " "

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    Range("I1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;I:\Strutture per Veicoli Aerospaziali\Second Exercise\Exercise 2 - Macro Folder\IDA_extv_2." _

        , Destination:=Range("$I$1"))

        .CommandType = 0

        .Name = "IDA_extv_2."

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlFixedWidth

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileFixedColumnWidths = Array(24)

        .TextFileDecimalSeparator = "."

        .TextFileThousandsSeparator = " "

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    Range("L1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;I:\Strutture per Veicoli Aerospaziali\Second Exercise\Exercise 2 - Macro Folder\IDD_extv_3." _

        , Destination:=Range("$L$1"))

        .CommandType = 0

        .Name = "IDD_extv_3."

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlFixedWidth

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileFixedColumnWidths = Array(24)

        .TextFileDecimalSeparator = "."

        .TextFileThousandsSeparator = " "

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    Range("O1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;I:\Strutture per Veicoli Aerospaziali\Second Exercise\Exercise 2 - Macro Folder\IDA_extv_11." _

        , Destination:=Range("$O$1"))

        .CommandType = 0

        .Name = "IDA_extv_11."

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlFixedWidth

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileFixedColumnWidths = Array(24)

        .TextFileDecimalSeparator = "."

        .TextFileThousandsSeparator = " "

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    ActiveWindow.ScrollColumn = 2

    Range("R1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;I:\Strutture per Veicoli Aerospaziali\Second Exercise\Exercise 2 - Macro Folder\IDB_extv_12." _

        , Destination:=Range("$R$1"))

        .CommandType = 0

        .Name = "IDB_extv_12."

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlFixedWidth

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileFixedColumnWidths = Array(25)

        .TextFileDecimalSeparator = "."

        .TextFileThousandsSeparator = " "

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    ActiveWindow.SmallScroll ToRight:=5

    Range("U1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;I:\Strutture per Veicoli Aerospaziali\Second Exercise\Exercise 2 - Macro Folder\IDC_extv_13." _

        , Destination:=Range("$U$1"))

        .CommandType = 0

        .Name = "IDC_extv_13."

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlFixedWidth

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileFixedColumnWidths = Array(25)

        .TextFileDecimalSeparator = "."

        .TextFileThousandsSeparator = " "

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    ActiveWindow.ScrollColumn = 6

    ActiveWindow.ScrollColumn = 5

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 1

    ActiveWindow.SmallScroll Down:=5

    Range("C22").Select

    ActiveCell.FormulaR1C1 = "=R[-12]C[1]"

    Range("C23").Select

    ActiveCell.FormulaR1C1 = "=R[-4]C[4]"

    Range("C24").Select

    ActiveCell.FormulaR1C1 = "=R[-5]C[7]"

    Range("C25").Select

    ActiveCell.FormulaR1C1 = "=R[-6]C[10]"

    Range("C26").Select

    ActiveCell.FormulaR1C1 = "=R[-16]C[13]"

    Range("C27").Select

    ActiveCell.FormulaR1C1 = "=R[-17]C[16]"

    Range("C28").Select

    ActiveCell.FormulaR1C1 = "=R[-18]C[19]"

    Range("C29").Select

End Sub

If it is possible I will also attach an archive with all the files.

Please explain me what should I change to make it work!

Thank you!

P.S.: I overwrite the files each interation.

Answer 1

Hi,

try this...

Sub TextQuery_01()

'Nov 04, 2014

With ActiveSheet.QueryTables.Add(Connection:="text;e:\tmp\text1.txt", Destination:=Cells(1, 1)) '<< change

   ' .CommandType = 0 '<< ignore this line

    .FieldNames = True

    .RowNumbers = False

    .FillAdjacentFormulas = False

    .PreserveFormatting = True

    .RefreshOnFileOpen = False

    .RefreshStyle = xlOverwriteCells

    .SavePassword = False

    .SaveData = True

    .AdjustColumnWidth = False

    .RefreshPeriod = 0

    .TextFilePromptOnRefresh = False

    .TextFilePlatform = 850

    .TextFileStartRow = 1

    .TextFileParseType = xlDelimited

    .TextFileTextQualifier = xlTextQualifierDoubleQuote

    .TextFileConsecutiveDelimiter = False

    .TextFileTabDelimiter = True

    .TextFileSemicolonDelimiter = False

    .TextFileCommaDelimiter = False

    .TextFileSpaceDelimiter = False

    .TextFileColumnDataTypes = Array(1, 1)

    .TextFileFixedColumnWidths = Array(24)

    .TextFileDecimalSeparator = "."

    .TextFileThousandsSeparator = " "

    .TextFileTrailingMinusNumbers = True

    .Refresh BackgroundQuery:=False

End With

End Sub

note

You can set the CommandType property only if the value of the QueryType property for the query table or PivotTable cache is xlOLEDBQuery.

here..

http://msdn.microsoft.com/en-us/library/office/ff840602(v=office.15).aspx

Answer 2

Hello! You did not understand! The. exe file has nothing to do with the Macro! I am trying to import data from the.txt files alone! 

I change the Preinput file, I run the App (al manually) and after the. txt are generated I run the Macro to import data!

The debugger shows error on the line with:

CommandType=0

Answer 3

Hi,

your scenario is...  .exe file > .txt file > .xls file

since an .exe file is a compiled file

I am not sure how you can handle this 'issue'

(convert an .exe file to .txt file, via vba macro)

Answer 4

Hello!

I would like an answer anywhere! It is the 3rd topic I make on different forums! Still no answer!

Thanks!

Answer 5

Would you like your answer here or at Excel Macro to import data from a File on the other site?