Use VBA to disable Excel linked data type refresh

Question

I am using the Excel Stock linked data type in a spreadsheet where I use VBA to generate the list of tickers to get quotes for. I then convert them to linked data types using

.Sheets("Quotes").Range("B1:B" & Row).ConvertToLinkedDataType ServiceID:=268435456, LanguageCulture:="en-AU"

It seems that by default, the quotes refresh automatically every 5 minutes, however for the purposes of my application this is undesirable. It seems I can disable this in Excel by using the "Data Types Refresh Pane" and setting the Refresh rate for "Stocks" to "Manually" as described here:

However as these spreadsheets are created and populated automatically by VBA this isn't very practical.

Is there a way to change this refresh setting programmatically via VBA?

Answer 1

I don't have Excel at hand to test so you'll have to experiment a bit.

Option 1 as mentioned in the comments is to copy + paste values to "freeze" the linked data type. I'm not sure this definitely works on linked data types, it does on the power query variety.

Option 2 is to read the linked data type into power query, expand it to make all the fields new columns, then collapse it using "Create Type". Again untested whether PQ can load a linked data type from the sheet. Then copy+paste values the result.

Option 3 will definitely work. Expand all the fields into the worksheet as new columns with a formula. Load all the fields into PQ and create a new record type from them as in option 2.

All 3 can be automated with VBA