Get value from textbox and use it inthe form of Array

[AkshayRM]

Get value from textbox and use it in the form of Array.

So basically what I am doing is,converting fixedwidth file.
I recorded a Macro for that. And delimiting the values. And the below value I am getting from userform. (Bold and underlined)
. TextFileFixedColumnWidths = Array(3, 5, 8, 8, 13...)
so user will enter values in text box like- 3 ,5, 8, 8, 13...)
Now I declared a variable.
Dim delimitvalues As Variant
delimitvalues = UserForm1. Textbook. Value
And then code
.
.
.
. TextFileFixedColumnWidths = Array(delimitvalues)

but this is not working it's shows error-Run time error 5 (Invalid procedure call or argument)
what I understand is it's taking as string and not as array.

Even I tries to remove double quotes by
delimitvalues=Replace(delimitvalues, Chr(34), "")

I tried this also
MyZeroBasedArray = Split (delimitvalues, ", ")

but nothing worked.
please help me on this with whichever method possible.

I hope my question is clear pleas let me know i will clarify.

 

[Fluff]

Hi & welcome to MrExcel.
How about

   Dim delimitvalues As Variant, Ary As Variant
   delimitvalues = UserForm1.Textbook.Value
   Ary = Evaluate("{" & Join(delimitvalues, ",") & "}")
   

        .TextFileFixedColumnWidths = Ary

 

[AkshayRM]

Hi & welcome to MrExcel.
How about

   Dim delimitvalues As Variant, Ary As Variant
   delimitvalues = UserForm1.Textbook.Value
   Ary = Evaluate("{" & Join(delimitvalues, ",") & "}")
  

        .TextFileFixedColumnWidths = Ary

Hello Fluff,
Thanks for looking into code.
When I debug by adding ur code I get error at line- Ary = Evaluate...... As Run time error 13 (Type mismatch)

 

[Fluff]

What exactly are you putting in the textbox?

 

[AkshayRM]

What exactly are you putting in the textbox?

3, 4, 9, 15, 7,11

 

[Fluff]

That should be ok, can you please post your entire code.

 

[AkshayRM]

That should be ok, can you please post your entire code.

Sub linedata()

Dim delimitvalues As Variant, Ary As Variant

delimitvalues = UserForm1. Textbook. Value

Ary = Evaluate ("{" & Join(delimitvalues, ", ") & "}")

With ActiveSheet. QueryTables. Add(Connection:= _
" TEXT; E:\............. Sample. txt" , Destination:=Range("$A$1"))
. 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
.TextFileTextQulifier=xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextfileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataType = Array(1, 1,1,1,1,1,1,1.....)
.TextFileFixedColumnWidths = Ary
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With

End Sub

userform
one text box and command button.
value input in text box- 8, 5, 7, 22, 15......

command button code-
private Sub CommandButton1_Click()

Call Module2. linedata

End Sub.


-----------
Thanks.

 

[Fluff]

Oops, it should be

   delimitvalues = Split(UserForm1.TextBook.Value, ",")
   Ary = Evaluate("{" & Join(delimitvalues, ",") & "}")

 

[AkshayRM]

Hi
It's working now thank you so much.
Can I ask for some more help,

As you can see in code there is one more array.
.TextFileColumnDataType = Array(1, 1,1,1,1,1,1,1.....)
And this, I am not taking from user.
This is like if there are 7 values or numbers in Ary above then no. Of values in this array should be 8. that is no of values in Ary + 1. And then it should print 8 (1 values) as below.

.TextFileColumnDataType = Array(1, 1,1,1,1,1,1,1.....)

Is this possible.

Thanks.

 

[Fluff]

As they are all 1s you can just comment out that line of the code.