Getting Power Query data to an Excel worksheet

I'm having a bit of an issue with an Excel power query and VBA.

In VBA:
I create a new sheet in my workbook and name it.

I get my data from a CSV file with the information I need into a power query in my workbook. I can see the query in the workbook and view the data, everything is there. It is also a connection only query. I don't need to worry about refreshing the data because each week the CSV file has a different name. Refreshing is irrelevant.

Here is my issue. I want to use VBA code to take all the data in the query, all columns and all rows, and import it into the newly created sheet with the same formatting as if I was doing this all manually via the menu commands. Headers, Rows, shading, etc. Menu command: Data | From CSV | Choose CSV file | Load. Doing it manually creates a sheet with the name of the CSV file with, in my case, green shadings.

Once I have it in a sheet, then the rest of my script will do some data manipulation, I.e., converting customer ID# to customer name, delete a few columns, and create and format a pivot table and pivot chart.

Everything is working fine except putting the data from the query into a sheet.

Any help would be appreciated.

Thank you
Michael.

Solution found for my situation:

After I create my blank sheet and have loaded the query, and the data is there as a connection only query.

Add this line:

LoadQuery "QueryName", ActiveSheet

QueryName is the name you used when creating the query.



Then have the following Private Sub

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 = True
        .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

End Sub

This will take all your data from the query and dump it into a sheet.
I cannot take credit for the solution, found on another site, but thought it might be helpful to others.

crosspost: https://www.mrexcel.com/board/thread...sheet.1256530/

Rule 7: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

However, if you continue to crosspost, you can expect to have your thread BLOCKED until you update it yourself.

The sites are not connected. Read the link highlighted in RED in my post on why this is important to notify each forum of the crosspost.