Getting Power Query data to an Excel worksheet

[MichaelMorris]

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 and, in my case, green shadings.

Once I have it in a sheet, which is needed so the receipt can look at the data, I then create a pivot table and chart information.

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

Any help would be appreciated.

Thank you
Michael.

 

[GraH]

If the purpose is to create a pivot from the table, why would you not load the data through Power Query in a pivot table directly?
The load to table is unwanted in this case.

The user wants to see the data? Then enable the drill down from the pivots. On demand they create that table.

 

[MichaelMorris]

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.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Getting Power Query data to an Excel worksheet
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[MichaelMorris]

Since I was unaware that the sites I was posting to were connected, I apologize.

I hadn't received any help after posting on either and just happened to stumble on the solution myself and wanted to get it on the thread so others could see that it had been solved.

Thanks
Michael