Hello! I have a issue with making a code that will extract the data/table from the csv file to a designated Excel(.xlxs) file and sheet. The thing is that the full table is only visible when .csv file is open with Excel Power Query option (Data--> From Text/CSV). So i tried to make some code that will do that for me, instead i to have to open the file, process it and save the content. But i have an issue....
Can you please help me again? I'm stuck and don't know how to complete the code, if it possible at first place.
Here is my code so far :
Can you please help me again? I'm stuck and don't know how to complete the code, if it possible at first place.
Here is my code so far :
VBA Code:
Sub PowerQueryTransform()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=TSO Metered Data;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [TSO Metered Data]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "TSO Metered Data"
.Refresh BackgroundQuery:=False
End With
Range("C21").Select
Windows("Meterd Monthly Data Colector.xlsm").Activate
End Sub