Get value from textbox and use it inthe form of Array

AkshayRM

New Member
Joined
Jun 22, 2020
Messages
9
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,051
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
   Dim delimitvalues As Variant, Ary As Variant
   delimitvalues = UserForm1.Textbook.Value
   Ary = Evaluate("{" & Join(delimitvalues, ",") & "}")
   

        .TextFileFixedColumnWidths = Ary
 

AkshayRM

New Member
Joined
Jun 22, 2020
Messages
9
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
   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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,051
Office Version
  1. 365
Platform
  1. Windows
What exactly are you putting in the textbox?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,051
Office Version
  1. 365
Platform
  1. Windows
That should be ok, can you please post your entire code.
 

AkshayRM

New Member
Joined
Jun 22, 2020
Messages
9
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,051
Office Version
  1. 365
Platform
  1. Windows
Oops, it should be
VBA Code:
   delimitvalues = Split(UserForm1.TextBook.Value, ",")
   Ary = Evaluate("{" & Join(delimitvalues, ",") & "}")
 

AkshayRM

New Member
Joined
Jun 22, 2020
Messages
9
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,051
Office Version
  1. 365
Platform
  1. Windows
As they are all 1s you can just comment out that line of the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,076
Messages
5,575,988
Members
412,693
Latest member
SJC2020
Top