Jul 13 2018
02:26 AM
- last edited on
Jul 25 2018
10:07 AM
by
TechCommunityAP
Jul 13 2018
02:26 AM
- last edited on
Jul 25 2018
10:07 AM
by
TechCommunityAP
I have a report using Power Query and Power Pivot, that is refreshed using VBA. It worked well for several years ,and since last week the VBA stops in Debug mode on the Refresh method of the connection, with the above 1004 error. No reasons for that, especially as i can hit F8 and the line will execute perfectly manually. For some reason, VBA is not running properly since one week.
Sometimes i have an error message mentoinning a file in my TEMP folder.
Any idea why ? how can i find which program is interferring ?
Jul 13 2018 02:58 AM
Jul 13 2018 03:13 AM - edited Jul 13 2018 03:17 AM
Hi JKP ! I'm honored to have you read my post, i've used your precious help a lot in the past.
In reality, I'm pretty sure the code is not really the problem, since it worked for 6-7 years with no issues. And again, it just stops on the line of code, and if i hit F8 it continues with no issue.
I believe the problem is inherent to Windows, or a conflicting app, or update. Moreover, some users dont run into the error message, while others do. Still, here the code (that includes SQL statements, confidential so I have to hide the details).
Sometimes also, Excel just freezes ('Not Responding') and doesnt finish the code.
Sometimes also, Excel closes completely without any warning.
And also, sometimes there is an error message mentionning the Temp folder, as such : "
We couldn't refresh multiple table including … the following error occurred while opening the file … xxx.162.cub.xml … the requested operation cannot be performed on a file with a user-mapped section open."
The code as it stops on the last line (Refresh):
With ActiveWorkbook.Connections("MYCALL21").OLEDBConnection (...) With ActiveWorkbook.Connections("MYCALL21") .Name = "MYCALL21" .Description = "" End With ActiveWorkbook.Connections("MYCALL21").Refresh
Jul 13 2018 03:18 AM
Jul 13 2018 07:26 AM
Are there multiple refreshes in the code? I see you have not set it to refresh synchonously, so a connection might be requested to refresh while a previous one has not yet finished.
ActiveWorkbook.Connections("MYCALL21").Refresh False
Jul 17 2018 12:36 AM
So first i believe the root cause was actually different, and the issue i reported here is just a secondary effect.
Essentialy, the problem seemed to come from Micro Trends anti virus who was conflicting with my report. I already read similar cases. I asked my IT to "fix" that and now it seems none of the error messages show up anymore, neither those mentionning a vertipaq.xml file in Temp, nor the VBA refresh issues.
Jul 17 2018 02:31 AM
Nov 27 2020 03:14 AM