Getting Power Query data to an Excel worksheet

M

MichaelMorris

New Member
Joined
Mar 31, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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.
 
Upvote 0
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:
VBA Code: 
LoadQuery "QueryName", ActiveSheet

QueryName is the name you used when creating the query.

Then have the following Private Sub
VBA 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 = 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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Ideas Power Query and/or Python?
Replies
8
Views
188
mickeystanford_alumni
M
M
  • Question
problem with VBA and power query
Replies
8
Views
215
mickeystanford_alumni
M
R
  • Question
Is there a way to open a file that Power Query has locked as Read-Only?
Replies
0
Views
182
rcrumbliss
R
P
  • Question
Passing a parameter from VBAto Power Query
Replies
4
Views
138
jkpieterse
jkpieterse
A
  • Question
VBA to unlock worksheet when clicking on "refresh all" ribbon button so it can refresh a power query source
Replies
1
Views
500
PeteWright
PeteWright

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top