How To Really Publish Excel Data To Power BI … Part Deux (amended)

Just a quick note for those that looked at and perhaps tried out the code in the post of a couple of weeks ago ( https://dmoffat.wordpress.com/2016/09/29/how-to-really-publish-excel-data-to-power-bi-part-deux/)  .  

I have made a very small but very important change to the code.

Turns out that when I push the limits (whatever those are?) by publishing data table with lotsa data (my recent test was 500,000 rows) the code basically grinds to a halt 😦 ..

This is because in the code while I paste xlValues into the target worksheet, when I paste xlFormats it take many minutes to complete the paste … that is because there are the formats in the source Pivot Table to paste and this take s loooooong time to complete.

So I have amended the code to paste only Values and Number Formats .. and it does it virtually instantly even with a large number of rows.

So you need to replace this:

.Cells(1, 1).PasteSpecial Paste:=xlValues
.Cells(1, 1).PasteSpecial Paste:=xlFormats

with this:

.Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

in Sub PBIExcelCode() ….

Sorry I missed that …

I will be posting about the next steps this week,

Dick