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)
--
Hope that helps.
Vergel Adriano
"DeathSurfer" wrote:
> 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" wrote:
>
> > 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.
> >
> >
> > --
> > Hope that helps.
> >
> > Vergel Adriano
> >
> >
> > "DeathSurfer" wrote:
> >
> > > 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
> > >
> > >
Similar Threads
