tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,825
- Office Version
- 365
- 2019
- Platform
- Windows
I have a workbook called Data.xlsx that contains a single column of numerical data, numbers 1 to 10 in cells A1 through to A10.
In another workbook, I start by recording a macro, taking these steps:
1. Clicked Data
2. Get Data
3. From File
4. From Workbook
5. Chose the file, Data.xlsx and clicked Import
6. Selected the worksheet (Sheet1) containing the data
7. Clicked Transform Data
8. Clicked the filter dropdown and selected greater than 3
9. Clicked OK and Close and Load
Stopped the macro recording button.
This is the code generated:
However when I try to run the above code, I get an error message:
What does it mean? Is it refering to the workbook Data.xlsx or the workbook which I am recording the macro? I tried changing the name of the worksheet in both workbooks (from Sheet1 to Sheet10) but the error remains.
Thanks
In another workbook, I start by recording a macro, taking these steps:
1. Clicked Data
2. Get Data
3. From File
4. From Workbook
5. Chose the file, Data.xlsx and clicked Import
6. Selected the worksheet (Sheet1) containing the data
7. Clicked Transform Data
8. Clicked the filter dropdown and selected greater than 3
9. Clicked OK and Close and Load
Stopped the macro recording button.
This is the code generated:
Code:
Option Explicit
Sub Macro1()
Range("A1").Select
ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\MyFolder\data.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Sheet1_Sheet,{{""Column1"", Int64.Type}})," & Chr(13) & "" & Chr(10) & " #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each [Column1] > 3)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Filtered Rows"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Sheet1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Sheet1"
.Refresh BackgroundQuery:=False
End With
End Sub
However when I try to run the above code, I get an error message:
Code:
Run-time error
A query with the name 'Sheet1' already exists
What does it mean? Is it refering to the workbook Data.xlsx or the workbook which I am recording the macro? I tried changing the name of the worksheet in both workbooks (from Sheet1 to Sheet10) but the error remains.
Thanks