Load query data into worksheet with VBA

I'm trying to automate a process that my colleagues are doing. The process is manual today and they had an old VBA macro that formatted the file incorrectly and while I was fixing this I thought I could automate some other steps that they do. The steps today are:

1. Open a new excel file
2. Go to Data -> Get data from file -> From Text/CSV
3. Choose the file
4. Choose 1252: West european (Windows), Delimiter semicolon
5. Load
6. Run macro
7. Save as csv with utf-8 encoding

I have managed to fix every step but I am having trouble importing the csv data. I have managed to add a connection to the correct file but no data is showing up in the worksheet even though the data is showing up correctly in the Power Query Editor. I've tried searching for how to "load" the data but I can't figure it out.

My current code to create the connection:

Sub formatCSV()

    'Import data from csv
    Dim workbook As workbook
    Dim filePath As String
    Dim importFormula As String
    
    'Set file path
    filePath = ""
    
    Set workbook = ActiveWorkbook
    
    importFormula = _
    "let " & _
        "Source = Csv.Document(File.Contents(""" & filePath & """), [Delimiter = "";"", Columns=30, Encoding=1252, QuoteStyle=QuoteStyle.Csv]), " & _
        "#""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) " & _
    "in " & _
        "Source"
    
    workbook.Queries.Add Name:="Query1", Formula:=importFormula

End Sub

Appreciate all help!

You can try that Sub it takes two arguments: Query name and destination sheet as object. It load data at A1 cell.

You can add to your code in the aed of sub to try it:

LoadQuery "Query1", ActiveSheet

Sub code:

Private Sub LoadQuery(ByVal QueryName As String, ByVal LoadDataSheet As Worksheet)

With LoadDataSheet.ListObjects.Add(SourceType:=0, Source:= _
                                 "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QueryName & ";Extended Properties=""""", _
                                 Destination:=LoadDataSheet.Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & QueryName & "]")

    .RowNumbers = False
    .FillAdjacentFormulas = False
    '.PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    '.PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_" & QueryName
    .Refresh BackgroundQuery:=False

End With

''Additionally you can unlink data from Power Query and unlist - transform ListObject to data in cells
'With LoadDataSheet.ListObjects.Item("Table" & QueryName)
'    .Unlink
'    .Unlist
'End With


End Sub