Forum Discussion
Power Query/Overwrite cell question
I didn't actualy get the problem solved. I did find an easy workaround. If some of you could actually see what its doing , I expect you could fix it. I am probably not doing a good enough job of describing it. But I am in a good spot with the form now. I appreciate the help. I am actually having a different issue that I may post when I have a chance.
Hi Missippy , thank you for the update. We're glad to hear you found a workaround. Could you please share the details here and mark it as 'Accept as solution' to help others with similar issues? If you have any other questions, feel free to post them (as a new post) in the community. We're always happy to assist.
Thank you.
Hi Missippy , pls Try Using the VBA to Force Overwrite
Since Excel sometimes resets the Overwrite Cells setting, you can automate this using a VBA macro.
Open VBA Editor
- Press ALT + F11 to open the Visual Basic for Applications editor.
Insert a New Module
- Click Insert > Module.
Add the Follewing VBA
Sub RefreshQueriesWithOverwrite()
Dim ws As Worksheet
Dim qt As QueryTable
Dim lo As ListObject
' Loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
' Refresh all QueryTables
For Each qt In ws.QueryTables
qt.RefreshStyle = xlOverwriteCells ' Ensures overwrite
qt.Refresh
Next qt
' Refresh all Table Queries (ListObjects)
For Each lo In ws.ListObjects
If Not lo.QueryTable Is Nothing Then
lo.QueryTable.RefreshStyle = xlOverwriteCells ' Ensures overwrite
lo.QueryTable.Refresh
End If
Next lo
Next ws
End Sub
Run the Macro
- Close the editor and run RefreshQueriesWithOverwrite() from the Macros window (ALT + F8).
_____
Try Refresh Queries via Power Automate (for Office 365) - as Akash_Varuna suggested (windows only)
If your Excel workbook is connected to Power Automate, you can create a flow that triggers the refresh with the overwrite setting enabled.
Solution 3: Convert Queries to Tables with Automatic Overwrite
- Instead of loading data directly into cells, load the query into a Table (Load to → Table).
- Excel automatically overwrites table rows instead of resetting the Overwrite Cells setting.
I just tried this. It didnt refresh the queries. It went to the Debug screen. I am sure its me. This VBA is way over my head. No doubt I need to take a VBA class or 2.