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
.Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
in Sub PBIExcelCode() ….
Sorry I missed that …
I will be posting about the next steps this week,