Converting a string to an array of integers

G

Guest

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
 
G

Guest

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.
 
G

Guest

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?
 
G

Guest

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top