UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Unpivot Worksheet Macro, Office 2013    
 
   
mjschukas
post Oct 2 2017, 09:50 AM
Post#1



Posts: 2,193
Joined: 24-September 04



I have recorded the following Macro

(works well, but the second time I run, I get an Error: "Table3 already exists..." - I only need one, so I suppose I would just like to delete it and use again...)

??

CODE
Range("Table1[#All]").Select
    ActiveWorkbook.Queries.Add Name:="Table3", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Department"", type text}, {""January"", Int64.Type}, {""February"", Int64.Type}, {""March"", Int64.Type}, {""April"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Unpivoted Columns"" = Table.UnpivotOtherColumns(#""Changed Type"", {""Department""}, ""Attrib" & _
        "ute"", ""Value"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Unpivoted Columns"""
    Sheets.Add After:=ActiveSheet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1" _
        , 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_3"
        .Refresh BackgroundQuery:=False
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


?

thank you.
Go to the top of the page
 
GroverParkGeorge
post Oct 2 2017, 09:53 AM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


And the question is?

--------------------
Go to the top of the page
 
mjschukas
post Nov 30 2017, 04:08 PM
Post#3



Posts: 2,193
Joined: 24-September 04



sorry, I've been away...

I would like to overwrite "Table3" at the line generating the error: "Table3 already exists..."

CODE
ActiveWorkbook.Queries.Add Name:="Table3"


?

thank you. (pardon me again for my absence)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 09:43 PM