Set the values for .TextFileFixedColumnWidths in code

[jonelkins]

Hi All

Pulling my hair out over this one - can't seem to find anything on the net about how to do this, although I imagine it's dead easy and I'm just missing something!

OK... So in Excel I want to use code to import data from a number of different fixed width text files, using the same bit of code. I have a SQL database, in which I've created a table which lists the file 'short code' (unique identifier) and then a string of numbers representing the widths of each column (eg "1,10,60,8...etc"). Therefore, when I run the standard code, as below, I want to use something along the lines of .TextFileFixedColumnWidths = RS("ColumnWidths"). But this doesn't work (invalid procedure call or argument).

Any ideas?

This is the code I'm using...

[VBA]
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
.TextFileFixedColumnWidths = arrColWidths
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
[/VBA]

Thanks in advance...

Jon

[jonelkins]

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]

[Sandy1729]

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.