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