Excel macro split column data into rows

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
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"""




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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,414
Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers.
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
Sure, I noticed this is related to the table name, I have recorded another macro to cross check the table name, then I adjusted my macro below and it works:

VBA Code:
 Range("E2").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$1000"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveWorkbook.Queries.Add Name:="Table1 (2)", 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 text}, {""Proje" & _
        "ct 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"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table1 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table1 (2)]")
        .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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top