Forum Discussion
VBA Refresh Connections : Error 1004 Application-defined or object-defined error
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 ?
7 Replies
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
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 FalseSo 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.
- Deleting all the files in the Temp folder doesnt seem to fix the issue.
- If you want us to help, show the code.
- Sheets("CONSOLIDAT").Cells.ClearContents
col = Application.WorksheetFunction.CountA(Range("A3", Range("A3").End(xlToRight)))
Row = Application.WorksheetFunction.CountA(Range("A3", Range("A3").End(xlDown)))
Sheets("BOA").Activate
Sheets("BOA").Range("b2:H2").Copy
Sheets("CONSOLIDAT").Range("A1:E1").PasteSpecial xlPasteValues
Sheets("BOA").Select
Sheets("BOA").Range("b3").Select
Sheets("BOA").Range("b3", Cells(Row, col)).Select
