ActiveWorkbook.Connections().Refresh not working after version update

Hi,

I have several reports that I use to collect data from external data sources in a specific order. To update individual data connections I use VBA like this.

Please Login or Register  to view this content.

Capture2.PNG Capture3.PNG

Since version upgrade to 1711 this does not work anymore but gives error "Run-time error '1004': Application-defined or object-defined error." In previous Excel versions this code works as intended. Seems that ActiveWorkbook.Connections().Refresh is not supported any more?

What is this about and what should I do with this?

Most of my environment is still in version 1708 (semi-annual channel), but I'm expecting this to become a real problem after next version upgrade.

Hi,

Where are the UnprotectAllSheets and ProtectAllSheets routines located- in the same workbook? Does every connection fail to refresh with the same error, or only the first one (I note that you have spelled "parameters" differently in the first one)?

Try this
- each connection should have 0 (zero) against it in the message box
- 9 indicates that the connection cannot be found (error in name perhaps)

Please Login or Register  to view this content.

t has something to do with different kind of connection object in Excel 2016 after version 1711.

I am not convinced
- I am on 1712 and your code works
- specifically this method is usable

Please Login or Register  to view this content.

Are you able to update the connections manually?
If you can then try this:
- update each one manualy
(update them all)
- save and close the workbook
- open the workbook
- run the VBA

I have experienced a similar problem (not with connections) and Excel magically fixed itself

My next suggestion was going to be to try a new workbook - in case the old one was corrupted
If you are lucky, your problem may be restricted to only one workbook

Seems that when I recreate the connection object using Data->Existing Connections->"Comments" (open). It creates a new connection "Comments1". Now there is no problem refreshing this connection through VBA.

Now when thinking of this is may be that these problematic connections are of a kind that have originally been created with very old Excel version (2003 or so). At that time I guess there was no table objects even or at least they were not used for external data.

It may be that I have to fix these problematic connections to all my reports (which sucks). I'm still continuing to investigate this further...

I think you are correct