Design Dilemma

Printable View

03-22-2024, 08:02 AM
Bob Phillips

Design Dilemma
Bit of an odd one, and a bit convoluted to explain, but here goes.

I have an application where I am determining a KPI for many different measures for many properties, and loading the results into another workbook to produce various analyses.

The basic logic steps are:
1. A new output file, say Scorecard, is created from a template file
2. Read multiple Excel files, stage the data from each Excel workbook into separate worksheets in the application
3. Each worksheet of staged data is loaded into separate Power Query queries, usual bits and bobs done, and output back into tables in Excel
4. The Excel tables are then copied across to Scorecard
This was working okay, or so I thought, until I added a new column, run period, in anticipation of accumulating the data for historical analysis. When I did this I found that when a query returned no data the old data was being used (don't ask, but I have fixed that part).

However, I now have the problem that step 4 doesn't work anymore, the data just does not copy across. I have no idea as yet as to why the copy does not work, the only thing that makes it work is to stop the code with a breakpoint or a STOP, and then resume the code (F5). I have tried various things to correct this, such as
- make sure that screenupdating is on
- sprinkled in a few DoEvents
- added an Application.Wait
- added an extra message box to interrupt the flow
but none of these worked. I tried going back to the previous version, but that also had the problem, I think it could have occurred some time ago but was being masked by the problem I mentioned earlier.

I then took a different tack, instead of outputting the queries to the application I would direct them to the Scorecard. As the Scorecards would be different each time, I needed to change the query output at runtime, I have code that can supposedly do this. First problem was that I got an error 1004, so I activated the Scorecard and the target KPI sheet and then got an error that it couldn't find the query, it can't reference a query across workbooks.

I could put all of the queries in the template and run them there, but it has been very useful outputting the results into the application and looking at problems and issues there, same with staging the data.

One thing that occurred to me was to bypass the staging and read the Excel files directly into Power Query, but the files are in OneDrive, have you ever tried opening a OneDrive file in Power Query?

So, I am an an impasse. I have to continue looking at why the copy doesn't work, it did originally, but I am now wondering if there is a better solution here that I am not seeing.

Anyone have any thoughts/ideas?
03-22-2024, 08:32 AM
Paul_Hossler
Try this

https://www.vitoshacademy.com/vba-co...y-ghost-break/
Quote:
Anyhow, this is called “Ghost Break” and there is a good way to go around it, within 5 seconds. These are the steps:

Press “Debug” button in the popup.
Press Ctrl+Pause|Break twice.
Hit the play button to continue.
Save the file after completion.

And that is enough.
03-22-2024, 09:26 AM
Bob Phillips

It's not a ghost break Paul, although I have met those many times in my developments. This is more a case of the code not doing something that it should do, a copy that doesn't work.

Anyway, I think I have found the solution. It seems my code, which does a query refresh, carried on running whilst the query was still executing, so it was copying a blank table.

The solution is to turn off Background Refresh for the queries, and add a DoEvents in the code. I need to test some more, but seems good so far.
03-22-2024, 10:34 AM
Paul_Hossler

If playing with the timing doesn't provide reliable results, maybe the concept behind

http://www.appspro.com/Utilities/CodeCleaner.htm

That only works with 32 bit Office, but you could manually do the same thing

I'm still using 32 bit office and the cleaner has solved many of my problems
03-22-2024, 10:36 AM
georgiboy

I think that may well be the issue, I have had issues like this in the past but all Excel side. Back then I used the same method to refresh the query before I then refreshed the pivot tables to create an all in one refresh scenario. Below is the code I used in case it is any help:

Code:

Sub RefreshQuery() Application.ScreenUpdating = False With Sheet1.ListObjects("tbProjData") .AutoFilter.ShowAllData .QueryTable.Refresh BackgroundQuery:=False .Sort.SortFields.Clear .Range.AutoFilter 3, "<>" End With Sheet3.ListObjects("tbLastSaved").QueryTable.Refresh BackgroundQuery:=False Sheet3.ListObjects("tbStandby").QueryTable.Refresh BackgroundQuery:=False Sheet3.PivotTables("PivotTable1").PivotCache.Refresh Sheet3.PivotTables("PivotTable2").PivotCache.Refresh Sheet3.PivotTables("PivotTable3").PivotCache.Refresh Sheet3.PivotTables("PivotTable4").PivotCache.Refresh Application.ScreenUpdating = True End Sub

Instead of updating pivots i suppose you would run your copy code.
03-22-2024, 11:49 AM
Bob Phillips

Quote:

Originally Posted by Paul_Hossler

If playing with the timing doesn't provide reliable results, maybe the concept behind

http://www.appspro.com/Utilities/CodeCleaner.htm

That only works with 32 bit Office, but you could manually do the same thing

I'm still using 32 bit office and the cleaner has solved many of my problems

Paul,

When I switched to 64-bit Excel and Code Cleaner ceased working for me, so I wrote my own. It is a simple routine that adds a menu item in the VBIDE, and you can either export all modules, or you can be selective. I could not live without that functionality that Code Cleaner provided.

I know lots of people have used Code Cleaner to export all their modules and to re-import them when they get odd problems, but that is not why I need(ed) it. The reasons I need it are two-fold, first to save libraries of my code, but also to do a compare across versions. I used to have a program called VBADiff that compared all of the modules, exporting the files and then presenting them to WinMerge. Some years my VBADiff stopped working, and I couldn't contact the developer, so I had to forge my own. If I could find the time to automate WinMerge, it has a command line interface, it would be nearly perfect for me (on the problem I raised here I went back over 15 versions trying to see if I had coded in the problem!!!).
03-22-2024, 11:52 AM
Bob Phillips

Quote:

Originally Posted by georgiboy

I think that may well be the issue, I have had issues like this in the past but all Excel side. Back then I used the same method to refresh the query before I then refreshed the pivot tables to create an all in one refresh scenario. Below is the code I used in case it is any help:

Code:

Sub RefreshQuery() Application.ScreenUpdating = False With Sheet1.ListObjects("tbProjData") .AutoFilter.ShowAllData .QueryTable.Refresh BackgroundQuery:=False .Sort.SortFields.Clear .Range.AutoFilter 3, "<>" End With Sheet3.ListObjects("tbLastSaved").QueryTable.Refresh BackgroundQuery:=False Sheet3.ListObjects("tbStandby").QueryTable.Refresh BackgroundQuery:=False Sheet3.PivotTables("PivotTable1").PivotCache.Refresh Sheet3.PivotTables("PivotTable2").PivotCache.Refresh Sheet3.PivotTables("PivotTable3").PivotCache.Refresh Sheet3.PivotTables("PivotTable4").PivotCache.Refresh Application.ScreenUpdating = True End Sub

Instead of updating pivots i suppose you would run your copy code.

Yeah, I think it is. I stumbled across the solution because I found an old app of mine where I was querying a database and I had done as you are doing, setting the BackgroundQuery property to False. In my app though I can set that property of the query in the workbook via the GUI, so it is set at all times to False.