Quote Originally Posted by jonelkins View Post
Hi again... Apologies, after a bit more tweaking I appear to have cracked it. Here's the code I'm using...[VBA] Dim arrColWidths As Variant ReDim arrColWidths(Len(RS("ColumnWidths")) - Len(Replace(RS("ColumnWidths"), ",", ""))) For iCount = 1 To Len(RS("ColumnWidths")) - Len(Replace(RS("ColumnWidths"), ",", "")) + 1 arrColWidths(iCount - 1) = CInt(ExtractElement(RS("ColumnWidths"), iCount)) Next iCount With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFile, Destination:=Sheets(sCurrentClientShortCode & "." & sCurrentFile).Range("A1")) .Name = "Data.Import" .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, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileFixedColumnWidths = Array(arrColWidths) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With[/VBA]
Hi,Can u post the full code?ExtractElement() is user defined function?Please post its code too..My column widths are present in sheet "Input" Column 'A'. Please help me how to assign them to the array.Thanks,Sandeep.