VBA code to open CSV file and extract the data in a Excel sheet

[Raychin]

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 :

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

 

[rpaulson]

try this on a copy of your file. I use it a lot, its not the fastest method but it works every time.

creat a sheet and name it "Import" you can hide it if you want to

Sub Get_data_from_CSV_File()

Dim rs As Worksheet
Dim fName As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

Set rs = Worksheets("Import")

fName = Application.GetOpenFilename("Text Files (*.csv), *.csv")
If fName = "False" Then Exit Sub

rs.Cells.ClearContents 'clear data on import sheet

    Open fName For Input As #1
    r = 1
   
    Do Until EOF(1)
    Line Input #1, LineFromFile
   
    lineitems = Split(LineFromFile, ",")
   
    For c = 0 To UBound(lineitems)
    rs.Cells(r, c + 1) = lineitems(c)
    Next c
   
    r = r + 1
    Loop
   
    Close #1
   
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
MsgBox r & " Rows Imported"

End Sub

 

[Raychin]

@rpaulson Thank you! Now the data is in the "Import" sheet. Well now i have to complete the process - this data have to be processed with Power Query so the real table can show off and to be useful for the process. can it be done by VBA?

 

[Raychin]

I just past the 10min Edit period - it doesn't need PQ just a simple code for convert Text-to-Column is enough. Again - thank you very much rpaulson !

 

[Raychin]

Well, unfortunately i need Power Query to kicks in after the .csv data is imported in sheet "Import". Can it be done with VBA?