Excel macro split column data into rows

Apple08

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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

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
Back
Top