Getting value from userform and using it into code.

[AkshayRM]

Hi Team,

I am new to excel vba and trying to learn, but I am stuck in one place, I am not able to use value given by user into my code.

I am spliting the cell having multiple values into different rows.

Code-
Sub Macro7()
Dim columnname as string

Columnname=userform1.text1.value

Worksheet("sheet1").Activate
ActiveSheet. Usedrange. Select

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$M$16881"), , xlYes). Name_
="Tablel"
Range("Tablel[#All]"). Select
ActiveWorkbook. Queries. Add Name:="Table1", Formula:=_
"let" & Chr(13) & "" & Chr(10) & " Source=Excel.CurrentWorkbook() {[Name=""Table1""]}[Content], " & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter""=Table.ExpandListColumm(Table.TransformColumns(Source, {{""Hospital ID"", Splitter. SplitTextByDelimiter(""#(lf) "", QuoteStyle.Csv), let itemType= (type nullable text) meta [Serialized.Text=true] in type {itemType}}}), "" Hospital ID"") " & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Split Co" & "lumn by Delimiter"""
Sheets. Add After:=ActiveSheet
With ActiveSheet. ListObjects. Add(SourceType:=0, Source:=_
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties="""""_
, Destination:=Range("$A$l")). QueryTable
. CommandType=xlCmdSql
. CommandText=Array(" SELECT * FROM

")
.RowNumbers=False
. FillAdjacentFormulas=False
.PreserveFormatting=True
.RefreshOnFileOpen=False
.BackgroundQuery=True
.RefreshStyle=xlInsertDeleteCells
.SavePassword=False
.SaveData=True
.AdjustColumnWidth=True
.RefreshPeriod=0
.PreserveColumnInfo=False
.ListObject.DisplayName="Tablel_2"
.Refresh BackgroundQuery:=False
End With

End Sub

----------------------
Hospital ID is the column name which user enters. (The user may enter different column name.)

I tried to put (columnname) variable instead of Hospital ID . put getting errors.

 

[dmt32]

Hi,
trying using the built-in InputBox Function & see if this will do what you want

Sub Macro7()
Dim ColumnName As String
        
 Do
    ColumnName = InputBox("Enter Column Name", "Column Name")
'cancel pressed
    If StrPtr(ColumnName) = 0 Then Exit Sub
 Loop Until Len(ColumnName) > 0

'rest of code

Dave

 

[AkshayRM]

Hi,
trying using the built-in InputBox Function & see if this will do what you want

Sub Macro7()
Dim ColumnName As String
       
Do
    ColumnName = InputBox("Enter Column Name", "Column Name")
'cancel pressed
    If StrPtr(ColumnName) = 0 Then Exit Sub
Loop Until Len(ColumnName) > 0

'rest of code

Dave

Thank you for replay,
But as it will not make any difference.
Still tried your code as you gave. Getting same error.
Error-
[Expression.Error] The import columnName matches no exports. Did you miss a module reference?

 

[AkshayRM]

Can any one help me on this?