Hi All
I have used the macro to record the macro below to split the data in column E into rows by 'comma'. However the macro as shown below has a run time error - '2147024809 (80070057). A query with the name 'Table 1' already exists. The code in red below was highlighted for debug. Please could anyone help to point out the error. Many thanks.
ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Customers"", type text}, {""Alias"", type text}, {""Name"", type text}, {""Project Manager"", type text}, {""Presentations (e.g. 2020J, 2021D, 2021J)"", type text}, {""Course Code"", type text}, {""Presentation"", type any}, {""Projec" & _
"t Manager2"", type text}, {""Course Code & Presentation"", type text}})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.ExpandListColumn(Table.TransformColumns(#""Changed Type"", {{""Presentations (e.g. 2020J, 2021D, 2021J)"", Splitter.SplitTextByDelimiter("","", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), " & _
"""Presentations (e.g. 2020J, 2021D, 2021J)"")," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Presentations (e.g. 2020J, 2021D, 2021J)"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type1"""
I have used the macro to record the macro below to split the data in column E into rows by 'comma'. However the macro as shown below has a run time error - '2147024809 (80070057). A query with the name 'Table 1' already exists. The code in red below was highlighted for debug. Please could anyone help to point out the error. Many thanks.
ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Customers"", type text}, {""Alias"", type text}, {""Name"", type text}, {""Project Manager"", type text}, {""Presentations (e.g. 2020J, 2021D, 2021J)"", type text}, {""Course Code"", type text}, {""Presentation"", type any}, {""Projec" & _
"t Manager2"", type text}, {""Course Code & Presentation"", type text}})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.ExpandListColumn(Table.TransformColumns(#""Changed Type"", {{""Presentations (e.g. 2020J, 2021D, 2021J)"", Splitter.SplitTextByDelimiter("","", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), " & _
"""Presentations (e.g. 2020J, 2021D, 2021J)"")," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Presentations (e.g. 2020J, 2021D, 2021J)"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type1"""
VBA Code:
Range("G2").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$1000"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
[COLOR=rgb(0, 0, 0)]ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Customers"", type text}, {""Alias"", type text}, {""Name"", type text}, {""Project Manager"", type text}, {""Presentations (e.g. 2020J, 2021D, 2021J)"", type text}, {""Course Code"", type text}, {""Presentation"", type any}, {""Projec" & _
"t Manager2"", type text}, {""Course Code & Presentation"", type text}})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.ExpandListColumn(Table.TransformColumns(#""Changed Type"", {{""Presentations (e.g. 2020J, 2021D, 2021J)"", Splitter.SplitTextByDelimiter("","", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), " & _
"""Presentations (e.g. 2020J, 2021D, 2021J)"")," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Presentations (e.g. 2020J, 2021D, 2021J)"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type1"""[/COLOR]
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table1_2"
.Refresh BackgroundQuery:=False
End With