Friday, August 7, 2015

VBA script to import text file into existing sheet

'example fileStructure="28,17,42,24", dataTypes="2,1,1,1,1"
Sub ImportSheetData(fileStructure as string, dataTypes as string)
    Dim WidthsArray() As Integer
    Dim DataTypesArray() As Integer
    WidthsArray = StringToIntegerArray(fileStructure, ",")
    DataTypesArray = StringToIntegerArray(dataTypes, ",")

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & inputFullFileName, Destination:=ActiveSheet.Range("$A$1"))
        .Name = "Name"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = DataTypesArray
        .TextFileFixedColumnWidths = WidthsArray
        .TextFileTrailingMinusNumbers = True
        .MaintainConnection = False
        .Refresh BackgroundQuery:=False
        .DELETE
    End With
End Sub

No comments: