Converting a string to an array of integers

[DeathSurfer]

Duuuudes:

Is there an easy way in vba to convert a string to an array of integers. For
example:

I have this particular array of numbers in the element <column_data_types>
in a xml file:

<column_data_types>1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 5, 5, 1, 1,
1</column_data_types>

I have a function that will grab the array of numbers but the function
returns a string. ("1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 5, 5, 1, 1, 1") For this
particular situation I actually need this list of numbers to be numbers, not
a string, and pass them to an array in excel. Below is the situation that I'm
talking about, I am importing a query from a database and need to fill out
the .TextFileColumnDataTypes argument which takes an array of integers. I
need this to be dynamic so I put these columndatatypes in an xml file.

With WorkSheet("x").QueryTables.Add(Connection:="TEXT;" & ExcelFilePath
& "\FABRICREQ", Destination:=Range("FD_StartData"))
.Name = "FabricData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 5, 5,
1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Instead of looking like the above: .TextFileColumnDataTypes = Array(1, 1, 1,
1, 1, 1, 1, 1, 1, 5, 5, 5, 1, 1, 1)

it will look like this: .TextFileColumnDataTypes = Array(getXmlValue) except
getXmlValue returns a string which is the problem. Any ideas on how I should
handle this?

Any help would be appreciated.

Thanks

 

[Vergel Adriano]

Looks like the Split function is what you're looking for. Split will take a
string and delimiter as input and return an array. Search for "Split
Function" in the Microsoft Visual Basic Help.

 

[DeathSurfer]

From what I can tell the split function returns the array in a string unless
I'm doing something wrong. This is what I done:

..TextFileColumnDataTypes = Array(Split (getXmlValue, ",", 1,))

The above returned this:

"1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 5, 5,
1, 1, 1"

the numbers are in a string again. The function will not accept this.
I'm I doing something wrong?

 

[Vergel Adriano]

Hi,

The Split function already returns an array. Try it this way:

..TextFileColumnDataTypes = Split (getXmlValue, ",", 1,)

Hopefully, Excel would be able to do the data conversion automatically. If
that still gives you problems, then, you can take the output from Split and
put the values in an integer array.. here's an example:

Sub test()
Dim strarray As Variant
Dim iarray() As Integer
Dim i As Integer

strarray = Split("1,1,2", ",")
ReDim iarray(UBound(strarray))

For i = 0 To UBound(strarray)
iarray(i) = strarray(i)
Next i

End Sub

In the above code, strarray will be an array of string values ("1", "1",
"2"). iarray will have integer values (1, 1, 2)

 

[DeathSurfer]

Duuude:

That works, sorry you had to hold my hand through that.

later.