Shazir
Banned - Rules violations
- Joined
- Jul 28, 2020
- Messages
- 94
- Office Version
- 365
- Platform
- Windows
Hello, I have made below macros with macro recorder which is working accurate. There is one thing that i want to change is to that macros(Query) should apply on sheet with desired name whereas it is adding further sheet.
For Example I have 5 sheets with different names in a file and i want to run this query on ("Data") sheet.
For Example I have 5 sheets with different names in a file and i want to run this query on ("Data") sheet.
VBA Code:
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""https://docs.google.com/spreadsheets/d/e/2PACX-1vT_6nlZevY76gVcevvGg-CbM64uJW_JVFWW3aP1o3TPSCXuZ_J4FMjBPtnO-Fqpf4M4hpc3y9Zw2SWT/pubhtml?gid=1986004827&single=true""))," & Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Column1"", type text}, {""Column2"", Int64.Type}, {""Column3"", ty" & _
"pe text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17""" & _
", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", type text}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""Column1"", ""Column2""})," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Removed Columns"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Promoted Headers"",{{" & _
"""Employee Name"", type text}, {""Deparment"", type text}, {""Designation"", type text}, {""Joining Date"", type date}, {""Status"", type text}, {""Proation Over"", type date}, {""Jan"", Int64.Type}, {""Feb"", Int64.Type}, {""Mar"", Int64.Type}, {""Apr"", Int64.Type}, {""May"", Int64.Type}, {""Jun"", Int64.Type}, {""Jul"", Int64.Type}, {""Aug"", Int64.Type}, {""Sep" & _
""", Int64.Type}, {""Oct"", Int64.Type}, {""Nov"", Int64.Type}, {""Dec"", Int64.Type}})" & 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=""Table 0"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 0]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_0"
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False
Selection.AutoFilter
ActiveWindow.DisplayGridlines = False
ActiveWorkbook.RefreshAll
End Sub